Handling error in stored procedure
In addition to passing output parameters back to the calling program, stored procedures also pass back a return value. By default, this value is zero. If an error occurs during the execution of a stored procedure you may want to pass a value back to the calling environment that indicates the error that occurred. To do that you use the RETURN statement and the @@ERROR function.
The @@ERROR system function returns the error number that's generated by the execution of the most recent SQL statement. If the value is zero, it means that no error has occurred. The stored procedure listed below uses this function to test whether a DELETE statement that deletes a row from authors table is successful.
CREATE PROC spDeleteShashi @FirstName varchar(50)
DECLARE @ErrorVar int
DELETE FROM SHASHI WHERE fn_name = @FirstName
SET @ErrorVar = @ERROR
IF @ErrorVar <> 0
PRINT 'An Unknown Error Occurred'
RETURN statement immediately exists the procedure and returns an optional integer value to the calling environment. If you don't specify the value in this statement the return value is zero.