SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Transaction in Stored Proc
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

RoryBecker
Starting Member

United Kingdom
3 Posts

Posted - 09/25/2007 :  06:31:41  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 09/25/2007 :  06:45:10  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/25/2007 :  06:50:58  Show Profile  Reply with Quote
.. 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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000