Solved

EXECUTE cannot be used as a source when inserting into a table variable??!!

Posted on 2009-05-02
5
742 Views
Last Modified: 2012-06-27
I can't create such a function, why? It gives em the following error:

Msg 197, Level 15, State 1, Procedure TEST_TABLE, Line 20
EXECUTE cannot be used as a source when inserting into a table variable.

The thing is, the table name I'm trying to select from is defined dynamically from this statement:

(select Request_Table_Name dbo.Request_Type where Request_Type_ID=[SOME_ID])

Also, the number those tables that I'm trying to select from are a lot, and don't have a standarized naming convension for them, therefore I need to get their names dynamically like the above attempt.

CREATE FUNCTION TEST_TABLE

(

	@RequestTypeID int

)

RETURNS 

@MyTable TABLE 

(

	SystemID int, 

	DateOfSubmission datetime

)

AS

BEGIN
 

	declare @MySQL varchar(200)

	set @MySQL = 'select Application_ID, Date_of_Submission from (select Request_Table_Name dbo.Request_Type where Request_Type_ID='+@RequestTypeID+')'
 

	insert @MyTable

	EXEC sp_executesql @MySQL

	

	return

END

Open in new window

0
Comment
Question by:alfardan
  • 2
5 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24288750
Table variable cannot be used in the following statements:

insert into @MyTable EXEC sp_executesql @MySQL
select * into @MyTable EXEC sp_executesql @MySQL

To resolve your issue,
1. Create a temporary table.
create table #temp ()
2. Insert records as part of EXEC sp_executesql @MySQL into your temporary table
insert into #temp()
EXEC sp_executesql @MySQL
3. Insert records from that temp table into your table variable
insert into #temp
EXEC sp_executesql @MySQL

Hope this helps
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24288752
Small Mistake... Instead of the below statement

insert into #temp
EXEC sp_executesql @MySQL

use

insert into @MyTable
Select * from #temp
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24289015
you CANNOT use dynamic sql in a function, full stop.
you need to redesign this requrest into stored procedures.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 24289105
>> Table variable cannot be used in the following statements:

insert into @MyTable EXEC sp_executesql @MySQL
select * into @MyTable EXEC sp_executesql @MySQL <<
rrjegan17 -  you can try the first statement but not the second.

 

declare @temp table (name varchar(100))

insert @temp exec sp_executesql N'select name from sys.tables'
 

select * from @temp

Open in new window

0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now