SQL to make sure incoming id is not already in a field

Question

I've got 3 fields (grader 1, grader 2, grader 3) and I'm coming in with a grader ID and I want to make sure that grader id isn't one of the three (if they're not null).

Did I do this right?

WHERE @fycuserid <> ISNULL(grader1_fyc,0)
AND @fycuserid <> ISNULL(grader2_fyc,0)
AND @fycuserid <> ISNULL(grader3_fyc,0)

Answer 1

First off, you can't just say "SQL", because every DB has it's own strain of SQL. For instance, in DB2 you'd say:

WHERE @fycuserid <> COALESCE(grader1_fyc, 0)

That being said, assuming your DB's ISNULL is equivalent to COALESCE, your code seems good to me.

Answer 2

Well, there's OK and there's better. The design as you've described it is OK - it'll probably do what you want - but there are ways to make it better.

As things stand, the table in question has three fields, all of which contain essentially the same information - the ID of a grader. As you've seen, to check each of these fields special code has to be written to compare the procedure's parameter to grader1, grader2, and grader3 - and this special code will have to be repeated each time a similar comparison has to be made. This is OK, but I think you can see this is going to get a bit cumbersome.

When doing database normalization certain rules are applied, and sometimes the database design has to be changed. The basic things to check for to determine if a table is in first normal form are

1. Does the table faithfully represent a relation, and
2. Are there any repeating groups?

From your post it's tough to say whether or not the table represents a relation properly, but from the information given it's easy to see that there is a repeating group of fields - the three grader fields. These three fields could be removed from this table and a new table added. Let's say that the table mentioned in the post is PAPERS and that it contains a primary key called ID_PAPERS. A table to hold information on the graders who graded the paper could be called PAPER_GRADERS, and might look like this:

CREATE TABLE PAPER_GRADERS
  (ID_PAPER    NUMBER
     REFERENCES PAPERS(ID_PAPER),
   ID_GRADER   NUMBER
     REFERENCES GRADERS(ID_GRADER),
   PRIMARY KEY (ID_PAPER, ID_GRADER));

Now, if a paper is graded by a given grader a row would be created in PAPER_GRADERS with the ID of the paper and the ID of the grader. If a paper is graded by two graders there would be TWO rows created in PAPER_GRADERS, each having the same ID_PAPER but each row would have the ID of a different grader. This is what is called a "one-to-many" relationship - in this case, one paper can be graded by many graders, and there's no limit to the number of graders of a single paper.

Now, in the procedure that was originally mentioned, instead of having to explicitly check to see if one of the three grader fields contained the ID of the grader which was passed to the procedure, the SQL would be different - you'd do an inner join of PAPERS and PAPER_GRADERS, looking for an existing grader which matched the parameter. A SELECT to do this would look something like

SELECT *
  FROM PAPERS p
INNER JOIN PAPER_GRADERS g
  USING (ID_PAPER)
WHERE g.GRADER = @fycuserid

This type of database construct is very handy.

I hope this helps.