Catching row count and error number

After some days I am writing this blog.
I had a couple of training programs (total of 5.5 days) and then I need to travel to US (on official visit!) so I couldn't update the blog.
So here is the update.

If you need to track error number and row count of a statement how will you handle it?
Both of these are captured using @@ERROR and @@ROWCOUNT system variables.
But there is a catch:
They reflect the value of the immediate SQL statement irrespective of whether it is a DML statement (INSERT, UPDATE, DELETE & SELECT statements) or just a control statement. (Examples are IF, SET, WHILE) That means if you add SET @Err_Variable = @@ERROR after a DML statement, you will be able to catch the error number, but it will take the statement SET @Row_Variable = @@ROWCOUNT to return 1 always
How to resolve it?

The method is simple:  Add both in the same statement.  As SET does not allow setting the value for multiple variables, you need to use SELECT statement.

Here is the code if you need to do some testing:


CREATE PROCEDURE #strtestproc
AS

DECLARE @Err int
DECLARE @RowCount int

CREATE TABLE #strtest (str1 varchar(10))


--SET @Err =0
--SET @RowCount =0

---Case 1: Error happens & error number is captured first
INSERT INTO #strtest VALUES('Some long string to be placed here')

SET @Err = @@ERROR
SET @RowCount = @@ROWCOUNT

SELECT @Err AS ErrorNumber, @RowCount AS RowCounter
SELECT str1 AS Test1 FROM #strtest

--SET @Err =0
--SET @RowCount =0

--Case 2:Error happens & row counter is captured first
INSERT INTO #strtest VALUES('jvndfnvdfkjvdfgjdsfgnjdlgsbsggcZDXCVds dfsvdfvadfbvdafadvaf')

SET @RowCount = @@ROWCOUNT
SET @Err = @@ERROR

SELECT @Err AS ErrorNumber, @RowCount AS RowCounter
SELECT str1 AS Test1 FROM #strtest

--Case 3:multiple rows are inserted and row counter is captured first
TRUNCATE TABLE #strtest
INSERT INTO #strtest VALUES('dfgtfrg')
INSERT INTO #strtest VALUES('dfgtfrg')

--SET @Err =0
--SET @RowCount =0
INSERT INTO #strtest SELECT * FROM #strtest

SET @RowCount = @@ROWCOUNT
SET @Err = @@ERROR

SELECT @Err AS ErrorNumber, @RowCount AS RowCounter
SELECT str1 AS Test1 FROM #strtest

--Case 4:multiple rows are inserted and Error number is captured first
TRUNCATE TABLE #strtest
INSERT INTO #strtest Values('dfgtfrg')
INSERT INTO #strtest Values('dfgtfrg')

--SET @Err =0
--SET @RowCount =0
INSERT INTO #strtest SELECT * FROM #strtest

SET @Err = @@ERROR
SET @RowCount = @@ROWCOUNT

SELECT @Err AS ErrorNumber, @RowCount AS RowCounter
SELECT str1 AS Test1 FROM #strtest

--Case 5:multiple rows are inserted and Error number & row number are captured at once
TRUNCATE TABLE #strtest
INSERT INTO #strtest VALUES('dfgtfrg')
INSERT INTO #strtest VALUES('dfgtfrg')

--SET @Err =0
--SET @RowCount =0
INSERT INTO #strtest SELECT * FROM #strtest

SELECT @Err = @@ERROR, @RowCount = @@ROWCOUNT

SELECT @Err AS ErrorNumber, @RowCount AS RowCounter
SELECT str1 AS Test1 FROM #strtest

--Case 6:Error occurs and Error number & row number are captured at once
TRUNCATE TABLE #strtest

--SET @Err =0
--SET @RowCount =0
INSERT INTO #strtest VALUES('dfgtfrgvkbzjdfvklamklcvdfsladlvnals')

SELECT @Err = @@ERROR, @RowCount = @@ROWCOUNT

SELECT @Err AS ErrorNumber, @RowCount AS RowCounter
SELECT str1 AS Test1 FROM #strtest

DROP TABLE #strtest
GO

EXEC #strtestproc

DROP PROCEDURE #strtestproc