Your very own TRY..CATCH
Alter dbo.cuspRideSummaryDelete
to include an intentional error so we can see how the TRY
CATCH
block works.
This exercise is part of the course
Writing Functions and Stored Procedures in SQL Server
Exercise instructions
- Incorrectly assign
@DateParm
anvarchar(30)
data type instead of adate
. - Include
@Error
as an optionalOUTPUT
parameter. - Include the
DELETE
statement within theBEGIN TRY...END TRY
block. - Concatenate the
ERROR_NUMBER()
,ERROR_SEVERITY()
,ERROR_STATE()
,ERROR_MESSAGE()
,ERROR_LINE()
within theBEGIN CATCH...END CATCH
block andSET
to@Error
.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Alter the stored procedure
CREATE OR ALTER PROCEDURE dbo.cuspRideSummaryDelete
-- (Incorrectly) specify @DateParm
___ ___(30),
-- Specify @Error
___ nvarchar(max) = NULL OUTPUT
AS
SET NOCOUNT ON
BEGIN
-- Start of the TRY block
___ ___
-- Delete
DELETE FROM RideSummary
WHERE Date = @DateParm
-- End of the TRY block
___ ___
-- Start of the CATCH block
___ ___
SET @Error =
'Error_Number: '+ CAST(___() AS VARCHAR) +
'Error_Severity: '+ CAST(___() AS VARCHAR) +
'Error_State: ' + CAST(___() AS VARCHAR) +
'Error_Message: ' + ___() +
'Error_Line: ' + CAST(___() AS VARCHAR)
-- End of the CATCH block
___ ___
END;