begin try catch - 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)

begin try catch

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-08-24 : 09:38:19

Hi,
The sql below shows the format of the sql I am using in a stored procedure (SP).
Can you please let me know where to place the begin catch blocks?
Thanks

create procedure sp

as

create table #tblTemp

insert into #tblTemp some values...

...

BEGIN TRANSACTION

WHILE (@presentRow <= @MaxRows)
BEGIN

...
select @param1, @param2, ...

update tbl1 where field1 = @param1, ...

SELECT @Err = @@ERROR
IF @Err != 0
BREAK

update tbl2...

SELECT @Err = @@ERROR
IF @Err != 0
BREAK

SET @CurrentRow = @CurrentRow + 1

END

IF @Err = 0
BEGIN
update tableMain

SELECT @Err = @@ERROR
END

drop table #tbltemp

IF @Err != 0
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-24 : 10:04:00

can i ask what you're trying to achieve here?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-08-24 : 10:09:21

making sure where to place the try catch blocks.
It seems to me to place the try catch around all the statements and at the end of the sqls, to place the catch block which has rollback transaction.
Am I on the right track?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-24 : 10:12:17

yup. you're. why not try it out?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources