Solved

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

Posted on 2009-05-02
5
747 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 143

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
error in my cursor 5 41
Need return values from a stored procedure 8 21
Reformat SQL - so SSRS can read the columns 25 14
SQL - Simple Pivot query 8 15
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

820 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