How do I write a condition within an SQL Select statement

I have a rather long SQL query to pull some values out my database written inside a stored procedure. I am passing an IN parameter called pupiltype which will either =0 or =1.

If the pupiltype value=1, I need the line a line in my query to be:

 AND tbl_4.status=1

but if the pupiltype value is 0, it needs to be:

 AND (tbl_4.status=0 OR tbl_5.ID IS NOT NULL)

I can put a condition around my whole query, but this means duplicated lots of other lines of code which feels unneccersary. Is there a way of putting this conditional statement inside the SQL statement itself just around this one line to run different AND statements?

For example…:

WHERE tbl_1.sID=sID
    AND tbl_2.spID=spID 
    AND tbl_3.progress!=''
    AND tbl_4.status=1; // It's this line I need to be varied...

Cheers

Answer

Something like this:

( (pupiltype = 0 and tbl_4.status = 1) or
  (pupiltype = 1 and (tbl_4.status = 0 OR tbl_5.ID IS NOT NULL))
)

Advertisement

Answer