SCOPE_IDENTITY() always returning 0

Question

My stored procedure is:

ALTER PROCEDURE [dbo].[Insert_QuickLabDump]
    @Specimen_ID [varchar](50),
    @Client_Key int,
    @Outcome [varchar](50),
    @Medications [varchar] (max),
    @Date_Collected date,
@Time_Collected time(0) ,
@Date_Entered date,
@Time_Entered time(0) ,
@Date_Completed date,
@Time_Completed time(0) ,
@Test_Date date ,
@Test_Time time(0) ,

    @Practice_Name [varchar] (500),
    @Practice_Code [varchar] (500),
    @Client_ID [varchar] (500),
    @Requesting_Physician [varchar] (500),
    @Other_Medications [varchar] (max),
    @Order_Comments [varchar] (max),
    @Reference_Number [varchar] (500),
    @Order_Count int,
    @lastrecord INT OUTPUT

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    INSERT INTO [SalesDWH].[dbo].[QuickLabDump]
           ([Specimen ID]
           ,[Client Key]
           ,[Outcome]
           ,[Medications]
           ,[Date Collected],
[Time Collected]  ,[Date Entered] ,
[Time Entered]  ,
[Date Completed] ,
[Time Completed]  ,
[Test Date]  ,
[Test Time]  ,


           [Practice Name]
           ,[Practice Code]
           ,[Client ID]
           ,[Requesting Physician]
           ,[Other Medications]
           ,[Order Comments]
           ,[Reference Number]
           ,[Order Count])
     VALUES
           (@Specimen_ID,
@Client_Key,
@Outcome,
@Medications,
@Date_Collected ,
@Time_Collected ,
@Date_Entered,
@Time_Entered ,
@Date_Completed ,
@Time_Completed,
@Test_Date ,
@Test_Time,
@Practice_Name,
@Practice_Code,
@Client_ID,
@Requesting_Physician,
@Other_Medications,
@Order_Comments,
@Reference_Number,
@Order_Count
);
    select @lastrecord = scope_identity();
END

For some reason it is returning 0 every single time.

I am executing it like this:

public static int Insert_QuickLabDump(QuickLabDump dump)
{
   try
   {
      DbConnection cn = GetConnection2();
      cn.Open();

      // stored procedure
      DbCommand cmd = GetStoredProcCommand(cn, "Insert_QuickLabDump");
      DbParameter param;

      param = CreateInParameter("Specimen_ID", DbType.String);
      param.Value = dump.Specimen_ID;
      cmd.Parameters.Add(param);

       ..... (lots more parameters - same method) ......

       param = CreateOutParameter("lastrecord", DbType.Int32);

       cmd.Parameters.Add(param);

       // execute
       int id=cmd.ExecuteScalar().ToInt();
       return id;

       if (cn.State == ConnectionState.Open)
           cn.Close();
   }
   catch (Exception e)
   {
      throw e;
   }
}

What am I doing wrong?

Answer 1

It looks like you are using ExecuteScalar() and not looking at the output parameter to get the identity. Either look at the value of the output parameter after execution, or select SCOPE_IDENTITY() as the result of your query.

Here's a quick example which should work with ExecuteScalar():

CREATE PROCEDURE dbo.QuickExample

    @Name VARCHAR( 50 )

AS

INSERT INTO dbo.MyTable( Name )
    VALUES( @Name );

SELECT SCOPE_IDENTITY();

GO

Here's how to create a stored procedure with an output parameter:

CREATE PROCEDURE dbo.QuickExample

    @Name   VARCHAR( 50 ),
    @Id INT OUTPUT

AS

INSERT INTO dbo.MyTable( Name )
    VALUES( @Name );

SET @Id = SCOPE_IDENTITY();

GO

If you use an output parameter, you must specify it as one when you call the stored procedure. After calling the stored procedure, the parameter will be populated with the value set in the stored procedure.