Add a IF ...ELSE statement to stored procedure to skip duplicate primary keys

Question

I have a try catch in my physical C# code that just skips this insert process when a error accrues and continues the loop to fill the database. However this is bad coding practice.

So I would like to add an IF statement to the stored procedure below that will just skip if a primary key is already there. My primary key is @id.

How can I go about this?

CREATE PROCEDURE InsertProc
    (
    @id int, 
    @to nvarchar(100), 
    @from nvarchar(100), 
    @subject nvarchar(100), 
    @date datetime
    )
AS
    INSERT INTO Emails_Log (Email_ID, e_To, e_From, e_Subject, e_Date) 
    VALUES (@id, @to, @from, @subject, @date)

Nikola Markovinović · Accepted Answer · 2012-05-09T09:36:19.347

CREATE PROCEDURE InsertProc
    (
    @id int, 
    @to nvarchar(100), 
    @from nvarchar(100), 
    @subject nvarchar(100), 
    @date datetime
    )
    AS
    IF NOT EXISTS (SELECT NULL FROM Emails_Log
                    WHERE Email_ID = @ID)
    BEGIN
        INSERT INTO Emails_Log (Email_ID, e_To, e_From, e_Subject, e_Date) 
            VALUES (@id, @to, @from, @subject, @date)
    END

If you actually want to update record if already exists and insert if not, the pattern is as follows:

CREATE PROCEDURE InsertProc
    (
       @id int, 
       @to nvarchar(100), 
       @from nvarchar(100), 
       @subject nvarchar(100), 
       @date datetime
    )
    AS
       UPDATE Emails_Log
          SET e_To = @to, 
              e_From = @from, 
              e_Subject = @subject, 
              e_Date = @date
        WHERE Email_ID = @ID
       -- If there was no update it means that @ID does not exist,
       -- So we proceede with insert
       IF @@ROWCOUNT = 0
       BEGIN
          INSERT INTO Emails_Log (Email_ID, e_To, e_From, e_Subject, e_Date) 
               VALUES (@id, @to, @from, @subject, @date)
       END

score 1 · Answer 2 · edited May 23 '17 at 10:09

1

CREATE PROCEDURE InsertProc
    (
    @id int, 
    @to nvarchar(100), 
    @from nvarchar(100), 
    @subject nvarchar(100), 
    @date datetime
    )
    AS
    IF EXISTS(SELECT * From Emails_Log Where Email_ID = @id)
    UPDATE Emails_Log (Email_ID, e_To, e_From, e_Subject, e_Date) 
            SET e_To = @to, e_From = @from, e_Subject = @subject, e_Date = @date
            WHERE Email_ID = @id
    ELSE
    INSERT INTO Emails_Log (Email_ID, e_To, e_From, e_Subject, e_Date) 
            VALUES (@id, @to, @from, @subject, @date)

Another way to do this would be with MERGE command.

Look at this thread on SO to know more.

edited May 23 '17 at 10:09

Community

1
1

answered May 09 '12 at 09:15

LaGrandMere

10,265
1
33
41

1

set ...? would ((at)id, (at)to, (at)from, (at)subject, (at)date) go in there? – Pomster May 09 '12 at 09:18
1

Msg 102, Level 15, State 31, Procedure InsertUpdateProc, Line 11 Incorrect syntax near '('. : I got this error – Pomster May 09 '12 at 09:57

Romil Kumar Jain · Answer 3 · 2012-05-09T10:05:50.050

1

You can check for the existence of a record in table and insert the record in one statement.

enter image description here

edited May 09 '12 at 10:05

answered May 09 '12 at 09:58

Romil Kumar Jain

20,239
9
63
92

score 0 · Answer 4 · answered May 09 '12 at 09:28

try this code

CREATE PROCEDURE InsertProc
(
@id int, 
@to nvarchar(100), 
@from nvarchar(100), 
@subject nvarchar(100), 
@date datetime
)
AS
IF NOT EXISTS (SELECT Email_ID From Emails_Log Where Email_ID = @id)
BEGIN
    INSERT INTO Emails_Log (Email_ID, e_To, e_From, e_Subject, e_Date) 
        VALUES (@id, @to, @from, @subject, @date)
END

Add a IF ...ELSE statement to stored procedure to skip duplicate primary keys

4 Answers4