Reputation: 7707
I have sql server procedure, please 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 don't want to insert into table tblDelegate if the inserting courseid and cpuserid is same for that records in table tblDelegate
Upvotes: 3
Views: 4338
Reputation: 1850
What version of SQL Server you are using ? If you are on 2008 look up the MERGE statement.
Use the IF NOT Exists Clause then as pointed in the first answer.
Upvotes: 1
Reputation: 17639
Add a unique key constraint to the courseid
and cpuuserid
columns.
You'll then get a key violation if you try to insert a dupe.
As well as doing this you can test to see if the value exists before inserting it using your stored procedure.
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
Upvotes: 3
Reputation: 432742
Simply test first. In SQL Server 2005 you could also TRY/CATCH to ignore a 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"?
Upvotes: 2