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