h1aLPcjW

1/*
2Same SP is used to INSERT as well as UPDATE a table.
3Here we are avoid unnecessary checking of whether the record exists or not.
4Instead try to Update directly. If there is no record then @@RowCount would be 0.
5Based on that Insert it as a new record.
6*/
7CREATE PROCEDURE uspUPSert_Ver2
8(
9    @empID INT,
10    @fname VARCHAR(25),
11    @lname VARCHAR(25),
12    @emailid VARCHAR(50)
13)
14AS
15BEGIN
16    SET NOCOUNT ON
17    BEGIN TRAN
18        UPDATE tblUpsert WITH (SERIALIZABLE)
19            SET emailid = @emailid ,
20                firstname = @fname ,
21                lastname = @lname
22        WHERE EmpID = @empID
23
24        IF @@ROWCOUNT = 0
25        BEGIN
26            INSERT INTO tblUpsert VALUES (@empID, @fname, @lname, @emailid)
27        END
28    COMMIT TRAN
29END
30GO