Using Select Scope_Identity() in a stored procedure without an output variable

320 Views Asked by At

I am trying to utilize a vendors stored procedure that I cannot change. In C# I need the Scope_Identity value. If I could I would set @ID as an output variable. Running the procedure in SQL Developer does return the proper ID.

CREATE PROCEDURE [dbo].[spTest]
    (@CountryName nvarchar(20))
AS
    DECLARE @ID int
    SET NOCOUNT OFF;

    INSERT INTO [dbo].[CountryImages]([CountryName] )
    VALUES (@CountryName );

    SELECT @ID = SCOPE_IDENTITY()

    IF @ID > 0 
    BEGIN 
        RETURN @ID
    END
GO

I've tried this, but null is returned.

C#

....  
cmd.Parameters.AddWithValue("@CountryName", "abc");                   
conn.Open();   

var ID = (int)cmd.ExecuteScalar();

Thanks

0

There are 0 best solutions below