How to retrieve stored procedure error code in C#?

[David Carr]

Hi there,

My SQL server 2000 stored procedures are generating different error codes.
I am able to view these error codes when I run the stored proc in Query
Analyzer. I am hoping to be able to obtain in my C# web service.
Specifically, I am using SqlCommand and SqlDataReader objects for executing
the stored procedure.

Is there a simply mechanism/method to call to obtain the return code? I
have seen some mention of the stored procedure having to stuff it into a
parameter - I am hoping that I don't have to modify each of my stored procs
to do that.

Thanks in advance,
David

 

[Mark Ashton]

By generating error codes, do you mean with the RAISEERROR or PRINT
statement?
You can listen to those with the SqlConnection.InfoMessage event.

SqlConnection connection = new SqlConnection(connectionstring);
connection.InfoMessage += new SqlInfoMessageEventHandler(InfoMessage);
connection.Open();

private static void InfoMessage(object sender, SqlInfoMessageEventArgs args)
{
foreach(SqlError arg in args.Errors) {
Response.Write("Number={0}, Message={1}", arg.Number, arg.Message);
}
}

 

[David Carr]

Thx for the reply Mark.

To better clarify things for all, my SQL stored proc is given below and
returns a -2 if there is are duplicate records, otherwise 0. I now want to
retrieve this value through my C# code. Any ideas on the most simple and
elegant way to achieve this?

Thanks,
David

CREATE PROCEDURE dbo.PersonGet
@IDDistributor int,
@sUsername nvarchar(50),
AS

DECLARE @nCount int;
SELECT @nCount = Count(*)
FROM Person
WHERE (IDDistributor = @IDDistributor) AND (Username = @sUsername)

-- Should be unique, otherwise return -2 to indicate this.
IF (@nCount > 1)
BEGIN
-- Clear the returned set
SELECT NULL AS ID;
RETURN -2;
END

-- Otherwise, go ahead and perform the query again to return the result set
-- CODE OMITTED

RETURN 0;
GO

 

[William \(Bill\) Vaughn]

Sure, add another parameter "@fred" as
@fred int OUTPUT
In your SP code, add
SET @fred = <whatever>

In your application add another Parameter to the Parameters collection and
set the Direction to Output. After the DataReader is closed, @fred will be
populated with the answer.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Mark Ashton]

Add another parameter with name @RETURNVALUE and parameter.Direction =
ParameterDirection.ReturnValue. The name doesn't matter as much, but the
parameter direction does. The datatype will always be an Int32.

All Sql Server stored procedures have a return value parameter, though on
the client they are frequently ignored. The return value is a true output
only were other parameters against sql server are really either input or
inputoutput.

 

[David Carr]

Mark and William - thx for the replies, as they pointed me in the right
direction.

To summarise for any other eyes, the approach taken has been to have
different return values as indicated in the SQL below. Then the C# code can
as follows. Note that the return value cannot be obtained until the
SqlDataReader.Close() has been called.

Cheers,
David

....
SqlCommand cmdDist = new SqlCommand("PersonGet", m_oConn);
cmdDist.CommandType = CommandType.StoredProcedure;
cmdDist.Parameters.Add("@IDDistributor", lIDDistributor);
cmdDist.Parameters.Add("@sUsername", sUsername);
// Set up the return value
SqlParameter prmReturn = new SqlParameter("@ReturnValue", SqlDbType.Int);
prmReturn.Direction = ParameterDirection.ReturnValue;
cmdDist.Parameters.Add(prmReturn);
myReader = cmdDist.ExecuteReader();

// If data has been returned.
if (myReader.HasRows)
{
// Obtain the IDPerson
myReader.Read();
if (!myReader.IsDBNull(0))
{
long lIDPerson = myReader.GetInt32(0);
// Close the reader
myReader.Close();

// Now that the reader has been closed, obtain the return code from
the 'PersonGet' stored procedure.
long lResult = cmdDist.Parameters["@ReturnValue"].Value.ToString())
== 0);
....