Posted by Erland Sommarskog on 08/26/05 00:42

dtwilliams@hotmail.com (dan_williams@newcross-nursing.com) writes:

> OK, i'm trying to do some error checking on stored procedures and am
>
> INSERT INTO Supplier
> (supplierName, accOpenedBy, accOpenedDate)
> VALUES (@supplierName, @userId, getDate())
>
> SET @newSupplierId = SCOPE_IDENTITY()
>
> -- Check for an error creating new supplier
> SELECT @errorCode = @@ERROR

No, you are checking for error an error when retrieving the value from
SCOPE_IDENTITY(). Which never fails, so you will always get 0.

@@error is set after *every* statement.

This is why I always write my code as:

INSERT INTO Supplier (supplierName, accOpenedBy, accOpenedDate)
VALUES (@supplierName, @userId, getDate())
SELECT @err = @@error IF @err <> 0 RETURN @err

And then there is a space to the next statement. That is, conceptually
I view the error-checking bit as part of the statment it belongs to.


Oh! So much easier this will be in SQL 2005!


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

• Next in forum: Re: Managing ntext, text with a long text data
• Prev in forum: Re: Application Roles for Cross-Database Joins
• Thread view: Re: Stored procedure error handling

[Reply to this message]