Q:

SQL Server : SELECT from table updated during a Transaction

I have a stored procedure that contains the following logic:

Starts transaction scope using BEGIN TRANS A
Calls a second stored procedure where a record in Table1 is updated
Control returns to the original stored procedure where a SELECT is run against Table1
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?

A:

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.

