table variables as output parm for SP ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

table variables as output parm for SP ?

Author

Topic

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-15 : 07:27:24

Hi guys,

is it possible to declare table variables as parametre's for SP ? I get:


Server: Msg 156, Level 15, State 1, Procedure usp_test_tbvar, Line 1
Incorrect syntax near the keyword 'table'.
Server: Msg 137, Level 15, State 1, Procedure usp_test_tbvar, Line 6
Must declare the variable '@tbvar'.
Server: Msg 137, Level 15, State 1, Procedure usp_test_tbvar, Line 10
Must declare the variable '@tbvar'.

trying

create procedure usp_test_tbvar @tbvar table (e int,snm sysname, sip char(15), enab bit)
as

--declare @tbvar table (e int,snm sysname, sip char(15), enab bit)

insert into @tbvar (e,snm,sip,enab)
SELECT [entry], [server_nm], [server_ip], [enabled] 
FROM [RG_Scrap_Pad].[dbo].[server_ip_list]

select * from @tbvar

although this works fine.



declare @tbvar table (e int,snm sysname, sip char(15), enab bit)

insert into @tbvar (e,snm,sip,enab)
SELECT [entry], [server_nm], [server_ip], [enabled] 
FROM [RG_Scrap_Pad].[dbo].[server_ip_list]

select * from @tbvar

I noticed from this topic:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=34283&SearchTerms=table+variables
that Derrick noted we cannot exec sp's into @table variables. Can we use table variable's as paramter's, and output them ?

TIA

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

mr_mist
Grunnio

1870 Posts

Posted - 2004-07-15 : 07:53:52

Could you do it as a UDF instead?

-------
Moo. :)

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-15 : 09:39:49

The above was an example. The developer has a set of unions being done in an SP, and wants to use the restuls in another SP.

The idea was to have it in 2 SPs, since the SP creating the result set is useful, and might get re-used elsewhere - if I can get the data out.

Obviously I could create a table, insert the data, but I'd rather not go that way.
Another option that comes to mind is creating a view, based on the SQL.

... times passes ...

Seems that the requirement is much more adhoc, so were are going to insert into a temp table.

Thanks for the comment

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Kristen
Test

22859 Posts

Posted - 2004-07-15 : 09:53:52

> is it possible to declare table variables as parametre's for SP

Nope, BOL (re SProc parameters):

quote:

data_type

Is the parameter data type. All data types, except the table data type, can be used as a parameter for a stored procedure. However, the cursor data type can be used only on OUTPUT parameters. When you specify a data type of cursor, the VARYING and OUTPUT keywords must also be specified. For more information about SQL Server - supplied data types and their syntax, see Data Types.

mr_mist is right about UDF if you can do it. We have loads of "reused" stuff like that:


SELECT *
FROM   MyTable T
       JOIN dbo.MyUDF(@PageNumber, @ProductNameFilter, @CatalogueFilter) U
       ON U.PKColumn = T.PKColumn

the UDF finds the appropriate rows, and just returns the PK - which we then join to the main table (and any others) and away we go.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources