Insert Retrieve SP

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Axi - 23 Feb 2004 16:40 GMT

Hi,

I made an insert using a Stored Procedure (MS SQL 2000).
How can I store the ID of the last inserted record into a session var ?
I have code to do this with a regular "Insert" page (Using Dreamweaver), but
didnt work for the SP.

This is part of the code I have for the insert for the ASP (Dreamweaver).

code ....
MM_editCmd.Execute();


var rsNewAutoIncrement = MM_editCmd.ActiveConnection.Execute("select
@@identity")
Session("svReq_ID") = rsNewAutoIncrement(0).Value




This is part of the code that I am using with the Insert Stored Procedure:

AddInvoice.Parameters.Append AddInvoice.CreateParameter("@State", 200,
1,25,AddInvoice__State)
AddInvoice.Parameters.Append AddInvoice.CreateParameter("@Zip", 200,
1,25,AddInvoice__Zip)
AddInvoice.CommandType = 4
AddInvoice.CommandTimeout = 0
AddInvoice.Prepared = true
AddInvoice.Execute()

%>

I need to add code here to retreive the ID just insterted, but the above
code didnt work

Thanks in advance,

Axi
ASP VB / MS SQL 2000

Reply to this Message

Greg Hoover - 24 Feb 2004 02:01 GMT

Try something like the following:

SELECT SCOPE_IDENTITY() AS [InsertedId]

This assumes that the primary key column of the table you are inserting into
is an Identy column.

Read about @@IDENTITY and SCOPE_IDENTITY() in the Sql Server Books Online.
In general you almost always want to use SCOPE_IDENTITY instead of
@@IDENTITY.

- Greg

> Hi,
>
[quoted text clipped - 36 lines]
> Axi
> ASP VB / MS SQL 2000

Reply to this Message

MGFoster - 24 Feb 2004 05:41 GMT

Your stored procedure (SP) needs to return the @@Identity variable.
Something like this:

Create Procedure usp_MyProcedure @input Int, @output Int OUTPUT
AS
... your stuff ...

INSERT INTO .... < table w/ Identity column > ...

- -- Get the Identity value just created by the INSERT INTO
SET @output = @@IDENTITY

=====

Then you'll have to declare an Output variable in the ADO call &
assign a variable the result of the SP call. E.g. (p. 93 of _ADO 2.0
Programmer's Reference_, Wrox Publishers):

objCmd.Parameters.Append objCmd.CreateParameter("ReturnValue", _
adInteger, adParamReturnValue)

Set objRec = objCmd.Execute

intParamAvail = objConn.Properties("Output Parameter Availability")
If intParamAvail = 4 Then
' parameter not available until recordset is closed
objRec.Close
End If
intReturnValue = objCmd.Parameters("ReturnValue")

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

> Hi,
>
[quoted text clipped - 31 lines]
> I need to add code here to retreive the ID just insterted, but the above
> code didnt work

Reply to this Message