This is quite a handy feature in SQL Server that has been introduced recently.
In your stored procedures or functions,you can now write a try catch block and catch errors as we do in the more proper programming lanugages.
USE [SAMPLEDB]
GO
- StoredProcedure [dbo].[GetStudents]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.GetStudents
AS
SET NOCOUNT ON;
--Error variables
DECLARE @errMessage VARCHAR(2000)
@errNum INT,
@errSeverity INT,
@errState INT,
@errProcedure NVARCHAR(500),
@errLine INT
BEGIN TRY
-- Do some processing here...
Select * from Students
-- Open Cursor etc
END TRY
BEGIN CATCH
--capture error information into variables
SELECT @errNumber = ERROR_NUMBER(),
@errSeverity = ERROR_SEVERITY(),
@errState = ERROR_STATE(),
@errProcedure = ERROR_PROCEDURE(),
@errLine = ERROR_LINE()
--raise an error now
RAISERROR (@errMessage, @errSeverity, 1, @errNumber, @errSeverity, @errState, @errProcedure, @errLine) WITH NOWAIT;
--rollback any open transaction
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
GOTO OutOfCatch
END CATCH
OutOfCatch:
- Finishing code here
