SCOPE_IDENTITY()始终返回0 [英] SCOPE_IDENTITY() always returning 0

问题描述

我的存储过程是:

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.

For some reason it is returning 0 every single time.

我正在这样执行它:

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

我在做什么错了?

推荐答案

似乎您正在使用 ExecuteScalar()，而不是查看输出参数来获取标识.在执行后查看输出参数的值，或者选择 SCOPE_IDENTITY()作为查询的结果.

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.

这是一个简单的示例，应该与 ExecuteScalar()一起使用:

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.

这篇关于SCOPE_IDENTITY()始终返回0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！