Classic ASP getting SCOPE_IDENTITY() Value from SQL2005

Question

I can't figure out how to get the SCOPE_IDENTITY() back to my variables from an SQL2005 Store Procedure.

My sSQL String:

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()"

My sSQL Output:

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()

Executing my SQL Command:

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

Trying to Output the SCOPE_IDENTITY() Produces an Empty Variable (No Output):

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

The Store Procedure runs correctly. My Information gets stored into my database with out problems. RecordNumber is the Column with the Identity, and the Store Procedure has defined @RecordNumber as an Output:

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

For your stored procedure, do this:

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

And then get the id the same way you'd retrieve any other query result.

Answer 2

You might try testing to make sure the SCOPE_IDENTITY() function is working as expected- add SELECT @RecordNumber to the end of the sproc and run it manually in Management Studio to confirm that the variable is getting set the way you expect. If that doesn't work try SELECT SCOPE_IDENTITY() to confirm that it is working at all. Finally, hardcode the variable value as a test to make sure that the OUTPUT param is working as it should.

Answer 3

Asp classic is not my strong point, but the ideas are the same.

The problem is you are not returning the identity as a record set, rather as an OUT Parameters. This means the way you are trying to read it is incorrect.

Try Joel's suggestion, or get it via the return code:

Return Scope_Identity()

Alternatively you should build up your query using parameters and specify the last one as your out parameter. Then execute the query and check the value of the last parameter. In .NET that would be (convert to VB as needed):

SqlCommand cmd = new SqlCommand("INSERT INTO Foo (Description) VALUES (@Description); SET @Result = SCOPE_IDENTITY()");
SqlParameter paramDesc = new SqlParameter("@Description", SqlDbType.Int);
cmd.Parameters.Add(paramDesc);
SqlParameter paramResult = new SqlParameter("@Result", SqlDbType.Int);
paramResult.Direction = ParameterDirection.Output;
cmd.Parameters.Add(paramResult);

I hope you are doing some scrubbing of the input as they method of querying is very prone to SQL Injection attacks.

Rob.

Answer 4

I agree with Joel Coehoorn's response, but I wanted to note that you are sending your SCOPE_IDENTITY() variable back as an output parameter, but not retrieving it that way in your ado call. You cannot retrieve an output parameter using the method you are to call the stored procedure.

If you are curious there are some ado examples here to call stored procedures.

Answer 5

I agree with Robert. If you are going to use an output parameter in your stored procedure and call it with dynamic SQL, in your buildup, you'd have to assign a SQL variable to the output parameter and then select that variable. You must also use the OUTPUT keyword when assigning the SQL variable, such as:

sSQL = "DECLARE @RecNo int; EXEC [sp_NewClaim] 'param1', 'param2', etc..... @RecNo OUTPUT; SELECT @RecNo;"