Stop insertion into table if record already exists

Question

I have a sql server procedure, see below.

ALTER PROCEDURE [dbo].[uspInsertDelegate]
(
    @CourseID int,
    @CPUserID int,
    @StatusID int,
    @CreateUser varchar(25)

)
AS
    SET NOCOUNT OFF;
INSERT INTO tblDelegate                      
(
    CourseID, 
    CPUserID, 
    StatusID, 
    CreateUser 

)
VALUES     
(
    @CourseID,
    @CPUserID,
    @StatusID,
    @CreateUser
)

RETURN

Now I do not want to insert tblDelegate into the table if the insert of courseid and cpuserid are the same for these records in the tblDelegate table

Answer 1

Just try again. In SQL Server 2005, you can also TRY / CATCH ignore the duplicate error.

IF NOT EXISTS (SELECT *
        FROM tblDelegate
        WHERE CourseID = @CourseID etc)
    INSERT INTO tblDelegate                      
    (
        CourseID, 
        CPUserID, 
        StatusID, 
        CreateUser 

    )
    VALUES     
    (
        @CourseID,
        @CPUserID,
        @StatusID,
        @CreateUser
    )

May I ask: do you mean "SET NOCOUNT ON"?

Answer 2

Add a unique key to the courseidand columns cpuuserid.

, .

, , , .

BEGIN TRAN

SELECT 1 
FROM tblDelegate WITH (TABLOCK) 
WHERE CourseId=@CourseID 
      AND CPUserID=@CPUserId
IF @@rowcount = 0
BEGIN
--Record doesn't already exist
--Insert it
END

COMMIT

Answer 3

What version of SQL Server are you using? If you are in 2008, see the MERGE manual.

Use the IF NOT Exists clause as indicated in the first answer.