Simple SQL stored procedure example with table output parameter ?

[StevenK]

In my travels I believe that I've seen mention of the ability to create a stored procedure within SQL Server that allows the return of a table (as an output parameter?)

Ideally we want to be able to call one stored procedure which performs a workload and in turn returns a table which can then be used by another stored procedure (or code block).

How easily is this done ?

Can someone provide or point me in the direction of a (simple) example that I can work from and build up to do what we require ?

Any help / suggestions would be greatly appreciated.
Thanks in advance,
Steve

 

[smitan]

Hi, try this in your SP:

Create Table tblTemp(fld1 nvarchar(1000), fld2 integer)

And fill the table with Insert Into .....

Good luck
Smitan

 

[StevenK]

Thanks for your help so far.
But I need the stored procedure to return a table of data rather feed one within it.
Can anyone suggest how this can be done ?
Thanks again.
Steve

 

[zhavic]

At first, create procedure that will return a table as result

CREATE PROCEDURE usp_get_table
AS

 ...
 blahblah
 ...

 -- create result  ( SELECT statement without INTO clause )

 SELECT some_field, some_another_field
   FROM some_table
   WHERE some_condition

And this is how to use result from the above procedure in another procedure

CREATE PROCEDURE usp_another_procedure
AS

  ...
  blahblah
  ...

  -- you need to create table with the same structure as the   table returning from stored procedure 'usp_get_table'
  Create Table #tblTemp(fld1 nvarchar(1000), fld2 integer)
 
  -- insert result from stored procedure 'usp_get_table'
  INSERT INTO #tblTemp EXECUTE usp_get_table

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.