2

I have a stored procedure that uses sp_executesql to generate a result set, the number of columns in the result can vary but will be in the form of Col1 Col2 Col3 etc.

I need to get the result into a temp table or table variable so I can work with it. The problem is I need to define the columns of the temp table, which I cant do dynamically using sp_executesql as the scope of the temp table is lost after the command is executed.

I have toyed with the idea of using Global Temp tables, as the scope allows it to be created dynamically, however, there is a very good chance the Global Temps would get updated by the concurrent executions of this process.

Any ideas?

Aziz Shaikh
  • 16,245
  • 11
  • 62
  • 79
SausageFingers
  • 1,796
  • 5
  • 31
  • 52
  • 1
    SQL is generally oriented around producing result sets with the same "shape" - the same columns (names, types) each time the query is executed. Whilst you can produce variable result sets using various hacks, it would be better to try to re-work your solution to fit this model of querying. You're also indicating a very procedural approach (1st I produce this result set, then I do further work with it), which again isn't what SQL is meant to be about - you find a way to describe your desired result, and let SQL Server sweat the details of how that happens. – Damien_The_Unbeliever May 23 '11 at 16:57
  • duplicate- http://stackoverflow.com/questions/840968/dynamically-create-temp-table-insert-into-temp-table-and-then-select – DForck42 May 23 '11 at 17:19
  • Not a duplicate, I have ruled out the use of Global Temp tables as a solution for me. – SausageFingers May 23 '11 at 17:34

3 Answers3

4

I have found a solution that works for me with the help of @SQLMenace in this post T-SQL Dynamic SQL and Temp Tables

In short, I need to create a #temp table in normal SQL first, then I can alter the structure using further dynamic SQL statements. In this example @colcount is set to 6. This will be determined by another stored proc when I implement this.

IF object_id('tempdb..#myTemp') IS NOT NULL
DROP TABLE #myTemp

CREATE TABLE #myTemp (id int IDENTITY(1,1) )
DECLARE @cmd nvarchar(max)
DECLARE @colcount int
SET @colcount = 6
DECLARE @counter int
SET @counter = 0
WHILE @counter < @colcount
    BEGIN
      SET @counter = @counter + 1
      SET @cmd = 'ALTER TABLE #myTemp  ADD col' + CAST(@counter AS varchar(4)) + ' NVARCHAR(MAX)'
      EXEC(@cmd)
    END

INSERT INTO #myTemp 
EXEC myProc @param1, @param2, @param3

SELECT * FROM #myTemp
Community
  • 1
  • 1
SausageFingers
  • 1,796
  • 5
  • 31
  • 52
  • How do you determine real @colcount at runtime? In other words - how do you know how many columns your SP returns? – Yuriy Galanter Jul 23 '12 at 18:39
  • I have a number of other stored procs and udf's that parse the first few thousand chars of the raw data and convert it to an xml string which I then cast into an xml type. Then I use xquery to find the maximum column count from the sample: `SELECT @colcount = max(r.r.value('count(./data)','int')) FROM @xml.nodes('/root/r') r(r)` – SausageFingers Jul 25 '12 at 12:07
2

IS there any reason you can't do something like:

SELECT *
INTO #MyTempTable
FROM MyResultSet

SELECT INTO doesn't require an explicit field list.

JNK
  • 63,321
  • 15
  • 122
  • 138
  • Can't do this as the results are coming from a stored proc, the following is not allowed. You can use INSERT INTO #MyTempTable EXEC MyProc but the schema needs defining first. – SausageFingers May 23 '11 at 17:11
  • This is a terrible idea. This will lock tempdb the entire time your query is running. This will cause serious issues if it is a long running query – cadrell0 May 23 '11 at 19:34
  • Ah, I guess just an old prejudice. It did this in older version of SQL Server, but apparently it has been fixed. – cadrell0 Jun 07 '11 at 20:40
2

You can use global temp tables whose names are 'uniquified' by the SPID of the creating process. This can allow you to avoid stomping on other global temp tables created by other connections.

Just make sure to clean them up when you're done... :)

mwigdahl
  • 16,268
  • 7
  • 50
  • 64
  • global temps go away as soon as the defining connection is closed and any other referencing connection also closes - there shouldn't be any cleanup required (but it's still an ugly solution) – Damien_The_Unbeliever May 23 '11 at 17:00
  • I did consider this, but how would you query the temp table? SELECT * FROM @tmpTableName is not allowed so you would have to start using dynamic sql to query the results. Just feels wrong – SausageFingers May 23 '11 at 17:08
  • 1
    @Fly_Trap - everything here feels wrong - we may do better if you describe the original problem, rather than this broken half-solution that doesn't seem to be working. – Damien_The_Unbeliever May 23 '11 at 17:12
  • I'm importing a flat csv (of unknown structure) into a single nvarchar(max) field. I'm then converting the text into an xml stucture and using xquery to structure the data into a table. This is where I'm up to. Then I want to iterate the rows and look for key bits of information so I can apply business logic to the results. There are many reasons why I am doing this in such a way that would best be discussed on another thread. Anyhow I have found the answer to my problem now, I will it post shortly. – SausageFingers May 23 '11 at 17:18