TRY & CATCH those errors!

Writing Functions and Stored Procedures in SQL Server

Meghan Kwartler

IT Consultant

To handle errors or not

What is error handling?

Anticipation, detection and resolution of errors
Maintains normal flow of execution
Integrated into initial design

What happens without error handling?

Sudden shut down or halts execution
Generic error messages without helpful context are provided

Let's TRY

ALTER PROCEDURE dbo.cusp_TripSummaryCreate
  @TripDate nvarchar(30),
  @RideHrs numeric,

  @ErrorMsg nvarchar(max) = null OUTPUT

AS
BEGIN
  BEGIN TRY
      INSERT INTO TripSummary (Date, TripHours)
      VALUES (@TripDate, @RideHrs)
  END TRY
........

Time to CATCH

ALTER PROCEDURE dbo.cusp_TripSummaryCreate
  @TripDate nvarchar(30),
  @RideHrs numeric, 
  @ErrorMsg nvarchar(max) = null OUTPUT
AS
BEGIN
  BEGIN TRY
      INSERT INTO TripSummary (Date, TripHours)
      VALUES (@TripDate, @RideHrs)
  END TRY
  BEGIN CATCH
      SET @ErrorMsg = 'Error_Num: ' +
      CAST (ERROR_NUMBER() AS varchar) +
      ' Error_Sev: ' + 
      CAST(ERROR_SEVERITY() AS varchar) +  
      ' Error_Msg: ' + ERROR_MESSAGE()
  END CATCH
END

Show me the ERROR...

DECLARE @ErrorMsgOut as nvarchar(max)

EXECUTE dbo.cusp_TripSummaryCreate 
        @TripDate = '1/32/2018',
        @RideHrs = 100,
        @ErrorMsg = @ErrorMsgOUT OUTPUT

SELECT @ErrorMsgOut as ErrorMessag

ErrorMessage
-------------------------------------------------
Error_Num: 241 Error_Sev: 16 
Error_Msg: Conversion failed when converting date and/or time from 
           character string

THROW vs RAISERROR

THROW

Introduced in SQL Server 2012
Simple & easy to use
Statements following will NOT be executed

RAISERROR

Introduced in SQL Server 7.0
Generates new error and cannot access details of original error (e.g. line number where error originally occurred)
Statements following can be executed

Your turn to CATCH!

Writing Functions and Stored Procedures in SQL Server