July 11, 2002 at 11:25 pm
Hello,
i am using a stored procedure to insert a record into a table with an Indentity column.
The Sp takes an output parameter to return the value of the indentity column inserted.
I use ADO Command Object to call the stored procedure.
The ADO parameter direction is AdParamOutput
When the direction was AdParamInputOutput, and I had passed a NULL value,
my output also was a Null value
Though I am doing
Set @OutParam = @@Identity
in the Stored Proc
Why does the Stored Proc not update the output param inspite of the the parameter being defined as output in the definition of the stored proc.
I am unable to get the new identity value in ADO.
Regds,
RB
July 12, 2002 at 5:44 am
Declaring it as inputoutput should work. Have you verified that your proc is working by adding a print after the set? Also, if you have SQL2K a better method is to use Scope_Identity().
Andy
July 16, 2002 at 10:40 pm
quote:
Declaring it as inputoutput should work. Have you verified that your proc is working by adding a print after the set? Also, if you have SQL2K a better method is to use Scope_Identity().Andy
http://www.sqlservercentral.com/columnists/awarren/
Yep tried that before posting.
In fact I tried a print just before the return statement. However the SP shows the value properly but the ASP does not.
I would like to add more clarity to the problem definition.
This does not happen always. It happens only when I pass a large value in a text field.
The way my sp works is somewhat like this.
create proc sp_a
@intop int output,
@col1 int,
@col2 varchar(30)
@col3 int,
@coltext text
as
begin tran
insert into tbl_master
values (@col1,@col2)
Set @intOp = @@Identity
Insert into
tbl_Detail_Simple
values(@intOp,col3)
if @@Error <> 0
Begin
rollback tran
return @@error
End
Insert into
tbl_Detail_Text
values(@intOp,colText)
if @@Error <> 0
Begin
rollback tran
return @@error
End
Commit Tran
--print @intOp
return @@error
All the records are inserted properly
but the output is NULL if the ASP code has adparaminputoutput and I pass NULL.
The value is a random value if I make it
adParamOutput
Any help would be appreciated
RB
July 17, 2002 at 10:47 am
Would you post the code that is calling the stored procedure?
-Mike
Michael Levy
Michael Levy
ma_levy@hotmail.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply