CODEWITHSUNDEEP
sql-servertransactional
user3676641
user3676641

Reputation: 51

SQL Server : SELECT from table updated during a Transaction

I have a stored procedure that contains the following logic:

  1. Starts transaction scope using BEGIN TRANS A

  2. Calls a second stored procedure where a record in Table1 is updated

  3. Control returns to the original stored procedure where a SELECT is run against Table1

  4. Commit transaction using COMMIT TRANS A

For some reason, that SELECT in step 3 is always returning the data values before the table was updated. I need the updated values from Step 2, that haven't been committed, to be returned by my SELECT.

How would I go about selecting the dirty/uncommitted data from Table1?

Upvotes: 0

Views: 2342

Answers (1)

Sean Lange
Sean Lange

Reputation: 33571

The scenarios described sounds a little off to me. You can begin a transaction and then execute a procedure. The data affected by that procedure is part of the transaction. This is painless to test and demonstrate.

create table TransactionDemo
(
    SomeValue varchar(50)
)
insert TransactionDemo
select 'This is the original data.'

GO

create procedure TransactionDemoUpdate as
    set nocount on;
    update TransactionDemo
    set SomeValue = 'This is updated data.'

GO

begin transaction

select * from TransactionDemo --data prior to calling procedure

exec TransactionDemoUpdate --will update the data

select * from TransactionDemo --see the values have changed

rollback transaction

select * from TransactionDemo --after the rollback they are the original values again.

Upvotes: 4

Related Questions