Table variable stored procedure output

Question

Post reply

Table variable stored procedure output

mlazlo

SSC-Addicted

Points: 414
More actions
August 7, 2003 at 2:35 pm

#162119

I have posted this to another forum w/o any luck. Maybe someone here knows of a way to get this to work.
Using SQL 2k how can I declare a variable of type 'table' as an OUTPUT variable in a stored procedure?
Example:
Create Procedure [WH1].[Usp_ArchiveChild]
@sParent VarChar(20) = NULL,
@sKey VarChar(20) = NULL,
@sPId VarChar(5) = '0',
@tArchived Table OUTPUT
As
...
Go
This throws error:
Server: Msg 156, Level 15, State 1, Procedure Usp_ArchiveChild, Line 6
Incorrect syntax near the keyword 'Table'.
I am attempting to recursively call a stored procedure that returns a table. Now thiis process, when done, will be called through a MSSQL Agent job so I am not looking to use the expense of ADO.
Now as for returning output from a 'Select ...' statement and use that in a table variable in the calling procedure as stated in Books Online:
" ... A table variable behaves like a local variable. It has a well-defined scope, which is the function, stored procedure, or batch in which it is declared.
Within its scope, a table variable may be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. However, table may not be used in the following statements:
INSERT INTO table_variable EXEC stored_procedure
SELECT select_list INTO table_variable statements. "
So that won't work.
Unless there is a way to pass out a table variable w/o declaring it first, the only option I see is to use a temp. table to take the output of the stored procedure.
Any help would be appreciated!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

mccork SSC-Insane Points: 22288 More actions · Answer 1

Refer to QOD Aug 4 2003... TABLE variables cannot be passed to SPs (either as input, or output).

Looks like you've come across the other limitations of TABLE variables too. I hope they're overcome in SQL2k+++

You can try recursively calling a table function? Eg.

 

USE NORTHWIND

GO



IF EXISTS (SELECT * 

   FROM   sysobjects 

   WHERE  name = N'customerIds')

DROP FUNCTION customerIds

GO



CREATE FUNCTION customerIds 

(@rangestart char(5), 

 @rangeend char(5))

RETURNS @customerids TABLE 

(CustomerID char(5))

AS

BEGIN

INSERT @customerids

    SELECT CustomerID from Customers

            where CustomerId between

                ISNULL(@rangestart, (select min(CustomerId) from customers))

            and

                ISNULL(@rangeend, (select max(CustomerId) from customers))

RETURN 

END

GO







declare @customers table (CustomerID char(5))



insert @customers select * from dbo.customerIds('CHOPS','FRANK')

SELECT * FROM @customers

Cheers,

- Mark

Cheers,
- Mark

smclarnon SSC Journeyman Points: 77 More actions · Answer 2

Have you attempted to write the code as a UDF that returns a table?

mlazlo SSC-Addicted Points: 414 More actions · Answer 3

Thanks for the replies. I knew that a UDF would be an option, BUT in a programming sense, a function should perform 1 purpose. What the stored procedure would be doing is a bunch of steps that will have UDF's inside of it to perform different functions. But since SQL server doesn't allow tables variables in or out of a stored procedure, taking a funtion (UDF) and making it act as a stored procedure would be more efficient than using temp. tables in stored procedures.

mlazlo SSC-Addicted Points: 414 More actions · Answer 4

Now that I have tried to use a UDF that returns a table variable, I hit this wall:

"Only functions and extended stored procedures can be executed from within a function."

Inside of this what was originally to be a stored procedure, I dynamically need to execute DML's inside using sp_executesql <String variable>. Well this is a limitation of a UDF that I have run into.

So after all of this, what I have learned is that with all this new "table' type variable you can only do somethings in certain situations. So it looks like I will now go back to the "old standby"; stored procedure that returns a select statement and pass it to a temp. table in the calling stored procedure!

garfield_75025 SSC Veteran Points: 281 More actions · Answer 5

I don’t think you can use table as parameter of a stored procedure. Here is what I think that might work.

Create Procedure [WH1].[Usp_ArchiveChild]

@sParent VarChar(20) = NULL,

@sKey VarChar(20) = NULL,

@sPId VarChar(5) = '0',

@tArchived VARCHAR(128) -- This is going to be the name of the table to store your resultset. It has to be VARCHAR(128) since maximum length for a table’s name is 128.

As

…. – Your regular process

Say your stored your results in a temp table named '#tmpResult' within this procedure.

At the end of the procedure, add the following code.

DECLARE @strSQL VARCHR(255)

SELECT @strSQL = 'INSERT INTO ' + @tArchived + ' SELECT * FROM #tmpResult'

EXEC(@strSQL)

Now your resultset is stored in the table you specified. To make this work even better, check the existence of your table in sysobjects table to make sure the insert won’t fail.

Hope this helps.