Output Parameter Issue

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to use a datareader to execute a stored procedure and records from a table. I want to trap any possible errors in the stored procedure and return the error message via an output parameter. But, the output parameter is empty (nothing!) any time after I execute a query
For example, say I have the stored procedur

CREATE PROCEDURE sp_Some_Pro
@OutMsg varchar(200) = '' OUTPU
A
SET @OutMsg = 'test
RETUR

I do the following in my code after the ExecuteReader statement

sOutMsg = CType(arParms(0).Value, String

and sOutMsg is "test". But when the stored procedure has a select statement in it lik

CREATE PROCEDURE sp_Some_Pro
@OutMsg varchar(200) = '' OUTPU
A
SELECT * FROM AnyTabl
SET @OutMsg = 'test
RETUR

I get sOutMsg equal to nothing

I also got the same result when I tried to use just straight ADO instead of ADO.NET

Has anyone seen this before
 
CREATE PROCEDURE sp_Some_Proc
@OutMsg varchar(200) = '' OUTPUT
AS
SELECT * FROM AnyTable
SET @OutMsg = 'test'
RETURN
I get sOutMsg equal to nothing.
Has anyone seen this before?
Click to expand...

I am not sure about this, but try closing the datareader before accessing
the output parameter.

- Klaus
 
Klaus Jensen said:
I am not sure about this, but try closing the datareader before accessing
the output parameter.
Click to expand...

That should work for this test proc, but if there is an error ADO.NET
SQLClient will not populate the output parameters. The error message will
propagate to the client and SQLClient will raise an exception based on the
error message. No not only will it not work, it's completely unnecessary.

David
 
William Ryan eMVP said:
David:

I'm not following you, are you saying closing the reader is unnecessary? I
thought it had to be closed in order to grab the results, or at least you
had to process the entire resultset?

--
Click to expand...

Yes it is necessary to close the cursor before accessing the output
parameters. What won't work is your whole idea of passing the an error
message back using an output parameter. If you have a real error instead of
just a made up message, then neither the DataReader nor the output parameter
will be populated in ADO.NET.

EG

CREATE PROCEDURE sp_Some_Proc
@OutMsg varchar(200) = '' OUTPUT
AS
SELECT * FROM AnyTable
SET @OutMsg = 'test'
raiserror('some error occured',16,)
RETURN 1

David
 
Back
Top