0

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

Dale K
  • 25,246
  • 15
  • 42
  • 71
gwill1010
  • 1
  • `SELECT @ID` instead of `RETURN`. Is there any reason that `@ID > 0` would not be true, why do you need the `IF`? And what happens if you decide to restart your Identity column from negative numbers – Charlieface Nov 02 '21 at 20:32
  • 1
    @Charlieface "I am trying to utilize a vendors stored procedure that I can not change." – Dale K Nov 02 '21 at 21:32

0 Answers0