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

Transact-SQL (2000)

Error Handling

Author

Topic

anandc
Starting Member

20 Posts

Posted - 2006-01-05 : 00:23:20

While doing error handling in Stored Proc, do I need to put error handling code after each UPDATE statement in the procedure using @@ERROR variable.

e.g. Do i need to do like this for every update???


UPDATE tblClearingCharge
SET Calculated_ClrChrg = Calculated_ClrChrg + OrderSystem_Markup,
    Adj_ClrChrg = OrderSystem_Markup
WHERE Order_System = '1' AND ISNULL(ClrChrg_Id, 0) <> -1

IF @@ERROR <> 0 
BEGIN
	SET @strErrorCode = 'PRCC607'
	SET @strErrRemarks = 'usp_CLGCHG_CalcClearingCharge;12;@strValue='+ @strValue +',@strValueType=' + @strValueType
	EXEC usp_COMM_LogError 	@strErrorCode = @strErrorCode, @strRemarks = @strErrRemarks, @chrUpdateCommTranStatusToError = 'N'
	RETURN(1)
END

- Anand

Norwich
Posting Yak Master

158 Posts

Posted - 2006-01-05 : 00:43:53

The @@Error will pick up the error from the latest run Code - Meaning the error after the 'GO'.
You can only have 1 'GO'in a S_proc.

Try using a Transaction instead.

Regards
N

If you want your computer to be faster then throw it out of the window.

anandc
Starting Member

20 Posts

Posted - 2006-01-05 : 00:51:23

quote:
Originally posted by Norwich

The @@Error will pick up the error from the latest run Code - Meaning the error after the 'GO'.
You can only have 1 'GO'in a S_proc.

Try using a Transaction instead.

Regards
N

If you want your computer to be faster then throw it out of the window.

Hi Norwich

I believe you are getting something wrong here, @@ERROR returns the error number for the last Transact-SQL statement executed and not "the error after the 'GO'"

Regards

- Anand

Norwich
Posting Yak Master

158 Posts

Posted - 2006-01-05 : 01:12:22

quote:
Originally posted by Norwich

The @@Error will pick up the error from the latest run Code - Meaning the error after the 'GO'.

Thanks for pointing that out - I meant the error before the 'Go'.
But my point was that a SP will only allow you one 'GO' hence I suggested the Transaction route.

Regards
N

If you want your computer to be faster then throw it out of the window.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-05 : 01:25:01

Read more here
http://www.sommarskog.se/error-handling-I.html

Madhivanan

Failing to plan is Planning to fail

anandc
Starting Member

20 Posts

Posted - 2006-01-05 : 01:39:03

Hi Norwich

Even if I use transactions, how should I check errors in my SP to do ROLLBACK. Do I need to check @@ERROR after each UPDATE statement in my SP ??

Regards

- Anand

Norwich
Posting Yak Master

158 Posts

Posted - 2006-01-05 : 01:46:57

After the each Update include


IF @@error <> 0
Rollback Tran

and at the end of the batch include

Commit Tran

Regards
N

If you want your computer to be faster then throw it out of the window.

Norwich
Posting Yak Master

158 Posts

Posted - 2006-01-05 : 01:52:47

Check out these links, they should fix you up.

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50568[/url]

[url]http://www.sqlteam.com/item.asp?ItemID=2463[/url]

Regards
N

If you want your computer to be faster then throw it out of the window.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources