Combine UPDATE and SELECT in one statement

After we did an UPDATE on a record and when we want to find out what are updated, we will usually follow up with a SELECT. Example,

update tblTest
set
    Age = Age + 1, LastModifiedDate = GETDATE()
where Name = 'Name 1'

select * from tblTest
where Name = 'Name 1'

How about doing the above all in one single statement and avoiding the select statement from getting outdated record when someone intercepts and attempts to update?

It's possible! Try the following.

--SETUP
if ISNULL(object_id('tblTest'),0)<>0 drop table tblTest
go
create table tblTest (
    ID int identity(1,1) not null primary key,
    Name varchar(255) not null,
    Age tinyint not null,
    LastModifiedDate datetime not null default getdate()
)
go

--INSERT test records
insert into tblTest (Name, Age) values ('Name 1', 21)
insert into tblTest (Name, Age) values ('Name 2', 22)
go

--CREATE the temp table for select
if ISNULL(object_id('tempdb..#tem_tblTest'),0)<>0 drop table #tem_tblTest
create table #tem_tblTest (ID int, Name varchar(255), Age tinyint)

--UPDATE record and select into temp table (All in one statement)
update tblTest
set
    Age = Age + 1, LastModifiedDate = GETDATE()
output inserted.ID, inserted.Name, inserted.Age into #tem_tblTest (ID, Name, Age)
where Name = 'Name 1'

--SELECT what's updated
select * from #tem_tblTest
go

--CLEANUP
if ISNULL(object_id('tempdb..#tem_tblTest'),0)<>0 drop table #tem_tblTest