New error handling method in SQLServer procedures from version 2005. This is the better way of handling errors in SQLServer.

-- Create procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
GO




BEGIN TRY

BEGIN TRANSACTION -- Start the transaction

--insert
INSERT INTO....

--update
UPDATE INTO....



-- Generate divide-by-zero error.
SELECT 1/0;

-- If we reach here, success!
IF @@TRANCOUNT > 0
COMMIT TRANSACTION


END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION

-- Execute error retrieval routine.
EXECUTE usp_GetErrorInfo;

END CATCH;

New way to write transaction in stored procedure:

SET XACT_ABORT ON

BEGIN TRAN
……………………………………
……………………………………
……………………………………

COMMIT TRAN

SET XACT_ABORT OFF

In this approach, you don’t need to check @@error and rollback inside transaction, it will automatically rollback the action when error happens.

Difference between RowNumber, Rank and Dense Rank

CREATE TABLE T (PK INT IDENTITY, A INT, B INT, C INT)
CREATE UNIQUE CLUSTERED INDEX TPK ON T(PK)

INSERT T VALUES (0, 1, 6)
INSERT T VALUES (0, 1, 4)
INSERT T VALUES (0, 3, 2)
INSERT T VALUES (0, 3, 0)
INSERT T VALUES (1, 0, 7)
INSERT T VALUES (1, 0, 5)
INSERT T VALUES (0, 2, 3)
INSERT T VALUES (0, 2, 1)

SELECT *,
ROW_NUMBER() OVER (ORDER BY 8) AS RowNumber,
RANK() OVER (ORDER BY 8) AS Rank,
DENSE_RANK() OVER (ORDER BY 8) AS DenseRank
FROM T

PK A B C RowNumber Rank DenseRank
----- ----- ----- ----- ---------- ---------- ----------
5 1 0 7 1 1 1
6 1 0 5 2 1 1
1 0 1 6 3 3 2
2 0 1 4 4 3 2
7 0 2 3 5 5 3
8 0 2 1 6 5 3
3 0 3 2 7 7 4
4 0 3 0 8 7 4

Notice how the ROW_NUMBER function ignores the duplicate values for column B
and assigns the unique integers from 1 to 8 to the 8 rows while the RANK
and DENSE_RANK functions assigns the same value to each of the pairs of duplicate rows.
Moreover, notice how the RANK function counts the duplicate rows even while
it assigns the same value to each duplicate row whereas the DENSE_RANK function
does not count the duplicate rows.