CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedureserror-handling
BuddyJoe
BuddyJoe

Reputation: 71171

Error Handling in Current and Older Versions of SQL Server Stored Procedures

What is the prefered way to handle errors in 2005+ versions of SQL Server? And what about versions prior to 2005?

I'm working on some older T-SQL code and I'm finding this expression everywhere:

SELECT CustomerID FROM Customers WHERE CustomerNumber = @customer_number

SET @ERR = @@Error
IF @ERR <> 0 
RETURN @ERR

Assume CustomerID is an int. This seems like this would be a bad practice. How do you know if you got an ID back or an Error # ? It seems to me RAISEERROR is the better path. Forgive me if this seems basic - it has been a while since I have dealt with older procs.

Upvotes: 1

Views: 75

Answers (1)

Charles Bretana
Charles Bretana

Reputation: 146603

Because CustomerId would be returned in Tabular Data Stream (TDS) Resultset, whereas the error number is returned as the return value of the function call. Regardless of what database access technology you are using (ODBC, ADO, ADO.Net, etc.) these two things are different pieces of the return data package, each one of which requires specific, different client side syntax to access.

Upvotes: 2

Related Questions