Solved

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

Posted on 2009-05-02
5
740 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>> 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

763 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

15 Experts available now in Live!

Get 1:1 Help Now