Geek asks and answers

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

I have a catch attempt in my physical C # code that just skips this insertion process when an error occurs and the loop continues to populate the database. However, this is the wrong coding practice.

So, I would like to add instructions IFto the stored procedure below, which will simply skip if the primary key already exists. My primary key @id.

How can i do 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)
+3
source share
4 answers
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 really want to update the record, if it already exists, and insert, if not, the template looks like this:

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
+4
source 
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)

MERGE.

SO, .

+1

You can check for an entry in the table and insert the entry into one statement.

enter image description here

+1
source

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
0
source

More articles:


All Articles