How do you pass identify value to out parameter for an UPDATE?

Question

So far I have something like the following.

However I'm not sure what to do when I perform UPDATE - from another question here I found that you need to store the OUTPUT INSERTED result to a table because the update (or insert) may affect multiple rows? I tried using SCOPE IDENTITY but it return NULL on the UPDATE. Anyway if I use the table - then how do I get an individual integer that I can pass to the out parameter? Or do I have change the out parameter to a different type like a collection?

ALTER PROCEDURE [Data].[UpdateRecord] 

@theValue decimal(4,2) = NULL, 
@updatetime datetimeoffset(7), 
@maxintervaltime datetimeoffset(7),
@recordID int = NULL output

AS

declare @mytable as TABLE
(
    Id int
)

begin tran
if exists (select * from Data.theValue with (updlock,serializable) where Data.theValue.maxintervaltime = @maxintervaltime)
begin
    update Data.theValue set theValue = @theValue, updatetime = @updatetime, maxintervaltime = @maxintervaltime
    where Data.theValue.maxintervaltime = @maxintervaltime
    -- OUTPUT INSERTED.id into @mytable (this line is wrong)
end
else
begin
    insert into Data.theValue(theValue, updatetime, maxintervaltime) values(@theValue, @updatetime, @maxintervaltime);
    SET @recordID = SCOPE_IDENTITY();
end

commit tran

Would a `MERGE` be more suitable in this case? http://stackoverflow.com/questions/23860250/stored-procedure-merge-insert-or-update-and-return-id-issue — Liesel, Apr 01 '16 at 02:18
@LesH I suppose, I think based on some other posts I decided on this approach (update or insert) due to some issues with merge - in any case I'd like to know how to do it without changing to merge. — erotavlas, Apr 01 '16 at 02:53
OK, I'd suggest the MSDN for `OUTPUT` https://msdn.microsoft.com/en-us/library/ms177564.aspxand maybe this http://stackoverflow.com/questions/10999396/how-do-i-use-an-insert-statements-output-clause-to-get-the-identity-value If you still have no luck, I'll write you an example a bit later today. — Liesel, Apr 01 '16 at 03:03
It depends on whether `UPDATE` can affect more than one row. Is `maxintervaltime` unique? If it is unique, then `OUTPUT` table will have only one row and you can `SET @recordID` to the value of the top row from `@mytable`. If `maxintervaltime` is not unique and `UPDATE` can change several rows, then what do you want to return? — Vladimir Baranov, Apr 01 '16 at 04:32
Think about it: How can a single integer value return a set of integer values, the ID's from multiple updated rows. You will have to return a row-set in that instance, and must include some other, natural, key in that row-set in order to properly associate each ID value with its correct row. — Pieter Geerkens, Apr 01 '16 at 07:08

score 1 · Answer 1 · answered Apr 01 '16 at 06:35

1

The output clause should be placed between update and from/where clause. UPDATE can affect multi rows so you have to ensure your logic is correct.

update Data.theValue set theValue = @theValue, updatetime = @updatetime, maxintervaltime = @maxintervaltime
 OUTPUT INSERTED.id into @mytable 
where Data.theValue.maxintervaltime = @maxintervaltime

SET @recordID = top 1 id from @mytable

answered Apr 01 '16 at 06:35

cqi

539
3
13

should your last line be this? SET @recordID = (SELECT top(1) Id from @myTable) – erotavlas Apr 01 '16 at 22:41

How do you pass identify value to out parameter for an UPDATE?

1 Answers1