Downsides to MERGE with dummy USING table?

I'm creating some stored procedures specifically for use from C# to update various tables in our database. A large number of items require a predictable function that will:

1) Check if a matching row already exists 2) If it doesn't exist, insert data 3) Gather ID of row and return to user

I know this can be done in a number of ways, but the most elegant way I can imagine seems to be using a MERGE with a dummy table and using the procedure params for the ON clause, such as:

CREATE PROCEDURE dbo.UpdatePerson(@PersonID INT, @FirstName VARCHAR(50)) AS
    MERGE dbo.Person p
    USING (SELECT 1 One) One
    ON p.Person_ID = @PersonID
    WHEN MATCHED THEN 
        UPDATE SET First_Name = @FirstName
    WHEN NOT MATCHED THEN
        INSERT (Person_ID, First_Name) VALUES (@PersonID, @FirstName);

This wraps it all together in one nice bundle, even though I'm not working with an actual table to merge in. I know the same basic idea could be accomplished with:

...
USING (SELECT @PersonID Person_ID, @FirstName First_Name) NewPerson
ON p.Person_ID = NewPerson.Person_ID
...

and maybe this would offer some kind of performance benefit?

Can anyone offer any solid reasons for/against this kind of usage of MERGE?

Answers


Instead of using MERGE you can use if condition.

You are having a temp table

CREATE TABLE #Table(PersonID INT,First_Name VARCHAR(100))

-- BEFORE THAT INSERT INTO TEMP TABLE 

IF EXISTS(SELECT 1 FROM YOURTABLE WHERE PERSONID IN(SELECT PERSONID FROM #TABLE)) 
BEGIN
    -------YOUR UPDATE QUERY
END 
ELSE
BEGIN
    -------INSERT QUERY
END

DROP TABLE #Table

Need Your Help

remove hidden class from td in same row using jquery or javascript

javascript jquery webforms

I have records in table in webform with drop-down each row and hidden td. I want to show this hidden td if user choose certain value from drop down.

html flow charts/ workflow diagram

javascript html workflow flowchart

I have large set of flow charts and workflow diagrams. I want to draw them and convert them into a webpage, I also need every object in the flow chart to be clickable. I tried Visio and it does sav...