Error Trapping in Stored Procedures

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 2000 Forums

SQL Server Development (2000)

Error Trapping in Stored Procedures

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-12 : 08:35:11

Bala writes "Hi,

I am sending you the script of a stored procedure, where i want to set the contorl to a specified line (Label1) when a error raised while executing the same.

*****************************************************************

CREATE Procedure ProfirstSecond 
	@Tuserid int, -- User id Input
	@Tretval varchar(100) output -- Output parameter
as 
Declare @Tjobid   integer, @Error Integer

set Transaction Isolation Level Serializable
Begin transaction

Label1:

select Top 1 @TjobiD=U.Jobid 
from upload u with (Rowlock, HoldLock) where u.ZipName <> '' and Userid = @Tuserid order by priority

if len(Rtrim(Ltrim(@Tjobid)))>0
Begin 
	update Upload set statusid = 1111 where  Jobid = @Tjobid
	Set @Error = @@Error
	If @@Error <> 0
	Begin
		Goto Label1
	End
	set Transaction Isolation Level Read Uncommitted 

	select @Tretval = Convert(Varchar,@Tjobid)
End
Else
	select @Tretval =''
Commit Transaction

*****************************************************************

We do not want to allow another user to select the same row, which was selected by a user for Updation. We had tried to lock the row by Executing this SP. It is locking, but the error is getting rased then and there while updating. But it is not trapping the error which i displayed as follows.

****************************************************************
Server: Msg 1205, Level 13, State 1, Procedure ProfirstSecond, Line 18
Transaction (Process ID 55) was deadlocked on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
*****************************************************************

Thanks in Advance.

Regards,
Bala."

ToddV
Posting Yak Master

218 Posts

Posted - 2001-12-12 : 08:42:31

@@Error applies only to the last statement. In this case as in your proc you are evaluating whether the variable is set rather than the outcome of your update.

Set @Error = @@Error
~~If @@Error <> 0~~
If @Error <> 0

izaltsman
A custom title

1139 Posts

Posted - 2001-12-12 : 08:57:20

I don't think you will be able to trap deadlock errors (they are considered fatal so execution terminates before it gets to your error trap). You should be able to prevent this deadlock though if you take update locks instead of shared:


...
select Top 1 @TjobiD=U.Jobid 
from upload u with (UPDLOCK) where u.ZipName <> '' and Userid = @Tuserid order by priority
...

Depending on your requirements you might want to throw in a READPAST hint in there as well to improve concurrency.

Edited by - izaltsman on 12/12/2001 08:58:54

TonyH
Starting Member

29 Posts

Posted - 2001-12-12 : 09:17:49

Hi,

I think you may be over complicating things here. Using holdlock within the transaction is equivalent to setting the Transaction Isolation Level to Serializable, so you don't need both. I would try suggest removing the setting of the isolation levels altogether and keeping the Holdlock on the select within the transaction.

If this proc is the only place where this update of the status id to 111 can take place as a means of identifying the row as 'in use' then do you need a 'and statusid <>1111 ' added to the where clause as well to omit these rows?

TonyH
www.SQLCoder.com - Code generation for SQL Server 7/2000

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources