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

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

alfardanAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you CANNOT use dynamic sql in a function, full stop.
you need to redesign this requrest into stored procedures.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Small Mistake... Instead of the below statement

insert into #temp
EXEC sp_executesql @MySQL

use

insert into @MyTable
Select * from #temp
0
 
SharathData EngineerCommented:
>> 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.