Nested if statements in SQL Server stored procedure SELECT statement

Question

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):

@searchf1 varchar(100),   -- search filter 1
@searchr1 varchar(100),   -- search result 1
@searchf2 varchar(100),   -- search filter 2
@searchr2 varchar(100),   -- search result 2
@direction char(1),   -- direction to order results in
AS

set nocount on
set dateformat dmy

SELECT *
  FROM database.dbo.table T
 WHERE T.deleted = 'n'
ORDER BY CASE @direction
            WHEN 'A' THEN T.id
            WHEN 'D' THEN T.id DESC
         END

END

set nocount off

I have also tried the lines from ORDER BY as:

IF @direction = 'N' THEN
     ORDER BY 
          T.id
ELSE
     ORDER BY
          T.id DESC

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:

WHERE 
     deleted = 'n'
     IF @searchf1 <> '' THEN
         AND fieldf1 = @searchf1 AND fieldr1 = @searchr1

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.

Answer 1

For this I would try to go with a more formal Dynamic SQL solution, something like the following, given your defined input parameters

DECLARE @SQL VARCHAR(MAX)

SET @SQL = '
SELECT

FROM
     database.dbo.table T
WHERE
     T.deleted = ''n'' '

--Do your conditional stuff here
IF @searchf1 <> '' THEN
    SET @SQL = @SQL + ' AND fieldf1 = ' + @searchf1 + ' AND fieldr1 = ' + @searchr1 + ''' '

--Finish the query
SET @SQL = @SQL + ' ORDER BY xxx'

EXEC(@SQL)

DISCLAIMER: The use of Dynamic SQL is NOT something that should be taken lightly, and proper consideration should be taken in ALL circumstances to ensure that you are not open to SQL injection attacks, however, for some dynamic search type operations it is one of the most elegant route.

Answer 2

Another option that you might have, depending on the data type of your field, if nulls are NOT allowed, would be to do something like this.

SELECT * FROM database.dbo.table T WHERE T.deleted = 'n' AND fieldf1 = COALESCE(@searchf1, fieldf1) AND fieldr1 = COALESCE(@searchr1, fieldr1) --ETC ORDER BY fieldf1

This way you are not using dynamic SQL and it is fairly readable, just have the variable be null when you are looking to omit the data.

NOTE: As I mentioned this route will NOT work if any of the COALESCE columns contain null values.

Answer 3

Try it this way:

SELECT *  FROM database.dbo.table T WHERE T.deleted = 'n'
ORDER BY 
CASE WHEN @direction='A' THEN T.id END ASC,
CASE WHEN @direction='D' THEN T.id END DESC

Source Article:
http://blog.sqlauthority.com/2007/07/17/sql-server-case-statement-in-order-by-clause-order-by-using-variable/