我无法弄清楚如何从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
对于您的存储过程,请执行以下操作:
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。