Monday, January 7, 2013

Error Catching with @@ERROR and @@ROWCOUNT

Error Catching with @@ERROR and @@ROWCOUNT

@@ERROR  - Error Number
@@ROWCOUNT - Rows affected

Retrieve both values directly after a modification statement

The following code fails as the CostRate cannot be negative.
This is due to a Column Constraint on the location table (SQL 2012 Adventureworks)
It does however demonstrate catching the error number

INSERT INTO [Production].[Location] ([Name],[CostRate],[Availability],[ModifiedDate])
VALUES('Tool ',-2,    0.00, GETDATE())
SELECT @myError = @@ERROR , @myRowcount = @@ROWCOUNT

SELECT 'Error Number ' + CAST(@myError AS VARCHAR(10))
SELECT 'Row Count ' + CAST(@myRowcount AS VARCHAR(10))

Using @@ROWCOUNT to test data existance

No comments: