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

Question

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

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

My question is: can the OUTPUT $ action somehow attach to the SP output parameter, which will allow me to do only DbCmmand.ExecuteNonQuery () and check the output parameter? This will save me from having to instantiate the reader ...

By the way, I make this call in a loop, so no need to instantiate the reader will be a little faster (hopefully)

Answer 1

Sample discussion table

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

This TSQL makes the final choice at the end, which will end up being 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 only creates one line in @tmp, but the example below shows what happens when there are several entries 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 the additional temp table variable and SELECT statement in SP affect performance?

That would be negligible, you can ignore it.

• Should I “wrap” 2 in a transaction (my application is multithreaded)?

No. The insert in @tmp is atomic, and the selection comes from the temp table, which is session specific (nothing can interfere with it)

Answer 2

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

Alternatively, you can output to a varaible table and then select the entries in the table variable as part of your proc.

Why are you doing this loop? It may be much faster to process all records at a time from the table variable as an input variable (if any) in sp and use set-based instead of record by record (otherwise called row-by-agonizing-row).

Answer 3

You can disable OUTPUT in a table variable, and then assign a scalar output parameter to this.

You cannot directly assign OUTPUT strings to a scalar variable.