Retrieve output parameters from failed stored procedure

[stuart]

Is it possible to retrieve output parameters (adParamOutput) from a
stored procedure (asp/sql server) even when the procedurte fails - or
is it only possible to retrieve return values (adParamReturn). I can
retrieve output values when the procedure succeeds but not when it
fails. If it can be done i will go back and look at my code.

Thanks in advance

stuart

[Ken Schaefer]

You will need some kind of error handling in your sproc. In your error
handling code, set the output parameter.

Cheers
Ken

"stuart" <stuart.cochrane@kellogg.com> wrote in message
news:7ce2ca9e.0402120249.4de69562@posting.google.c om...
: Is it possible to retrieve output parameters (adParamOutput) from a
: stored procedure (asp/sql server) even when the procedurte fails - or
: is it only possible to retrieve return values (adParamReturn). I can
: retrieve output values when the procedure succeeds but not when it
: fails. If it can be done i will go back and look at my code.
:
: Thanks in advance
:
: stuart

[Bob Barrows]

stuart wrote:

> Is it possible to retrieve output parameters (adParamOutput) from a
> stored procedure (asp/sql server) even when the procedurte fails - or
> is it only possible to retrieve return values (adParamReturn). I can
> retrieve output values when the procedure succeeds but not when it
> fails. If it can be done i will go back and look at my code.
>
> Thanks in advance
>
> stuart

Can you provide a short repro script so we can see what you're talking
about? Just write a quicky sproc using one of the pubs or Northwind tables
and show the ado code used to reproduce your issue.

Bob Barrows


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Stuart Cochrane]

Hi Bob,

Below is a sample of asp and tsql code:

Thanks for any help or advice - For some reason if the insert succeeds i
can retrieve the output parameters, but if the insert fails(i.e. if a
primiary key is violated) the output parameters are empty.

Stuart


//asp code

on error resume next

set objCmd = server.createobject("ADODB.command")
objCmd.CommandText = "ps_ProcessLineIns"
objCmd.CommandType = adCmdStoredProc
set objCmd.Activeconnection = objConn

set objParam = objCmd.CreateParameter("@i_pl_code", adVarChar,
adParamInput, 10, pl_code)
objCmd.parameters.Append objParam
set objParam = objCmd.CreateParameter("@i_pl_name", adVarChar,
adParamInput, 50, pl_name)
objCmd.parameters.Append objParam
objCmd.parameters.Append objParam
set objParam = objCmd.CreateParameter("@o_message_id", adVarChar,
adParamOutput)
objCmd.parameters.Append objParam
set objParam = objCmd.CreateParameter("@o_message_text", adInteger,
adParamOutput)
objCmd.parameters.Append objParam

objcmd.Execute

o_message_text = objCmd.Parameters("@o_message_id").value
o_message_id = objCmd.Parameters("@o_message_text").value

/////TSQL code


CREATE procedure ps_ProcessLineIns (
@i_pl_code varchar(10) , -- Process Line Code - In
@i_pl_name varchar(50) , -- Process Line Name - In
@i_pl_description varchar(50), -- Process Line description -In
@o_message_id int output, -- Message ID - Out
@o_message_text varchar(100) output -- Message Text - Out
)
as
set nocount on
--
declare @ErrorSave INT
set @ErrorSave = 0
--
Begin
-- Insert Process Line after passing validation.
insert into PROCESS_LINES

(PL_CODE,PL_NAME,PL_DESCRIPTION,F_PA_ID,F_LOV_PROC ESS_LINE_TYPE_ID,STATU
S,
CREATED_DATETIME,CREATED_BY)
values

(@i_pl_code,@i_pl_name,@i_pl_description,@i_pa_id, @i_pl_line_type_id,@i_
pl_status,getdate(),@i_user_id);

-- Trap Error Code
Set @ErrorSave = @@error
if @ErrorSave <> 0 goto errorhandler
--
Set @o_message_id = 0
Set @o_message_text = 'Insert Success'
--
errorhandler:
Set @o_message_id = @ErrorSave
Set @o_message_text = 'Insert Failed'
End
GO






*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Bob Barrows [MVP]]

Does it work as expected when run in Query Analyzer? I.E. if you pass
parameters that cause an error, are the ouput parameters correctly
populated?

--In QA:
declare @i_pl_code varchar(10),@i_pl_name varchar(50),
@i_pl_description varchar(50), @o_message_id int,
@o_message_text varchar(100)

--set the above variables to values that will cause an error in the sproc,
then:
exec ps_ProcessLineIns @i_pl_code,),@i_pl_name,@i_pl_description,
@o_message_id output,@o_message_text output
select @o_message_id [Output Message ID], ,@o_message_text [Output Message
Text]


See below for my initial comments. If my suggested changes do not help,
could you supply a CREATE TABLE script for the PROCESS_LINES table? Relevant
columns and primary key ...

Bob Barrows

Stuart Cochrane wrote:
<snip>

> set objCmd.Activeconnection = objConn
>

Add this line (I added the With block to enable the command to fit your line
width):
with objCmd
..parameters.Append .CreateParameter(@RETURN_VALUE, _
adInteger, adParamReturnValue)
end with

> set objParam = objCmd.CreateParameter("@i_pl_code", adVarChar,
> adParamInput, 10, pl_code)
> objCmd.parameters.Append objParam
> set objParam = objCmd.CreateParameter("@i_pl_name", adVarChar,
> adParamInput, 50, pl_name)
> objCmd.parameters.Append objParam
> objCmd.parameters.Append objParam

Why is this line repeated?

> set objParam = objCmd.CreateParameter("@o_message_id", adVarChar,
> adParamOutput)
> objCmd.parameters.Append objParam
> set objParam = objCmd.CreateParameter("@o_message_text", adInteger,
> adParamOutput)
> objCmd.parameters.Append objParam
>
> objcmd.Execute

objcmd.Execute ,,adExecuteNoRecords

[Stuart Cochrane]

Hi Bob,

Yes, if I run the code in Query Analyzer everything is okay and I
receive the correct output parameters.

Stuart

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Bob Barrows [MVP]]

Stuart Cochrane wrote:

> Hi Bob,
>
> Yes, if I run the code in Query Analyzer everything is okay and I
> receive the correct output parameters.
>
> Stuart
>

So what about the second part of my post ... ?

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.