Output parameters and @@identity

[tmcain]

I am having trouble returning a value to an asp page. The set up I have is a user enters data in a form. This info is inserted into a table and assigned an auto-generated key. I want the stored procedure I have written to return the key back to the page for another stored procedure to use as an input parameter.
I have examined the logic and syntax of the ASP page (along with at least 6 other ASP programmers) and the page checks out fine. For some reason the output parameter isn't returning like it should.
Would someone look at the stored procedure I have written and let me know if you see a problem? I would also appreciate any suggestions of how to debug it in sql analyzer.(Note*I have done this and it seems to work okay, but maybe I am not testing right.)
If you also have other ideas as to how to accomplish the task please let me know. I am getting desperate with this!
Thanks
T

 

[bperry]

here's an example stripped down to the bare bones.
i.e. no input parameters, and no error checking (which ordinarily is essential).
-------------------------
CREATE PROCEDURE dbo.sprInsertHeader (
@parmIdentity int = 0 output,
)
AS
SET NOCOUNT ON

Insert into dbo.RentalHdr (blah blah)
select @parmIdentity = Scope_Identity()

return
GO
-----------------------
I am thinking the problem is perhaps on the client side: your ASP page might not be correctly set up to receive the output paramter.

 

[tmcain]

Sorry! I guess I haven't had enough coffee.
Here is the stored procedure:


--This Procedure creates a basket for the Outgoing Shipment Form

CREATE Procedure sp_CreateShipment

@Employee varchar(50),
@idShipment int output

As

-- Select @Employee, @idShipment



Begin Tran
Insert into Basket(Employee)
Values (@Employee)
Commit Tran

-- Select @@identity as "@@identy"
set @idShipment = @@identity
Return @idShipment
select @idShipment output


-- Select @idShipment as "idShipment"
Return
GO

 

[bperry]

P.S.
Where I have Scope_Identity() in my earlier post, you would probably have @@Identity. (They are nearly the same thing.)

select @parmIdentity = @Identity

My apologies if that was misleading to you.

bperry

 

[bperry]

P.S. Again!

And, of course, I made another dumb typo:

select @parmIdentity = @@Identity

 

[bperry]

Based on your example, this should really be all you need.
I had a Select instead of a Set in my example, but I'm not thinkng that should matter. (try it if you like.)

If that doesn't work, I'd look at the client side.

CREATE Procedure sp_CreateShipment
@Employee varchar(50),
@idShipment int output
AS
Insert into Basket(Employee) Values (@Employee)
Select @idShipment = @@identity
Return

 

[bperry]

I think (?) this is how you would set up in Query Analyzer to test your SP. If you can trap your identity value here, then you know your SP is working, and you can focus on the client.
(untested)

declare @idvalue int
exec sp_CreateShipment 'Billy', @idvalue output
Select @idvalue

 

[tmcain]

Thanks!
I have tested and it looks fine. So for some reason my page isn't working properly.

 

[bperry]

Within ASP, there's at least three ways I know about for calling SPs and passing paramters. Here's one way that I use in a similar situation, so perhaps it might give you an idea where to look.
-------------------------
Dim adCmdStoredProc
Dim adInteger
Dim adChar
Dim adVarChar
adCmdStoredProc = 4
adInteger = 3
adChar = 129

Dim parmIn
Dim parmOut
Dim parmInOut
Dim parmRetVal
parmIn = 1
parmOut = 2
parmInOut = 3
parmRetVal = 4

objCmd.CommandText = "dbo.sprInsertHeader"
objCmd.CommandType = adCmdStoredProc

objCmd.Parameters.Append objCmd.CreateParameter("ReturnCode", adInteger, parmRetVal,,0)
objCmd.Parameters.Append objCmd.CreateParameter("EmployeeName", adChar, parmIn,50, parmVend)
objCmd.Parameters.Append objCmd.CreateParameter("IdentityValue", adInteger, parmOut,,0)

On Error Resume Next
objCmd.Execute
---------------------------------

 

[tmcain]

In case anyone refers to this in the future, I found the problem. The ASP page was treating my stored proc output param like a resultset so I had to close the recordset before I could use the parameters.

Here is the code I had to insert right after my cmd.execute:

While Not rst Is Nothing
Set rst = rst.NextRecordset()
Wend