problem returning IDENTITY

Hi all,

I have a sp where I only do an insert, and am trying to return the
identity value created. Here's what my sp resemble :

...
AS
SET NOCOUNT ON
-- do the insert
DECLARE @ret
SELECT @ret = SCOPE_IDENTITY()
RETURN @ret

I tried to return directly SCOPE_IDENTITY(), I also tried to change
SCOPE_IDENTITY() with @@IDENTITY, with and without SET NOCOUNT ON...I
don't know what to do anymore. A few times it returned always 1 and at
other times, it was always returning -1. This depended on what options
I've tried, I don't remember what situation returned what value...but
for now, written as above, it is returning -1.

thanks for your help!

ibiza wrote:

> Hi all,
>
> I have a sp where I only do an insert, and am trying to return the
> identity value created. Here's what my sp resemble :
>
> ...
> AS
> SET NOCOUNT ON
> -- do the insert
> DECLARE @ret
> SELECT @ret = SCOPE_IDENTITY()
> RETURN @ret
>
> I tried to return directly SCOPE_IDENTITY(), I also tried to change
> SCOPE_IDENTITY() with @@IDENTITY, with and without SET NOCOUNT ON...I
> don't know what to do anymore. A few times it returned always 1 and at
> other times, it was always returning -1. This depended on what options
> I've tried, I don't remember what situation returned what value...but
> for now, written as above, it is returning -1.
>
> thanks for your help!

It isn't a good idea to use RETURN to return data from a proc. Use
RETURN for error status only: zero = OK, non-zero = error. To return
other values use an output parameter or a result set:

CREATE PROC usp_x
(@param1 INTEGER, @ret INTEGER OUTPUT)
AS
SET NOCOUNT ON;
SET @ret = 123;
RETURN
GO

DECLARE @r INTEGER;
EXEC usp_x @param1 = 1, @ret = @r OUTPUT;
SELECT @r;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

thank you very much for your reply. Well, it does work with an output
parameter!

And I will take note of your remark for my upcoming posts.

Thanks again! :)