I encountered a stored procedure that had the following error handling block immediately after an update attempt. The following were the last lines of the SP.

Is there any benefit of doing this? It appears to me as though this code is just rethrowing the same error that it caught without any value added and开发者_开发问答 that the code would presumably behave 100% the same if the Try Block were ommited entirely.

Would there be ANY difference in the behavior of the resulting SP if the TRY block were ommitted?

BEGIN CATCH

SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSev = ERROR_SEVERITY(), @ErrState = ERROR_STATE()
        RAISERROR (@ErrMsg, @ErrSev, @ErrState)

END CATCH

Barring the fact that the "line error occured on" part of any message returned would reference the RAISERROR line and not the line the error actually occured on, there will be no difference. The main reason to do this is as @Chris says, to allow you to programmatically use/manipulate the error data.

What we usually do in our stored procedure is to write the catch block like this

BEGIN CATCH
  DECLARE @i_intErrorNo int          
  DECLARE @i_strErrorMsg nvarchar(1000)          
  DECLARE @i_strErrorProc nvarchar(1000)          
  DECLARE @i_intErrorLine int          

  SELECT @i_intErrorNo=Error_Number()          
  SELECT @i_strErrorMsg=Error_Message()          
  SELECT @i_strErrorProc=Error_Procedure()          
  SELECT @i_intErrorLine=Error_Line()   

  INSERT INTO error table ////// Insert statement. 

END CATCH 

This is something we use to do to store error. For proper message to user, I always use the output parameter to the stored procedure to show the detailed/required reason of the error.

if you look on the msdn page for RAISERROR then you see this general description:

Generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct.

It appears that the "calling application" will get the error message. It may be that the creator of the stored procedure wanted only the error message, severity, and state to be reported and no other options that can be added. This may be because of security concerns or just that the calling application did not need to know the extra information (which could have been verbose or overly detailed, perhaps).

There is a subtle difference, as demonstrated below.

First setup the following:

CREATE TABLE TMP
( ROW_ID int NOT NULL,
  ALTER TABLE TMP ADD CONSTRAINT PK_TMP PRIMARY KEY CLUSTERED (ROW_ID)
)
GO
CREATE PROC pTMP1
AS
BEGIN TRY
  INSERT INTO TMP VALUES(1)
  INSERT INTO TMP VALUES(1)
  INSERT INTO TMP VALUES(2)
END TRY
BEGIN CATCH
  DECLARE @ErrMsg varchar(max)= ERROR_MESSAGE(),
          @ErrSev int = ERROR_SEVERITY(),
          @ErrState int = ERROR_STATE()
        RAISERROR (@ErrMsg, @ErrSev, @ErrState)
END CATCH
GO
CREATE PROC pTMP2
AS
  INSERT INTO TMP VALUES(1)
  INSERT INTO TMP VALUES(1)
  INSERT INTO TMP VALUES(2)
GO

Now run the following:

SET NOCOUNT ON
DELETE TMP
exec pTMP1
SELECT * FROM TMP
DELETE TMP
exec pTMP2
SELECT * FROM TMP
SET NOCOUNT OFF
--Cleanup
DROP PROCEDURE pTMP1
DROP PROCEDURE pTMP2
DROP TABLE TMP

You should get the following results:

Msg 50000, Level 14, State 1, Procedure pTMP1, Line 12
Violation of PRIMARY KEY constraint 'PK_TMP'. Cannot insert duplicate key in object 'dbo.TMP'. The duplicate key value is (1).
ROW_ID
-----------
1

Msg 2627, Level 14, State 1, Procedure pTMP2, Line 4
Violation of PRIMARY KEY constraint 'PK_TMP'. Cannot insert duplicate key in object 'dbo.TMP'. The duplicate key value is (1).
The statement has been terminated.
ROW_ID
-----------
1
2

Notice that the TRY..CATCH version did not execute the third INSERT statement, whereas the pTMP2 proc did. This is because control jumps to CATCH as soon as the error occurs.

NOTE: The behaviour of pTMP2 is affected by the XACT_ABORT setting.

Conclusion

The benefit of using TRY..CATCH as demonstrated depends on how you manage your transaction boundaries.

• If you roll-back on any error, then the changes will be undone. But this doesn't eliminate side-effects such as addtional processing. NOTE: If a different session simultaneously queries TMP using WITH(NOLOCK) it may even be able to observe the temporary change.
• However, if you don't intend rolling back a transaction, you may find the technique is quite important to prevent certain data changes being applied in spite of an earlier error.