Thursday, March 8, 2007

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
Share:

0 comments:

Post a Comment

Your feedback is very important to me. Please provide your feedback via putting comments.

Search This Blog
Loading...
About Me
My Photo

Full Stack Developer,Blogger,Microsoft Mvp, Mentor, Life long learner, 10+ Years of Experience for JavaScript, web and Microsoft technologies. ASP.NET MVC,Web Forms,C#,AngularJs,Golang
Mvp profile
Subscribe to my blog

Enter your email address:

Follow us on facebook
Blog Archive
Total Pageviews