sp error handling not working - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sp error handling not working

Author

Topic

desikankannan
Posting Yak Master

152 Posts

Posted - 2010-11-11 : 00:22:29

hi,
below the stored procedure, iam sending, its not firing.
pls guide me how to write error handling.

USE [smartsystem]
GO
/****** Object: StoredProcedure [dbo].[procstdguagemaster] Script Date: 11/11/2010 10:46:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[procstdguagemaster]
(
-- insert record for guagemaster createion date
@fldguagekey bigint,
@fldguagecode bigint,
@fldguagename varchar(200),
@fldguageidnum varchar(30),
@fldguagewrlimit numeric(18,4),
@fldimsizego numeric(18,4),
@fldimsizenogo numeric(18,4),
@fldscrapflag varchar(1),
@companyid varchar(50),
@userid varchar(100),
@creationdate datetime,
@modifiedby varchar(100),
@modifydate datetime,
@status varchar(50),
@cancel varchar(50),
@reasonforcancel varchar(50),
@scrapflag char(1)
)
as
set nocount on

begin transaction

insert into mst_stdgaugemaster(fldguagekey,fldguagecode,fldguagename,fldguageidnum,fldguagewrlimit,fldimsizego,fldimsizenogo,fldscrapflag,companyid,userid,creationdate,modifiedby,modifydate,status,cancel,resonforcancel,scrapflag)
values
(@fldguagekey,@fldguagecode,@fldguagename,@fldguageidnum,@fldguagewrlimit,@fldimsizego,@fldimsizenogo,@fldscrapflag,@companyid,@userid,@creationdate,@modifiedby,@modifydate,@status,@cancel,@reasonforcancel,@scrapflag)
if @@error <> 0 goto ERR_HANDLER
commit transaction
return 0
--Exception Handing
ERR_HANDLER:
print 'unexpected error occurred!'
Rollback transaction
return 1

Desikankannan

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-11 : 01:19:31

you are saying return 0 or return 1. Base on this are you saying that error is not handled ?

Since you are using 2005, why don't you use Try/Catch statement.
You can a look in BOL for Try/Catch statement.

Begin Try
End Try
Begin Catch
End Catch

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources