Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a stored procedure that runs a few commands. I don't want these commands to be wrapped in the transaction of the stored procedure. If the 4th command fails, I want the 1st, 2nd and 3rd ones to stay and not rollback.

Is it possible to write the stored procedure in such a way that it doesn't all execute as one big transaction?

share|improve this question
4  
OK, so what's your question? –  JNK Jul 12 '13 at 16:08
    
Updated to clarify slightly –  Matthew Steeples Jul 12 '13 at 16:30
3  
Stored procedures don't automatically start transactions. Also, you can't control whether or not a caller has started a transaction. –  Jon Seigel Jul 12 '13 at 16:37

6 Answers 6

All of the transactions won't execute in a single transaction. Take a look at this example:

use TestDB;
go

if exists (select 1 from sys.tables where object_id = object_id('dbo.TestTranTable1'))
    drop table dbo.TestTranTable1;
create table dbo.TestTranTable1
(
    id int identity(1, 1) not null,
    some_int int not null
        default 1
);
go

insert into dbo.TestTranTable1
default values;
go 4

select *
from dbo.TestTranTable1;

if exists (select 1 from sys.sql_modules where object_id = object_id('dbo.ChangeValues'))
begin
    drop proc dbo.ChangeValues;
end
go
create proc dbo.ChangeValues
as
    update dbo.TestTranTable1
    set some_int = 11
    where id = 1;

    update dbo.TestTranTable1
    set some_int = 12
    where id = 2;

    update dbo.TestTranTable1
    set some_int = 13
    where id = 3;

    -- this will error out (arithmetic overflow)
    update dbo.TestTranTable1
    set some_int = 2147483648
    where id = 4;
go

exec dbo.ChangeValues;

select *
from dbo.TestTranTable1;

Here is the output:

enter image description here

By creating an Extended Events session to monitor the sql_transaction event, here is the output from executing dbo.ChangeValues:

enter image description here

As you can see in this above screenshot, there are separate transactions for each of the four statements. The first 3 commit, and the last one rolls back because of the error.

share|improve this answer
2  
+1 @ThomasStringer for using Extended Events. Such powerful tool to see under the hood ! –  Kin Jul 12 '13 at 17:06

I think there may be some confusion here about a batch vs a transaction.

A transaction is a statement or set of statements that will either succeed or fail as a unit. All DDL statements are in transactions themselves (i.e. if you update 100 rows but row 98 throws an error, none of the rows are updated). You can wrap a series of statements in a transaction as well using BEGIN TRANSACTION and then either COMMIT or ROLLBACK.

A batch is a series of statements that are executed together. A stored procedure is an example of a batch. In a stored procedure, if one statement fails and there is error trapping (normally TRY/CATCH blocks) then the subsequent statements will not execute.

I suspect your issue is the batch is getting cancelled when an error occurs because either the stored proc itself or an outer scope (like the application or stored proc that calls this procedure) has error trapping in it. If that is the case this is trickier to resolve since you need to adjust how you handle errors at whatever scope is trapping them.

share|improve this answer

Everything in sql server is contained in a transaction.

When you explicitly specify begin tran and end tran then it is called Explicit Transaction. When you dont, then it is Implicit transaction.

To switch which mode you're in, you'd use

set implicit_transaction on

or

set implicit_transaction off

select @@OPTIONS & 2

if above returns 2, you're in implicit transaction mode. If it returns 0, you're in autocommit.

A transaction is ALL or nothing to keep database in a consistent state .. remember ACID properties.

