Re: How to retrieve stored procedure error code in C#?

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.
__________________________________

"David Carr" <David_Carr@xxxxxxxxxxxxxxxx> wrote in message
news:%23Mr9CgdoFHA.2484@xxxxxxxxxxxxxxxxxxxxxxx
> 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
>
>
>
> "Mark Ashton" <markash@xxxxxxxxxxxxxxxxxxxx> wrote in message
> news:OTCUYw5nFHA.860@xxxxxxxxxxxxxxxxxxxxxxx
>> 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);
>> }
>> }
>>
>> --
>> This posting is provided "AS IS", with no warranties, and confers no
> rights.
>> Please do not send email directly to this alias. This alias is for
> newsgroup
>> purposes only.
>> "David Carr" <David_Carr@xxxxxxxxxxxxxxxx> wrote in message
>> news:%2379YZl5nFHA.3304@xxxxxxxxxxxxxxxxxxxxxxx
>> > 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
>> >
>> >
>>
>>
>
>


.

Relevant Pages