TRY-CATCH Block-SQL Server

In SQL server 2000, to handle sql server exception we have to use goto statement while in the sql server 2005 support the try-catch type of exception handling just like vb.net or c#.

here is exmple of it.


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE PROCEDURE [dbo].[sp_InsertState]
@StateId int,
@Name varchar(30)
AS
BEGIN
declare @Error Int
declare @TableName sysname
declare @ErrorMessage nvarchar(2000)
BEGIN TRY
BEGIN TRANSACTION
SET NOCOUNT ON;
INSERT INTO tbl_State_Master
(stateid, name, isactive)
VALUES (@StateId,@Name,1)
SET NOCOUNT OFF;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
set @ErrorMessage=ERROR_MESSAGE()
RAISERROR('State can not be inserted',16,1)
END CATCH
END