dont insert if record exists

/*if key values exist don't insert new record*/
SELECT

/*if exists don't insert*/
CASE
WHEN ISNULL(gradeId, -1) = -1 THEN
INSERT INTO tblScores
(gtStudentId, assignmentId, score)
VALUES (@.nStudent, @.nAssignment, 0)
END

FROM tblScores
WHERE gtStudentId = @.nStudent AND assignmentId = @.nAssignment

tblScores has two fields comprising its primary key (gtStudentId, assignmentId) and the gradeId field is a required filed in this table.

I'm getting syntax errors when I click check syntax (near keywords insert from and end).

one other note: this CASE END is nested inside a BEGIN END loop, is this the problem? Is the 'End" of the 'Case' closing the 'End' of the 'Begin'?

thanksI think the problem is that the INSERT-statement is not a result_expression (see BOL). Besides, execute an INSERT-statement from a select-case? What are you trying to accomplish?|||Besides, execute an INSERT-statement from a select-case? What are you trying to accomplish?

Its rather complicated but I'll try:

This code is inside a trigger for an enrollment table. If a student joins a class late (after assignements have been assigned) then the trigger creates new records in the scores table giving the new student a 0 for each assignment already assigned to the class. If a student gets unenrolled from the class the scores are maintained in case the student gets reenrolled. If the student gets reenrolled then I want to do a select on the scores table and bypass any scores the student may have had prior to being unenrolled.
INSERT-statement is not a result_expression
How does one do a conditional INSERT?|||IF NOT EXISTS(
SELECT TOP 1 gradeId
FROM tblScores
WHERE
gtStudentId = @.nStudent
AND assignmentId = @.nAssignment)
BEGIN

INSERT INTO tblScores (
gtStudentId,
assignmentId,
score)

SELECT
@.nStudent,
@.nAssignment,
0
END|||I forget which site...but we just did this...Gotta find the code I wrote...

Why would you want to double every access to the database?

Just handle the dup key error...in a calling sproc or the application...

EDIT: It was originally about Contraints...same thing

USE Northwind
GO

CREATE TABLE myTable99(Col1 char(1), CHECK(Col1 IN ('Y','N')))
GO

CREATE PROC mySproc99 @.x char(1) AS INSERT INTO myTable99 SELECT @.x RETURN
GO

CREATE PROC mySproc00
AS
DECLARE @.rc int
EXEC @.rc = mySproc99 'Y'
SELECT @.rc, @.@.ERROR
EXEC @.rc = mySproc99 'B'
SELECT @.rc, @.@.ERROR
GO

EXEC mySproc00
GO

DROP PROC mySproc00
DROP PROC mySproc99
DROP TABLE myTable99
GO|||I did not understand very clearly what you are trying to do... is it inserting into your table only those records that _do not already exist_ in that table?

If so, ty this:

Insert into tblScores (gtStudentId, assignmentId, score)

VALUES (@.nStudent, @.nAssignment, 0)

Where (not exists (Select * from tblScores
Where WHERE gtStudentId = @.nStudent
AND assignmentId = @.nAssignment))|||My Point, just attempt the insert...and trap the Error if it fails

Don't make 2 data access attempts to the database