CREATE TABLE [dbo].[Products](
    [ProductID] [int] NOT NULL,
    [ProductName] [varchar](25) NULL,
    [DatabaseName] [sysname] NOT NULL,
 CONSTRAINT [pk_Product_ID_ServerName] PRIMARY KEY CLUSTERED 
(
    [ProductID] ASC,
    [DatabaseName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


-- insert some data 
INSERT INTO [dbo].[Products]([ProductID], [ProductName], [DatabaseName])
SELECT 1, N'repl1_product1', N'repl1' UNION ALL
SELECT 1, N'repl2_product1_02', N'repl2' UNION ALL
SELECT 1, N'repl3_product1_03', N'repl3' UNION ALL
SELECT 2, N'repl1_product1_01', N'repl1' UNION ALL
SELECT 2, N'repl2_product1', N'repl2' UNION ALL
SELECT 2, N'repl3_product1_03', N'repl3' UNION ALL
SELECT 3, N'repl1_product1_01', N'repl1' UNION ALL
SELECT 3, N'repl2_product1_02', N'repl2' UNION ALL
SELECT 3, N'repl3_product1', N'repl3' UNION ALL
SELECT 4, N'repl1_product1_01', N'repl1' UNION ALL
SELECT 4, N'repl2_product1_02', N'repl2' UNION ALL
SELECT 5, N'repl1_product1_01', N'repl1' UNION ALL
SELECT 5, N'repl2_product1_02', N'repl2'

-- create SP now -- note that the first 3 will succeed and 4th will fail due to string truncation...

IF OBJECT_ID ('usp_UpdateProducts', 'P') IS NOT NULL
    DROP PROCEDURE usp_UpdateProducts;
GO
create procedure usp_UpdateProducts
as 
begin try
update Products 
set ProductName = 'repl1_product1'
where DatabaseName = 'repl1'and ProductID = 1;
update Products
set ProductName = 'repl2_product1'
where DatabaseName = 'repl2' and ProductID = 2;
update Products
set ProductName = 'repl3_product1'
where DatabaseName = 'repl3' and ProductID = 3;
update Products
set ProductName = 'repl3_product1_03&&&&&&&&&&39399338492w9924389234923482' -- this will fail ...
where DatabaseName = 'repl3' and ProductID = 4;
SELECT 1/0;
end try
begin catch
SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage;
end catch
go
share|improve this answer

You will need individual transactions for each command. You can also accomplish this with saved transactions.

http://msdn.microsoft.com/en-us/library/ms188378.aspx

share|improve this answer
2  
I see there is a down vote on this and wanted to qualify that individual transactions it the default behavior for stored procedures, because all statement are wrapped in implicit transactions; however, no one should rely on implicit transactions to control the destiny of their code. It is a much better practice to explicitly control the way transactions are handled in production code. –  Adam Haines Jul 12 '13 at 17:22

This is how stored procedures work by default. The stored procedure isn't wrapped within a transaction automatically.

If you want the stored procedure to stop when it hits the first error you'll want to put some TRY/CATCH login in there to return in the event of a problem with command 2 for example.

share|improve this answer

separate each of the parts with a BEGIN TRAN and to a check if the transaction was successful. if it was commit it, otherwise do a rollback, since they are all executing from the same level you will be able to commit each section separately without having to rollback all if one fails.

For more info you can take a look at: http://msdn.microsoft.com/en-us/library/ms188929.aspx

share|improve this answer
    
Will that create sub transactions within my Stored Procedure? Ideally I'd like to avoid that if possible –  Matthew Steeples Jul 12 '13 at 16:28
1  
If the SP is called from within a transaction, then saved transactions above are the answer. If the sp isn't called with, then @mrdenny is correct. Sql server doesn't support nested transaction. –  StrayCatDBA Jul 12 '13 at 16:40
    
@StrayCatDBA just to clarify.. there are Nested Transactions in SQL Server, but they are evil .. SQL Server allows you to start transactions inside other transactions – called nested transactions. Refer to sqlskills.com/blogs/paul/… , msdn.microsoft.com/en-us/library/ms189336(v=sql.105).aspx and sqlblog.com/blogs/kalen_delaney/archive/2007/08/13/… –  Kin Jul 12 '13 at 17:02
1  
To be clear (and for the lazy who don't want to click the links), you aren't actually starting another transaction. Aka the title on Paul's post: "Myth: Nested Transactions are real." They aren't real transactions. COMMIT in a nested transaction does nothing except decrement @@TRANCOUNT. It's true that you don't get an error if you nest BEGIN TRAN/COMMIT, but that is different from having real nested transctions. –  StrayCatDBA Jul 12 '13 at 17:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.