returning output parameter - 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)

returning output parameter

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-17 : 20:24:12

I have a stored procedure and the declarations are like

@through_date datetime,
@status varchar(20),
@instat_id int OUTPUT
When I insert a record to the tbl_reint table with the through_date and status a auto incremental value instat_id is created and now I need to return this value like

select @instat_id =@@identity
but how will I check the return value with the query analyzer

Declare @temp int
select @temp = execute usp_reater '12/12/2004','N'
print @temp

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-17 : 22:05:34

I'm pretty sure its:

Declare @temp int

execute usp_reater '12/12/2004','N', @temp

print @temp

Corey

gpl
Posting Yak Master

195 Posts

Posted - 2004-09-18 : 04:52:06

Ive always done


Declare @temp int

execute usp_reater '12/12/2004','N', @temp Output

print @temp

Graham

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-18 : 11:48:52

oops.... you're right... i don't ever actually use outputs so i forget...

Corey

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-18 : 21:44:57

got it right.. thanks guys.

Kristen
Test

22859 Posts

Posted - 2004-09-19 : 01:41:27

Seventhnight:"i don't ever actually use outputs so i forget"

You'll be in trouble with the thought police for not returning your error information that way!

Kristen

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-19 : 10:46:51

I don't have errors

Corey

SamC
White Water Yakist

3467 Posts

Posted - 2004-09-19 : 16:03:38

My 2 cents on style... When a proc has a lot of paramters, I like to name 'em all instead of relying on position:

DECLARE @instat_ID INT
DECLARE @returnvalue INT
EXEC @returnvalue = usp_reater @through_date='12/12/2004',@status='N', @instat_id=@instat_id OUTPUT
print @returnvalue
print @instat_id

Sam

Kristen
Test

22859 Posts

Posted - 2004-09-20 : 09:12:54

Probably better to always name them - some blighter is bound to bung an extra parameter "in the middle" ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources