1

This question is related to this one : Executing a stored procedure inside BEGIN/END TRANSACTION

One of the answers to the question states that everything inside a transaction can be rolled back, so I'm wondering why a value updated by a simple stored procedure call is still update when I rollback the transaction.

Here is a simple example :

BEGIN TRANSACTION
    DECLARE @id varchar(30) = 'BCPEDEV/'
    DECLARE @lvalue float
    EXEC sp_update_num @id, @outValue OUTPUT
    PRINT 'outValue = ' + CONVERT(varchar, @outValue) -- Prints updated value       
ROLLBACK -- Value stays updated after rollback

Then I check the value whith :

SELECT NUM FROM MyTable WHERE Id = @id

And here is the procedure itself :

ALTER PROCEDURE [dbo].[sp_update_num] @id varchar (21) AS
    BEGIN   
        DECLARE @UpdatedValues table (NUM float NULL);
        DECLARE @CLE_ID varchar(15) 
                UPDATE TOP(1) [dbo].[MyTable]
                    SET NUM = NUM + 1
                    OUTPUT inserted.NUM INTO @UpdatedValues 
                    WHERE ID = @id AND ETAT = 0     
            SELECT NUM FROM @UpdatedValues  
    END

The procedure updates one single value in a table by incrementing it, and there is no transaction inside the procedure.

I was trying to do this in ADO.Net but then I realized I couldn't do it in plain SQL !

Some help would me much appreciated :)

BlackMushroom
  • 83
  • 2
  • 12
  • What does `sp_update_num` do then? – DavidG Feb 14 '18 at 10:30
  • sp_update_num does the following : update mytable set myValue = myValue+1 where id=@id. When I rollback my value is still updated, and I expect it not to be. – BlackMushroom Feb 14 '18 at 10:33
  • 1
    Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! – marc_s Feb 14 '18 at 10:35
  • How are you checking that the value isn't getting rolled back? The code you have shown will absolutely rollback the value. – DavidG Feb 14 '18 at 10:36
  • If you are checking the @outValue variable value, that value will not change after the rollback. The underlying data in the table will be rolled back, though. Post a complete example including the sp code and the method you use to check the value. – Dan Guzman Feb 14 '18 at 10:40
  • Mine works perfectly. Its rolling back – SqlKindaGuy Feb 14 '18 at 10:43
  • @marc_s : Thanks for the tip :) – BlackMushroom Feb 14 '18 at 10:53

1 Answers1

0

Based on that we dont know how you SP looks like, ive tried to write one my self, and this is rolling backfine:

Table and Test data

CREATE TABLE [dbo].[Dest1](
    [FirstName] [nvarchar](50) NULL,
    [LastName] [nvarchar](50) NULL,
    [values] [float] NULL
) ON [PRIMARY]

GO


insert into dbo.Dest1(FirstName,LastName,[values])
values
('Thomas','Jensen',1),
('Hans','Larsen',1)

SP

ALTER PROCEDURE [dbo].[SP_NAME_update]
    -- Add the parameters for the stored procedure here

   @id varchar(30),

    @outValue float OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;



   update [LegOgSpass].[dbo].[Dest1] set [values] = [values]+1 where FirstName=@id

   select @outValue = @@ROWCOUNT

END

SQL Code

BEGIN TRANSACTION
    DECLARE @id varchar(30) = 'thomas'
 DECLARE @outValue float

    EXEC [dbo].[SP_NAME_update] @id,@outValue OUTPUT
    PRINT 'outValue = ' + CONVERT(varchar, @outValue) -- Prints updated value       
ROLLBACK -- Value stays updated after rollback

When i uncomment my begin and rollback my values increment, and when i dont it stays as the same.

SqlKindaGuy
  • 3,501
  • 2
  • 12
  • 29