Solved

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

Posted on 2009-05-02
5
756 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 41

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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.​
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

734 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