sql - 经典 ASP 从 SQL2005 获取 SCOPE_IDENTITY() 值

Question

我不知道如何从 SQL2005 存储过程中将 SCOPE_IDENTITY() 返回给我的变量。

我的 sSQL 字符串:

sSQL = "EXEC [sp_NewClaim] " & Chr(34) & ClaimNumber & Chr(34) & ", " & Request.Cookies("UserID") & ", " & Request.Cookies("MasterID") & ", " & Chr(34) & strRestaurante & Chr(34) & ", " & Chr(34) &  Fecha & Chr(34) & ", " & Chr(34) & Hora & Chr(34) & ", " & Chr(34) & Request("Tiempo") & Chr(34) & ", " & Chr(34) & Request("Luz") & Chr(34) & ", " & Chr(34) & Request("Desc") & Chr(34) & ", " & Chr(34) & Request("incidente") & Chr(34) & ", " & Chr(34) & Request("codigos") & Chr(34) & ", False, 0; SELECT RecordNumber = SCOPE_IDENTITY()"

我的 sSQL 输出:

EXEC [sp_NewClaim] "W200811", 7, 8, "Otro -- WORK PLEASE", "11/19/2008", "01:19 PM", "Nublado", "Mala", "asdasd", "uyiuyui", "C-Junta", False, 0; SELECT RecordNumber = SCOPE_IDENTITY()

执行我的 SQL 命令:

Set rsData= Server.CreateObject("ADODB.Recordset")
rsData.Open sSQL, conDB, adOpenKeyset, adLockOptimistic

尝试输出 SCOPE_IDENTITY() 会产生一个空变量(无输出):

Response.Write("<br />Record Number: " & rsData("RecordNumber"))

存储过程正确运行。我的信息毫无问题地存储到我的数据库中。 RecordNumber 是具有标识的列，存储过程已将 @RecordNumber 定义为输出:

USE [db_clcinsurance_com]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE sp_NewClaim
 (
    @ClaimNumber nvarchar(50),
    @blah............
    .................
    @RecordNumber INT OUTPUT
    )
AS

BEGIN

    INSERT INTO Accidente (ClaimNumber,........., RecordNumber)

    VALUES (@ClaimNumber,....., @RecordNumber)

    SET @RecordNumber = SCOPE_IDENTITY();

END

Answer 1

对于您的存储过程，请执行以下操作:

CREATE PROCEDURE sp_NewClaim
 (
    @ClaimNumber nvarchar(50),
    @blah............
    .................
)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO Accidente (ClaimNumber,........., RecordNumber)
        VALUES (@ClaimNumber,....., @RecordNumber)

    SELECT SCOPE_IDENTITY()
END

然后以与检索任何其他查询结果相同的方式获取 ID。