SCOPE_IDENTITY()总是返回0

本文关键字：返回 IDENTITY SCOPE | 更新日期: 2023-09-27 18:18:44

我的存储过程是:

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

由于某种原因，每次都返回0。

我是这样执行的:

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;
   }
}

我做错了什么?

SCOPE_IDENTITY()总是返回0

看起来您正在使用ExecuteScalar()，而不是查看输出参数以获得标识。要么在执行后查看输出参数的值，要么选择SCOPE_IDENTITY()作为查询结果。

这里有一个快速的例子，应该与ExecuteScalar()工作:

CREATE PROCEDURE dbo.QuickExample
    @Name VARCHAR( 50 )
AS
INSERT INTO dbo.MyTable( Name )
    VALUES( @Name );
SELECT SCOPE_IDENTITY();
GO
下面是如何使用输出参数创建存储过程: 
CREATE PROCEDURE dbo.QuickExample
    @Name   VARCHAR( 50 ),
    @Id INT OUTPUT
AS
INSERT INTO dbo.MyTable( Name )
    VALUES( @Name );
SET @Id = SCOPE_IDENTITY();
GO

如果使用输出参数，则必须在调用存储过程时将其指定为输出参数。调用存储过程后，将使用存储过程中设置的值填充参数。

相关文章：