Cory Foy

Friday, July 08, 2005

Inserting the results of a stored procedure to a temp table

Back from Florida. It was a good trip - though we forgot just how hot it is down there in the summer. I got to spend 8 hours cooking barbecue chickens for my old fire department's Fourth of July parade - it was a great time, and I'll talk some more about that later.

But one of my coworkers swung by and asked a very simple question - how do you get the results of a stored procedure (that returns a result set) into a table that you can query or join or use somewhere else? The simplest way is that temp tables are your friend, and you just insert the results into your temp table:

CREATE PROC test_proc
AS
SET NOCOUNT ON
SELECT TOP 10 id, name FROM employee

CREATE TABLE #tmp(
[id] int,
name varchar(64)
)

INSERT INTO #tmp
exec test_proc

With that, you can now treat the temp table like any other table:

SELECT *
FROM #tmp

Just don't forget to clean up when you are done!

DROP TABLE #tmp

posted by Cory Foy at 1:43 PM

7 Comments:

  • how about stored procedure with parameter. Any Solution?

    By Anonymous Anonymous, at 2:47 PM  

  • This is ok if Stored procedure returns one table. Can u please tell me how to cope with the sp which returns two tables.

    By Anonymous Anonymous, at 2:14 AM  

  • For the stored procedure with a parameter, you could just pass the parameter along:

    CREATE PROC test_proc
    @param1 varchar(128)
    AS
    --...

    INSERT INTO #tmp
    exec test_proc 'Cory'

    For a stored procedure which returns two tables - I'm not sure what the question is. If your Stored Proc is returning a table type, then you should be able to work like that as if it were any other table. If it is returning the results of two tables in one resultset, and you wanted it to go into two temp tables, the easiest way would be to do exactly what I have here, and then select out of the temp table into the two (temp) tables you want them to go into.

    By Blogger Cory Foy, at 8:51 AM  

  • I've been trying to figure this out for a day. I can't believe it was this easy!! Thanks.

    By Anonymous Anonymous, at 7:42 PM  

  • Does this work on sybase. I can't get it to. Do you know what will work similarly on Sybase?

    By Anonymous Anonymous, at 1:27 PM  

  • I get Invalid object error #tmp

    when doing this

    INSERT INTO #tmp
    exec test_proc 'Cory'

    By Anonymous Anonymous, at 3:44 PM  

  • For the Invalid Object error - are you doing the Create table call first? Are you using SQL Server or something else?

    By Blogger Cory Foy, at 7:44 PM  

Post a Comment

Links to this post:

Create a Link

<< Home