Home Rollback procedure call
Reply: 1

Rollback procedure call

BlackMushroom
1#
BlackMushroom Published in 2018-02-14 10:27:04Z

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 :)

plaidDK
2#
plaidDK Reply to 2018-02-14 11:41:04Z

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.334749 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO