Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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

Posted on 2009-05-02
Medium Priority
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.

	@RequestTypeID int
@MyTable TABLE 
	SystemID int, 
	DateOfSubmission datetime
	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

Open in new window

Question by:alfardan
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
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
LVL 57

Expert Comment

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

insert into #temp
EXEC sp_executesql @MySQL


insert into @MyTable
Select * from #temp
LVL 143

Accepted Solution

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

Expert Comment

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


Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

715 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