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

Comments

Popular posts from this blog

How to create Rest API(Web API) with ASP.NET Core 1.0

Creating PDF with ASP.Net MVC and RazorPDF

Solution: gulp is not recognized as internal or external command- Visual Studio