SQL Transaction - use values before commit?

Question

So I am creating a stored procedure in SQL2014. Towards the start it has: Begin Transaction

Then it does an insert into a table, which has an auto-incrementing primary key: Insert into tNotice.

And then latter on in the stored proc, I need to write a query against the tNotice table to use to build up another insert statement into a different table. My question is, will the newly inserted values be selected since this is going to be in a transaction, and the commit won't be until the end of the stored proc?

Answer 1

You can do a quick test to confirm this, I just ran a test out of curiousity, this is on sql server 2012. The current session will have visibility to a record that was previously inserted but not committed, however other sessions will not have visibility to that depending on their transaction isolation level.

Example

create table hs_test(
id int
)

begin transaction

insert into hs_test values (1)

select * from hs_test

--commit

I ran this in one ssms window, with the default isolation level of ReadCommitted. Without committing, the select will still see the value of 1 inserted.

Now if you open up another ssms window, and try to query that table, you won't see any records, because it hasn't been committed yet.

Answer 2

If you are still in the same block of t-sql (in other words, t-sql that appears in that session between the call to start transaction and commit), yes you can see the records that have not yet been committed.

However, if you are not in the same block (meaning in some other session), no they will be invisible until you commit.

I asked in comments about why you are querying the tNotice table after the insert. If it's just so you can get the id used in the insert, don't do that. Check @@IDENTITY right after the insert and get the id used that way.

If you write the insert correctly, you put the values for tNotice's new record in vars (with the exception of the auto-inc id). It would look something like this:

begin transaction
-- code to SELECT or SET values for @varField_1, @varField_2, and @varField_3

-- [key_id] is the auto-inc id in this case so we don't need it as part of the insert.
INSERT INTO [dbo].[tNotice] 
           ([field_1]
           ,[field_2]
           ,[field_3])
     VALUES
           (@varField_1
           ,@varField_2
           ,@varField_3)

SET @varKeyID = @@IDENTITY -- this gives you what id was used in the insert above.

-- MORE CODE
commit

Now there is no reason to go back to tNotice for values you now have in vars and you can use them as you need to later in the sp.