3

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?

Léo Léopold Hertz 준영
  • 134,464
  • 179
  • 445
  • 697
Yuriy Galanter
  • 38,833
  • 15
  • 69
  • 136
  • Your life would be easier if you were doing this against a function rather than an SP – Neil Jul 28 '12 at 20:50
  • Unfortunately I do not control this – Yuriy Galanter Jul 30 '12 at 01:53
  • If you can create a db along side the DB locked down one (I don't know what sort of restrictions you are under) You may be able to cache some info there - or do something there. The brute fore solution seems so poor. – Neil Jul 30 '12 at 02:05

4 Answers4

2

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

GilM
  • 3,711
  • 17
  • 18
  • Thanks for the prompt response, but I am afraid this won't work for us. OPENROWSET requires elevated permissions which won't be available. – Yuriy Galanter Jul 23 '12 at 19:27
2

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!

Fredrik Johansson
  • 3,477
  • 23
  • 37
1

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.

Neil
  • 1,605
  • 10
  • 15
  • This is also a very fragile solution - ideally you would not catch everything in the catch - just the specific error. – Neil Jul 28 '12 at 20:53
0

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

Community
  • 1
  • 1
Mafu Josh
  • 2,523
  • 1
  • 23
  • 25