September 19, 2010 at 3:57 am
Hi,
1) Anybody knows whether sql server has structured way of error handling. If stored procedure so big & having multiple insert/update/delete then checking every time @@error = 0 is not helpful. Anybody please suggest the other ways of error handling.
2) Please suggest if stored procedure has errors then which value returning is best practice in below i used return 0
e.g
Create Proc...
..
..
..
UPDATE TrascationTable
SET n_Flag = 0
WHERE n_Flag = 40
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN 0
END
UPDATE MasterTable
SET n_Amount = @val+ (n_Discount*100/n_ValueBased)
WHERE n_TransCode = @n_Code
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN 0
END
September 19, 2010 at 4:33 am
SQL Server 2005 or later supports try catch block. Before SQL Server 2005 the only way to have some kind of error handling was to check the value of @@error. One of the problems with this approach was that the value of @@error got reset after each statement, so if you had an error and you didn’t catch it right after the statement that caused it, you couldn’t catch it. Of course there were other problems with this approach. If you work with SQL Server 2005 or higher version, then you can modify the code to work with the try-catch block.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 19, 2010 at 5:48 pm
To add to what Adi has already stated, in SQL Server 2000, SET XACT_ABORT ON became one of my very best friends as a not-so-good attempt at Try/Catch. As the 5th Element had a habit of saying... "ba-da-BOOM!"
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply