Add a IF ...ELSE statement to stored procedure to skip duplicate primary keys
sql-server, stored-procedures, t-sql
Solution
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
Problem
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) ```