Retrieving Updates Made Within A Transaction?

Hi All,

This seems like a tricky question to me. I have a Stored Procedure
that encapsulates a number of updates to various tables within a
transaction.

However, at a later part of the transaction I need to be able to
select records changed by an update statement made earlier within the
same stored proc (and within the same transaction) and need for that
select to reflect the changed values.

My understanding, however, is that the records aren't actually changed
by the update statement until the transaction is committed, and
therefore my later select statement won't return the expected records
because the update is being held until the transaction is committed.

Is this accurate? And, if so, is there a reasonable workaround that
still leaves me able to rollback the entire transaction if I strike a
problem somewhere along the way?

So, a pseudo code example would be:


BEGIN TRANSACTION

UPDATE mytable SET myid = @yourid WHERE myid = @id

SELECT * FROM mytable where myid = @id

COMMIT TRANSACTION

In this above example, would the select statement return the records
that have a myid value of @id as before the update as after the
update?

Many, many thanks in advance!

Much warmth,

Murray