as fas as I understand you would like to control the order of execution if x is not present search for y and so on. Therefore a simple IN clause or the case expressions probably won't work if you've got entries for x as well as for y and z....by result returning multiple rows.
The best way I can think of would be to introduce a priority factor within a cte and use this to return only the relevant row.
Here is an example. For simplicity I use a table with the following structure
CREATE TABLE test
( StudentID int,
The table has the following entries:
As you described we'd like to return X first, then possibly Y (if no X present) and at last Z and only one row per student if the applicable subject code.
XYZ is easy because it is alphabetical but let's introduce some additional complexity guessing that there could be also an A which we would like to return if there is no X.
Here is a SQL Fiddle with my example
I would use the following query to achieve this:
VALUES ('X', 1), ('A', 2), ('Y', 3), ('Z', 4)
) AS tab(code,prio)
SELECT StudentId, SubjectCode
ROW_NUMBER() OVER (PARTITION BY StudentId ORDER BY prio) AS rn
FROM test t
INNER JOIN cteSortLogic sl ON t.SubjectCode = sl.code
WHERE SubjectCode IN ('X', 'Y', 'Z')
WHERE res.rn = 1
The CTE gives me a simple table with the codes and their priority of output:
Then I join it to my table and add a rownumber based on the priority:
So here's the output of my subselect (or derived query) named "res":
I have still got all the rows but assigned a rownumber based on my priority. Then the last thing I have to do is to filter only the most relevant rows which are the one having row number 1 .
Presto...here's the result of the whole query:
So given your example query your code would look like this:
VALUES ('X', 1), ('Y', 2), ('Z', 3)
) AS tab(code,prio)
select dbo.GetStudentNumber('M', mcl.StudentID) As Student_Number,
SubjectCode, ROW_NUMBER() OVER (PARTITION BY StudentId ORDER BY prio) AS rn, *
FROM dbo.Question mcl
inner join Answer mclo on mclo.AnswerID = mcl.AnswereID
inner join cteSortLogic sl on mclo.SubjectCode = sl.code
where StudentID = 6588
AND SubjectCode IN ('X', 'Y', 'Z')
rn = 1