return TABLE problem - SQL Server Forums

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)

return TABLE problem

Author

Topic

Greex
Starting Member

3 Posts

Posted - 2004-11-15 : 06:45:39

I've been reading your articles for a while now, and hope you can help me.

I want to write a stored procedure (SQL2000/Win2k)
which returns a table (for ease of interfacing),
yet I get an error, which I will detail shortly.

Here's the code for the proc.:

===============================================================
CREATE PROCEDURE aquire_fbn
AS

DECLARE @rt table (fbn varchar(16))

insert into @rt -- <-- here it works fine
select top 1 fbn from tnt_fbn where used != 1 order by fbn asc

return @rt -- <-- this line results in an error ?!
GO
===============================================================

If I remove the return, I get Syntax Ok
BUT: as soon as I put 'return @rt' in,
I get an error:
Error 137: Variable '@rt' must be declared.

That's quite obscure in my oppinion since it works fine until I
try to return the table variable.

I hope your eyes see more than mine,
sincerely, Hermann Scharitzer

Andraax
Aged Yak Warrior

790 Posts

Posted - 2004-11-15 : 07:15:28

Hi!

Two problems here:

1) You cannot use table variables as neither input nor output from procedures.

2) When you want to return a value from a procedure you have to use and output parameter:

create proc test @slask int OUTPUT

AS
...

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-15 : 07:51:24

Probably this will do what you need - it returns a resultset

insert into @rt -- <-- here it works fine
select top 1 fbn from tnt_fbn where used != 1 order by fbn asc

select * from @rt
return

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-15 : 09:55:24

Use a Function instead?

From BOL


USE pubs
GO
CREATE FUNCTION SalesByStore (@storeid varchar(30))
RETURNS TABLE
AS
RETURN (SELECT title, qty
      FROM sales s, titles t
      WHERE s.stor_id = @storeid and
      t.title_id = s.title_id)

Brett

8-)

Greex
Starting Member

3 Posts

Posted - 2004-11-15 : 11:25:32

quote:
Originally posted by nr

Probably this will do what you need - it returns a resultset

insert into @rt -- <-- here it works fine
select top 1 fbn from tnt_fbn where used != 1 order by fbn asc

select * from @rt
return

Aye! That did the trick.
Thanks all for the quick response :)

"Cursors are useful if you don't know sql." <-- too true :)

Greex
Starting Member

3 Posts

Posted - 2004-11-15 : 11:26:48

I was thinking too C-ish :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources