asked
Question about transaction in stored procedure
I have created following procedure. By this procedure i want to exceute two statements. If both statement executed successfully then it should return blank (@strReturn='') string. If both statement fails then it should return '1;2'. If first fails then '1' or second fails then '2'. Both statement should be executed whether error encountered or not. But when i execute this procedure following error occurs: Server: Msg 208, Level 16, State 1, Procedure TESTING, Line 21 Invalid object name 'TBL_CAR'. Server: Msg 266, Level 16, State 1, Procedure TESTING, Line 49 Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1. Please anyone suggest me that what is wrong with this procedure. /*Procedure code start */ SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO /* NAME : TESTING AUTHOR : Subhash DATE : 24-01-2011 PARAMETER : DESCRIPTION : This Procedure will do following: */ CREATE PROCEDURE TESTING @strReturn VARCHAR(20) = '' OUTPUT AS BEGIN SET @strReturn = '' -- Delete extra records in TBL_CHALLAN_PACKAGING -- This case appears when TBL_CHALLAN_PACKAGING contains more than -- one records for on challan. BEGIN TRANSACTION --This table doesn't exist. i want to encounter error. DELETE FROM TBL_CAR IF @@ERROR = 0 BEGIN IF @@TRANCOUNT > 0 COMMIT TRANSACTION END ELSE BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION SET @strReturn = '1' END -- Update records whose dat_leave is marked but 'YSN_DELETED_FROM_DBMS' is not set BEGIN TRANSACTION --This table doesn't exist. i want to encounter error. DELETE FROM TBL_bus IF @@ERROR = 0 BEGIN IF @@TRANCOUNT > 0 COMMIT TRANSACTION END ELSE BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION SET @strReturn = CASE WHEN LEN(@strReturn) > 0 THEN @strReturn + ';2' ELSE '2' END END ALTER TABLE TBL_DB_USER ENABLE TRIGGER ALL RETURN END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO GRANT EXECUTE ON TESTING TO PUBLIC --Now execute EXEC TESTING
I think the error message is quite obvious. You are trying to delete from the table `TBL_CAR` which cannot be found on the system. The second error comes about due to the missing error handling in your sproc. You started an explicit transaction (`BEGIN TRANSACTION`), but the error threw you out of the normal control flow, so you couldn't commit or rollback the transaction. You do not mention what version of SQL Server you are using, but if you are on 2005 and above, take a look at using [TRY..CATCH][1] (2008R2 link, but valid for 2005). You can then wrap the entire thing inside the error flow control mechanism of try..catch and be able to catch errors and deal with them properly. EDIT - Addition to address the comments --- I suggest you take a look at the *very* detailed articles from Erland Sommarskog - [Error Handling in SQL Server 2000 - a Background][1] - [Implementing Error Handling with Stored Procedures][2] These articles are the best you can find IMO. Everybody should read them and understand how it all works. Also worth reading (even if you are not on 2005): [Error Handling in SQL 2005 and Later][3] [1]:
http://www.sommarskog.se/error-handling-I.html [2]:
http://www.sommarskog.se/error-handling-II.html [3]:
http://www.sommarskog.se/error_handling_2005.html [1]:
http://msdn.microsoft.com/en-us/library/ms175976.aspx
Thanks dear... williamD for answering. I want to make it clear that i am expecting error from this statement that's why i used the table which doesn't exist. I am using SQL Server 2000. so i cant use TRY...CATCH How can i handle this situation. Because i want to move forward if any statement gives error. Thanks..
I think the site may need renaming soon.
WilliamD.sqlservercentral.com seems good to me... +1 :)
