table variable as output parameter in stored proc

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

SQL Server Development (2000)

table variable as output parameter in stored proc

Author

Topic

kaus
Posting Yak Master

179 Posts

Posted - 2007-03-02 : 14:05:22

Hi - can I use a table variable as an output parameter in stored procedure? I ve got:
CREATE procedure CHT_Fiscal_Sum_WH
@Fiscalyr as integer,
@WH_Sum Table(SiteNo Int, SiteName nvarchar(50),
FYE as Int, AVGMGYR Decimal(12,4), SUMMGYR Decimal(12,4),
AVGBODYR Decimal(13,4), LBSBODYR Decimal(13,4),
AVGTSSYR Decimal(13,4), LBSTSSYR Decimal(13,4)) output

getting error:
Incorrect syntax near the keyword 'Table'

It works if I declare it as a variable though, maybe this cant be done?

Thanks

kaus
Posting Yak Master

179 Posts

Posted - 2007-03-02 : 16:28:17

Here's my answer:
http://www.microsoft.com/technet/community/chats/trans/sql/sql0724.mspx

Q: cdickey : Can table variables be used with the output of stored procedures?

A: No, though you can return a cursor and get similar functionality.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-03-02 : 17:41:04

But you can do:

create table #x(...)
insert #x
exec CHT_Fiscal_Sum_WH @Fiscalyr = ..

as long as the create table matches the resultset of the sp.

rockmoose

kaus
Posting Yak Master

179 Posts

Posted - 2007-03-02 : 19:04:13

Thats a good idea, thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources