CODEWITHSUNDEEP
sqlsql-server-2008sql-server-2012
alwaysVBNET
alwaysVBNET

Reputation: 3320

Return one OUTPUT result from MERGE query

I have a merge query which updates or inserts if no records found. In my results, I have a problem when the record does not exist and it get inserted. The query returns BOTH 'Updated' (as empty) and 'Inserted' (with the proper value).

Any ideas how to avoid return the empty 'Updated' when there are no updates?

ALTER PROCEDURE [dbo].[spInsOrUpdApplicant]
    -- Add the parameters for the stored procedure here
    @Name nvarchar(50),
    @Surname nvarchar(50),
    @Position nvarchar(50),
    @NationalID int,
    @ApplicantID int

AS
BEGIN
    SET NOCOUNT ON;
-- Update the row if it exists.    
    UPDATE tbApplicant
    SET Name = @Name, Surname = @Surname, Position = @Position, NationalID = @NationalID

        OUTPUT INSERTED.ApplicantID AS 'Result'

    WHERE ApplicantID = @ApplicantID;

-- Insert the row if the UPDATE statement failed.   
    IF (@@ROWCOUNT = 0 )
    BEGIN
        INSERT INTO tbApplicant (Name, Surname, Position, NationalID)
        OUTPUT INSERTED.ApplicantID AS 'Result'
        VALUES (@Name, @Surname, @Position, @NationalID)
    END

END;

Upvotes: 0

Views: 125

Answers (2)

Brent
Brent

Reputation: 598

I do something very similar.

Inside my stored procedure, I have this:

 DECLARE @mergeResults TABLE (mergeAction varchar(10), tableName varchar(50));
 OUTPUT $action, 'Table Name' INTO @mergeResults;

Can you insert into the table variable and then decide how you want to move the data based on what you see?

You mentioned you have a merge query but you aren't using a merge - you're using more of an "upsert".

Merge T-SQL: http://msdn.microsoft.com/en-us/library/bb510625.aspx

Upvotes: 0

Pieter
Pieter

Reputation: 420

It seems that the 'output' always fires even if no actual rows were updated. You can see the same behavior in triggers. You might want to consider doing the following: 

ALTER PROCEDURE [dbo].[spInsOrUpdApplicant]
    -- Add the parameters for the stored procedure here
    @Name nvarchar(50),
    @Surname nvarchar(50),
    @Position nvarchar(50),
    @NationalID int,
    @ApplicantID int

AS
BEGIN
    SET NOCOUNT ON;
-- Check if the row exists.    
    IF EXISTS (SELECT 1 FROM tbApplicant WHERE ApplicantID = @ApplicantID) BEGIN
-- update if the row exists.    
        UPDATE tbApplicant
        SET Name = @Name, Surname = @Surname, Position = @Position, NationalID = @NationalID
        OUTPUT INSERTED.ApplicantID AS 'Result'
        WHERE ApplicantID = @ApplicantID;
    END
    ELSE BEGIN
-- Else Insert.   
        INSERT INTO tbApplicant (Name, Surname, Position, NationalID)
        OUTPUT INSERTED.ApplicantID AS 'Result'
        VALUES (@Name, @Surname, @Position, @NationalID)
    END

END;

Upvotes: 2

Related Questions