Return Value Best Practices

[Elegabalus]

I've got a simple question:

I'm building a web-based app, and I'm wondering what is the best way to get a single value out of the database? For instance, if I'm inserting a record into a table, and I want to get the identity value back out, should I be using an OUTPUT variable, a return value, or simply Selecting the identity?

i.e., which way is best:

1)

DECLARE @Identity int OUTPUT
INSERT (...)
VALUES (...)
SET @Identity = SCOPE_IDENTITY()

2)

INSERT (...)
VALUES (...)
RETURN SCOPE_IDENTITY()

3)

INSERT (...)
VALUES (...)
SELECT SCOPE_IDENTITY()

As far as I am aware, all three of these methods work, but I'm just wondering which is the best way, and if there are any downsides to any of them.

 

[SQLSister]

Either the output variable or the select statement will do. It is best to save return variables for a variable whcih indicates if the procedure failed or succeeded. I personally usually set the identity variable to a a variable and then do a select @Identity to return the value as the result of the sp.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[Elegabalus]

Ah, okay.

Are there reserved return values? I was reading something online that suggested that the return value will default to 0 if the stored procedure executes without errors. That is, the return value will have a value even if you don't explicitly return a value in the sproc.

Is that true? And is that why you shouldn't use the return value to return an identity?

 

[SQLDenis]

yes, see example below

create proc testreturn
as
select getdate()

go

declare @i int
exec @i =testreturn
select @i

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/