What do you do if you have a stored procedure that has a very "wide" result set and you want to store the results in a temp table so you can debug/manipulate/have a look at them? There are several options, some of which involve OPENROWSET or OPENQUERY, but those commands both require remote ad-hoc queries to be enabled on the server and didn't really meet my needs today. You can create the temp table before you execute the stored procedure, but that can be cumbersome if the stored procedure returns 350 columns. Below is a different solution where we dynamically create the table using some system tables and cursors.
IMPORTANT NOTE: This solution is not recommended for use in a stored procedure or as any kind of regular processing. I wrote this for the sole purpose of looking at the data that would be returned from a stored procedure so I could sort and manipulate those results for debugging purposes.
-- declare your variables
DECLARE @procedureName VARCHAR(100),
@sql VARCHAR(max),
@text VARCHAR(8000),
@alterSql VARCHAR(8000),
@index INT,
@slice VARCHAR(8000),
@Delimiter CHAR(1)
-- drop the table if it exists
IF Object_id(N'tempdb..#temp') IS NOT NULL
BEGIN
DROP TABLE #temp
END
-- create the temp table
CREATE TABLE #temp
(
placeholder CHAR(1)
)
-- initialize the variables
SET @procedureName = 'SampleTable'
SET @sql = ''
SET @Delimiter = ','
SET @index = 1
-- each stored procedure will populate a variable number of rows in syscomments to contain the entire text of
-- the stored procedure, broken into 8,000 character chunks
-- open a cursor for all the syscomments records
DECLARE procedurecursor CURSOR FOR
SELECT sc.[text]
FROM syscomments sc
INNER JOIN sysobjects so
ON sc.id = so.id
WHERE so.[name] = @procedureName
OPEN procedurecursor
FETCH next FROM procedurecursor INTO @text
WHILE @@FETCH_STATUS = 0
BEGIN
-- append the text to the larger @sql variable
-- replace all line breaks with a ~! combination (you may want to use a different combination)
SET @sql = @sql
+ Replace(Replace(@text, Char(10), '~!'), Char(13), '~!')
FETCH next FROM procedurecursor INTO @text
END
CLOSE procedurecursor
DEALLOCATE procedurecursor
-- trim the sql script down to remove everything after (and including) the last occurrence of "FROM"
SELECT @sql = Substring(@sql, 1, Len(@sql) - Charindex('MORF', Reverse(@sql))
- 3)
-- replace the ~! with spaces
SELECT @sql = Replace(@sql, '~!', ' ')
-- trim the sql script down to remove everything before (and including) the last occurrence of "SELECT"
SELECT @sql = Substring(@sql, Len(@sql) - Charindex('TCELES', Reverse(@sql)) + 3
, Len(
@sql))
-- replace spaces with nothing (this assumes spaces are not in the column names)
SELECT @sql = Replace(@sql, ' ', '')
-- only proceed if the sql script is populated
IF @sql IS NOT NULL
AND Len(@sql) > 1
BEGIN
-- iterate through the sql script and alter the temp table to add the column
WHILE @index != 0
BEGIN
SET @index = Charindex(@Delimiter, @sql)
IF @index != 0
BEGIN
SET @slice = LEFT(@sql, @index - 1)
END
ELSE
BEGIN
SET @slice = @sql
END
IF( Len(@slice) > 0 )
BEGIN
IF CHARINDEX('.', @slice) > 0
BEGIN
SET @slice = SUBSTRING(@slice, CHARINDEX('.', @slice) + 1, LEN(@slice))
END
SET @alterSql = 'ALTER TABLE #temp ADD ' + @slice
+ ' VARCHAR(8000)'
EXEC (@alterSql)
END
SET @sql = RIGHT(@sql, Len(@sql) - @index)
IF Len(@sql) = 0
BEGIN
BREAK
END
END
END
-- drop the placeholder column from the temp table
ALTER TABLE #temp
DROP COLUMN placeholder
-- execute the stored procedure with whatever parameters you're using to debu
INSERT INTO #temp
EXEC Sampletable_get
@UserID = 12345
-- view the results of the stored procedure call
SELECT *
FROM #temp
Just set your variable names to be what you need and hit Execute. You'll end up with a temp table called #temp populated with the results of the stored procedure. Again, you don't want to use this in a stored procedure or anything, but it can be helpful to debug stuff.
A couple of obvious issues:
- This hasn't really been tested other than the couple of times I used it so it's possible it won't work for you "out of the box" so be prepared to tweak it
- All of the columns in the temp table will be VARCHAR(8000) so you won't be able to perform certain functions (such as ORDER BY) without converting the data
- I know that's a bummer, but I couldn't come up with a way around it
