sql server - fill results from executed query string in a temp table dynamically

Question

I'm writing a stored procedure. I have a string which contains an sql query. For example:

DECLARE @sql nvarchar(max)
SET @sql = (N'SELECT pkOrderID FROM Orders')

(Just to note: this isn't what the select statement looks like. This is just an example of what I mean) I then want to execute the string and put the result in a temporary table E.g. #tempTable. I know EXEC(@sql) exists but not sure if it will do me any good in this situation. The other twist is that I do not know the names of all the columns in the returned @sql so the temp table #tempTable needs to be created dyanmically based off the return from @sql. Thanks for any help.

JanR · Accepted Answer · 2014-10-27 00:26:04Z

2

I think you could use SELECT INTO to do what you want but it would mean updating your string:

DECLARE @sql nvarchar(max)
SET @sql = (N'SELECT frompkOrderID INTO #tmporders FROM Orders')

then you should be able to run EXEC @sql to create the table

more information about SELECT INTO here : http://msdn.microsoft.com/en-au/library/ms188029.aspx

edited Oct 27, 2014 at 0:26

answered Oct 27, 2014 at 0:11

JanR

6,1303 gold badges25 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

frontin Over a year ago

Is there a way for me to do this without creating the structure of tempTable and dynamically having it create the columns based off whats returned from @sql

JanR Over a year ago

Select Into will create the table based on the result, assuming it doesn't already exist. Will add a reference to my answer

JohnS · Accepted Answer · 2014-10-27 00:50:36Z

1

There is no simple way to do this. The problem with @JanR's solution is that the #tmporders table will be out of scope to the script that calls your stored procedure (ie It will produce an error like "Invalid object name '#rtmporders'"

One alternative is to use a global temp table (eg ##tmporders).

So your SP might look like this:

CREATE PROCEDURE TestSP 
AS
BEGIN
  SELECT pkOrderID  INTO ##tmporders FROM Orders
END
GO

And the calling script might be like:

EXEC TestSP
SELECT * FROM ##temporders

answered Oct 27, 2014 at 0:50

JohnS

2,0581 gold badge14 silver badges16 bronze badges

3 Comments

frontin Over a year ago

ya I just ran into the out of scope issue. Let me try this out

frontin Over a year ago

When I try and drop the table at the end of the sp with DROP TABLE ##tempTable. It says cannot drop because it either doesn't exist or I dont have permission. I'm guessing it thinks it doesn't exist but it does because it properly executed the string statement...

JohnS Over a year ago

Global temp tables (##table) are dropped automatically when the session that created the table ends, and whenever any other TSQL referencing that table has finished.

Collectives™ on Stack Overflow

sql server - fill results from executed query string in a temp table dynamically

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related