CODEWITHSUNDEEP
t-sqlstored-proceduresdynamictemp-tables
Yuriy Galanter
Yuriy Galanter

Reputation: 39807

TSQL: Determine number of columns returned by Stored Procedure

This probably has been asked before, but I was unable to find a satisfying answer.

I need to insert results of a stored procedure into a temporary table, something like:

INSERT INTO #TEMP EXEC MY_SP

I don't know in advance how many columns the SP will return, so I need to prepare my #TEMP table (via dynamic ALTER .. ADD commands) to add columns to match SP resultset.

Assumption is - SP accepts no parameters and number of columns is always the same. But how do I determine that number in pure TSQL outside of SP so I can store it for example into a variable?

Upvotes: 3

Views: 2700

Answers (4)

Mafu Josh
Mafu Josh

Reputation: 2682

I don't understand the syntax, and this probably isn't the best way, but someone seems to have done this with converting to xml and parsing it: Dynamic query results into a temp table or table variable

Upvotes: 0

Neil
Neil

Reputation: 1633

Let me say from the start that if I had to do this I would try find a way to do it outside the SQL environment or something else, because the solution I am providing is not a good way to do this, but it works. So I am not saying that this is a good idea. I have a sp called test: 

CREATE PROCEDURE Test
AS
SELECT 1 as One, 2 as Two

To execute this dynamically I do the following: 

DECLARE @i int
SET @i = 1;
DECLARE @SUCESS bit 
SET @SUCESS = 0
WHILE(@SUCESS = 0)
BEGIN 
  DECLARE @proc VARCHAR(MAX)
  DECLARE @count int
  SET @count = 1
  SET @proc = 'DECLARE @t TABLE ( c1 varchar(max) '
  WHILE @count < @i 
  BEGIN 
    SET @proc = @proc + ', c' + CONVERT(varchar, @count + 1) + ' varchar(max) '
    print @proc 
    SET @count = @count + 1
  END
  SET @proc = @proc + '); INSERT INTO @t EXEC Test'
  BEGIN TRY
    EXEC(@proc);
    SET @SUCESS = 1
  END TRY
  BEGIN CATCH
    SET @i = @i+1
  END CATCH

END

SET @proc = @proc + '; SELECT * into ##t FROM @t '
EXEC( @proc )

SELECT * from ##t

This is a poor solution to your problem because you have lost the data type of your columns, their names etc.

Upvotes: 1

Fredrik Johansson
Fredrik Johansson

Reputation: 3535

Tough one, especially if someone else is denying you the necessary permissions to run e.g. OPENROWSET.

Have you considered unpacking/script the SP and add its contents directly to your T-SQL? In this way you can modify and adapt it however you may want.

Otherwise, if you could explain more about the SP:

  • What does the SP do?
  • What kind of information does it output? One-N columns, - how many rows?
  • Is it slow/fast? (Could we perhaps use a brute-force [try-catch] approach)?
  • How does it determine the columns to output and how often does that change?
  • Can you pre-determine the columns in any way? (So that you may use an INSERT #temp EXEC sp_getData syntax).

Best of luck!

Upvotes: 2

GilM
GilM

Reputation: 3771

It's a bit awkward, but you can do something like:

SELECT * INTO #temp 
FROM OPENROWSET('SQLOLEDB','Data Source=MyServer;Trusted_Connection=yes;Integrated Security=SSPI', 'EXECUTE MyDB.MySchema.MyProcedure @MyParm=123')

I've requested an EXECUTE INTO syntax, like SELECT INTO to avoid having to know the shape of the stored proc output in advance, but it was rejected

Upvotes: 2

Related Questions