Transaction in Stored Proc - 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)

Transaction in Stored Proc

Author

Topic

RoryBecker
Starting Member

3 Posts

Posted - 2007-09-25 : 06:31:41

I have the following logic in my stored proc...
-------------------------------------------------------------
ALTER PROC sp_ExecuteAndLog @SQL varchar(8000) AS
Begin Transaction
Execute(@SQL)
Insert Into Log (lgSQL) Values (@SQL)
Commit Transaction
GO
-------------------------------------------------------------
...I then Execute this stored proc thus...
------------------------------------------------------------
Declare @Statement varchar(8000)
SET @Statement = 'bibble bibble bibble'
Exec sp_ExecuteAndLog @Statement
GO
-------------------------------------------------------------

When I execute this it works....

I expected that "bibble bibble bibble" would not be an authentic SQL Statement and would fail. this does indeed fail but does not cause the transaction to fail and therefore "bibble bibble bibble" ends up being placed in my log.

This Log is reapplied to another db for other reasons and this naturally fails.

Can someone explain how I can get this to fail correctly and rollback the transaction?

Thanks in advance.

--
Rory Becker

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-09-25 : 06:45:10

This happens because BEGIN TRANSACTION statement written outside EXECUTE does not apply to it. EXECUTE creates its own transaction space. Also you need to rollback transaction by checking @@error value inside EXEC.

SET @Statement = 'begin tran bibble bibble bibble if @@error<>0 rollback tran'
Execute(@SQL)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Kristen
Test

22859 Posts

Posted - 2007-09-25 : 06:50:58

.. or by transferring the Error State from the inner dynamic SQL to the outer - e.g. using sp_ExecuteSQL instead of EXEC with an OUTPUT parameter - so that the Outer code can do the ROLLBACK if it so wishes.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources