Returning error from stored proc

[TheNortonZ]

This is probably a stupid question but its been so long since I've had to do
it with ADO, I can't decide which way to do this in ADO.Net.

I have basic stored procedures (SQL 2000/MSDE) that do inserts, updates and
deletes. I call these from an SQLDataAdapter Update/Insert/Delete -> Update
command.

So, I can put a try/catch around the adapter Update call, but should I just
be trying to catch a Sqlexception? How should I throw the error within the
stored proc? For example, I can do:

DELETE FROM tblMine WHERE myID = @iID

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN A
RETURN ?
END

Should I return a value? I suppose a basic sample might be all I need.

Thanks.

STom

 

[Mary Chipman]

Returning data errors from stored procedures is tricky, because the
server doesn't necessarily consider everything an error that you want
it to. In addition, @@error doesn't persist past the following
statement, it just gets reset to 0. The best course of action is to
create local variables to capture not only @@error but also
@@rowcount, and then test to see if if @@error <> 0 and @@rowcount > 0
before you roll back or commit an explicit transaction. Make sure that
your stored procedure returns success/failure in an output parameter
or return value, and test for that in your ADO.NET code. Also remember
that SET NOCOUNT ON should be the first line of code in the sproc.
Basically you want to do *all* of the processing on the server and
just return success/failure messages to your client code to process.
Here's what the code would look like from the next line after an
INSERT statement. It assumes you've declared @Err and @Rows int local
variables and that @EmployeeID, @RetCode and @RetMsg are declared as
OUTPUT parameters.

SELECT @Err = @@ERROR, @Rows = @@ROWCOUNT
IF (@Err <> 0) GOTO HandleErr

IF @Rows > 0
SELECT @EmployeeID = @@IDENTITY,
@RetCode = 1,
@RetMsg = 'New Employee Added'
ELSE
SELECT @EmployeeID = 0,
@RetCode = 0,
@RetMsg = 'New Employee Not Added'
RETURN

HandleErr:
SELECT @EmployeeID = 0,
@RetCode = 0,
@RetMsg = 'Runtime Error: ' + CONVERT(VarChar, @Err)
RETURN
GO

--Mary

 

[TheNortonZ]

Mary,

Thanks for the great information!

Now, when I am using a data adapter Update function, how would I check for
the return value. The typical return value from an Update call is the number
of rows.

Thanks again.

Norton.

 

[Mary Chipman]

Reference the value property of the command.

--Mary