Is it possible to use $action of the MERGE statement in output variable of stored procedure?

Question

I have a stored procedure that either updates existing or inserts single record (I use MERGE TOP(1) ..) using MERGE statement in SQL Server 2008 R2. I use MERGE's OUTPUT clause to emit the value of $action to see what action was taken (INSERT, UPDATE, DELETE, 'blank').

However, when I call my SP from C# program, I am forced to execute a reader (DbCommand.ExecuteReader()) and loop through it once to get the $action value...

My question is, can the OUTPUT $action somehow be attached to SP output parameter which will enable me to do just DbCmmand.ExecuteNonQuery() and examine the output parameter? This will save me from having to instantiate the reader...

BTW, I am doing this call in a loop, so not having to instantiate the reader would be a bit faster (I hope)

Answer 1

Sample table for discussion

create table t1 (id int identity primary key, other int);
insert t1 select 2;

This TSQL does a final select at the end, which will end up as the result for ExecuteNonQuery

set nocount on
declare @tmp table (action sysname)
;
with new(id,other) as (select 1,4)
merge top(1)
into t1
using new
on t1.id = new.id
when matched then update set other = new.id
when not matched then insert values (other)
output $action
into @tmp
;
set nocount off
select action from @tmp

This works well for TOP(1) since it produces only one row in @tmp, but the example below shows what happens when there are multiple records, continuing from the above

set nocount on
declare @tmp table (action sysname)
;
merge
into t1
using (values(1,4),(2,5)) new(id,other)
on t1.id = new.id
when matched then update set other = new.id
when not matched then insert values (other)
output $action
into @tmp
;
set nocount off
select action from @tmp

Output:

action
======
UPDATE
INSERT

• Does additional temp table variable and SELECT statement to SP have much impact on performance?

This would be negligible, you can ignore it.

• Would I have to "wrap" the 2 in a transaction (my app is multi threaded)?

No. The insert into the @tmp is atomic, and the select is coming from a temp table that is specific to the session (nothing can interfere with it)