Making sure record does not exist before inserting

[christer99]

I have this Store Procedure that inserts records into my CSRProg table. I added a line to prevent that an existing record is being added: if not exists (select * from CSRPRog where pol_idx=@param2 and exp=@param4)

However, if I try simulate trying to add the same record twice on after on another, I am getting a SQL error: Incorrect syntax near the keyword 'where'. Note that I am only getting the error after the program tries enter to same record a 2nd time (the first time, when the record does not exist, there is no error).

Is there any other way to prevent existing records from being added?


CREATE PROCEDURE dbo.saveCSRPOLHIS
@param1 VARCHAR(21), @param2 NVARCHAR(12), @param3 NVARCHAR(7) , @param4 NVARCHAR(8), @param5 NVARCHAR(4) , @param6 SMALLINT, @param7 VARCHAR(4) , @param8 INT, @param9 VARCHAR(20), @param10 VARCHAR(20)
AS

if @param9='CHECKBOX1'
BEGIN
BEGIN SET NOCOUNT ON
if not exists (select * from CSRPRog where pol_idx=@param2 and exp=@param4)
INSERT INTO CSRProg ( RECIDX, POL_IDX, REC2, EXP, EXPYEAR, PROGID, PROGRESSWHO, LOGID,CHECKBOX1) VALUES ( @param1, @param2, @param3, @param4, @param5, @param6, @param7, @param8, @param10 )
SELECT NEWID = SCOPE_IDENTITY() END SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS OFF
END
if @param9='CHECKBOX3'
BEGIN
BEGIN SET NOCOUNT ON
INSERT INTO CSRProg ( RECIDX, POL_IDX, REC2, EXP, EXPYEAR, PROGID, PROGRESSWHO, LOGID,CHECKBOX3) VALUES ( @param1, @param2, @param3, @param4, @param5, @param6, @param7, @param8, @param10 )
SELECT NEWID = SCOPE_IDENTITY() END SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS OFF
END
if @param9='CHECKBOX2'
BEGIN
BEGIN SET NOCOUNT ON
INSERT INTO CSRProg ( RECIDX, POL_IDX, REC2, EXP, EXPYEAR, PROGID, PROGRESSWHO, LOGID,CHECKBOX2) VALUES ( @param1, @param2, @param3, @param4, @param5, @param6, @param7, @param8, @param10 )
SELECT NEWID = SCOPE_IDENTITY() END SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS OFF
END
if NOT (@param9 IN('CHECKBOX1','CHECKBOX2' ,'CHECKBOX3' ))
BEGIN
BEGIN SET NOCOUNT ON
if not exists (select * from CSRPRog where pol_idx=@param2 and exp=@param4) INSERT INTO CSRProg ( RECIDX, POL_IDX, REC2, EXP, EXPYEAR, PROGID, PROGRESSWHO, LOGID, PROGRESS) VALUES ( @param1, @param2, @param3, @param4, @param5, @param6, @param7, @param8, @param10 )
SELECT NEWID =SCOPE_IDENTITY() END SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS OFF
END
GO

 

[hmckillop]

Try putting a unique constraint on the fields specified and then handling an error on the insert.

"I'm living so far beyond my income that we may almost be said to be living apart