Nested if statements in SQL Server stored procedure SELECT statement
I'm new here, and relatively new to stored procedures, so please bear with me! I've checked related questions on here and can't find anything that works in this instance.
I am trying to build a stored procedure (MS SQL Server 2005) that takes a number of passed in values and in effect dynamically builds up the SQL as you would with inline SQL.
This is where I've come unstuck.
We have (somewhat simplified for clarity):
I have also tried the lines from ORDER BY as:
Both approaches give me an error along the lines:
"Incorrect syntax near the keyword 'DESC'." (which references the line id DESC following the final ORDER BY
As part of this stored procedure I also want to try to feed in matched pairs of values which reference a field to look up and a field to match it to, these could either
be present or ''. To do that I need to add into the SELECT section code similar to:
This however generates errors like:
Incorrect syntax near the keyword 'IF'.
I know dynamic SQL of this type isn't the most elegant. And I know that I could do it with glocal IF ELSE statements, but if I did the SP would be thousands of lines long; there are going to up to 15 pairs of these search fields, together with the direction and field to order that direction on.
(the current version of this SP uses a passed in list of IDs to return generated by some inline dynamic SQL, through doing this I'm trying to reduce it to one hit to generate the recordset)
Any help greatly appreciated. I've hugely simplified the code in the above example for clarity, since it's the general concept of a nested IF statement with SELECT and ORDER BY that I'm inquiring about.