Listing 3: Error-Handling Template for COM+ and T-SQL Environments
CREATE PROCEDURE proc_1
(@param1 int,  @param2 char(2) )
AS

BEGIN TRAN  
SELECT / UPDATE / INSERT / DELETE . . .
  
-- Store system-function values using one statement.
SELECT       @error_stat = @@ERROR,     
         @row_count  = @@ROWCOUNT       

IF @error_stat <> 0  
BEGIN
   ROLLBACK TRAN  
   RAISERROR('suitable message', severity, state)
   RETURN  @error_stat       -- Not just 1 or -1
End

IF @rowcount <> [ expected value]
BEGIN
            ROLLBACK TRAN
            RAISERROR('suitable message', severity, state)
             RETURN -999     -- Standardized return code for this condition.
END
COMMIT TRAN
RETURN 0                      -- Success