SQL: Building where clause

Question

Is there a way to get build a WHERE clause on the fly in a sql statement?

This code is within a Stored Procedure. I have x amount of parameters and each parameter's default value is NULL

SELECT *
FROM MyTable m
WHERE
   IF(NOT(@Param1 IS NULL))
     m.Col1 = @Param1
   END IF
AND
   IF(NOT(@Param2 IS NULL))
     m.Col2 = @Param2
   END IF

[EDIT:] I'm running SQL server 2005. [EDIT:] The number of parameters are fixed, but can have a NULL value. If a parameter has a NULL value it shouldn't be included in the WHERE clause. Each parameter also correlates to a specific column.

Answer 1

Isn't this equivalent to the following, without any dynamic behavior in it?

SELECT *
FROM MyTable m
WHERE
   (@Param1 IS NULL OR m.Col1 = @Param1)
AND
   (@Param2 IS NULL OR m.Col2 = @Param2)

Or is there a possibility that the columns themselves might be missing?

Answer 2

Assuming SQL Server 2005+ syntax because the database wasn't specified... Highly recommended reading before addressing the query: The curse and blessings of dynamic SQL

DECLARE @SQL NVARCHAR(4000)
    SET @SQL = N'SELECT m.*
                   FROM MyTable m
                  WHERE 1 = 1 '

    SET @SQL = @SQL + CASE 
                        WHEN @param1 IS NOT NULL THEN ' AND m.col1 = @param1 '
                        ELSE ' '
                      END   

    SET @SQL = @SQL + CASE 
                        WHEN @param2 IS NOT NULL THEN ' AND m.col2 = @param2 '
                        ELSE ' '
                      END

BEGIN

  EXEC sp_executesql @SQL,
                     N'@param1 [replace w/ data type], @param2 [replace w/ data type]'
                     @param1, @param2

END

Answer 3

You may be forced to use dynamic sql whether you're using a stored proc or not. Before you implement this stored proc, think about a few things.

1. Are the parameters themselves dynamic? Would you use 2 parameters one call, 10 the next? If this is the case you will need to create a ton of "placeholder" stored proc parameters that may not be used every call. What if you define 10 placeholder parameters but then need 11? This is not clean. Maybe you could use some sort of array parameter....

2. If parameters are dynamic, you will be forced to use dynamic SQL within your proc. This opens you up to injection attacks. You will have to manually escape all input within your stored proc. Using dymamaic sql in a proc defeats one of the main reasons for using procs.

If parameters are truly dynamic, I may actually favor generating sql from the code rather than the stored proc. Why? If you generate from the code you can use the ADO library to escape the input.

Just make sure you do something like....

sql = "select * from table where colA=@colA"; //dyanmically generated SQlCommand.Parameters.add("@colA", valueA);

And not....

sql = "select * from table where colA=" + valueA; //dyanmically generated the wrong way

Answer 4

Also a 3rd thing to think about. What if the data type of the parameters is dynamic? Stored procs start to fall apart because they are a defined interface. If your operations do not conform to a interface, trying to squish them into a pre-set interface is going to be ugly.

If you're making some sort of open-ended graphical query tool, this situation will pop-up. Most of the time your data access will conform to an interface, but when it doesn't..... stored procs may not be the way to go.