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

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开发者_StackOverflow 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)

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)

You could output the value to a variable and make that variable an output variable. This of course will only work if there is one and only one value for that varaiable at a time but it sounds like this is your case.

On the other hand, you can output to a table varaible and then select the records in the table variable as part of your proc.

Why are you doing this is loop? It might be much faster to process all records at one time with a table variable as input variable (if you have them) to the sp and use set-based instead of record-by-record (otherwise known as row-by-agonizing-row) processing.

You can shunt the OUTPUT into a table variable, then assign the scalar output parameter from this.

You can't assign OUTPUT rows to a scalar variable directly.