sql server - Passing SQL stored procedure entirety of WHERE clause

Question

I have a SQL stored procedure of the form

SELECT [fields] FROM [table] WHERE @whereSql

I want to pass the procedure an argument (@whereSql) which specifies the entire WHERE clause, but the following error is returned:

An expression of non-boolean type specified in a context where a condition is expected

Can this be done?

Answer 1

The short answer is that you can't do it like this -- SQL Server looks at the contents of a variable as a VALUE. It doesn't dynamically build up the string to execute (which is why this is the correct way to avoid SQL injection attacks).

You should make every effort to avoid a dynamic WHERE as you're trying to do, largely for this reason, but also for the sake of efficiency. Instead, try to build up the WHERE clause so that it short-circuits pieces with lots of ORs, depending on the situation.

If there's no way around it, you can still build a string of your own assembled from the pieces of the command, and then EXEC it.

So you could do this:

DECLARE @mywhere VARCHAR(500)
DECLARE @mystmt VARCHAR(1000)
SET @mywhere = ' WHERE MfgPartNumber LIKE ''a%'' '
SELECT @mystmt = 'SELECT TOP 100 * FROM Products.Product AS p ' + @mywhere + ';'
EXEC( @mystmt )

But I recommend instead that you do this:

SELECT TOP 100 * 
    FROM Products.Product AS p 
    WHERE 
        ( MfgPartNumber LIKE 'a%' AND ModeMfrPartNumStartsWith=1)
    OR  ( CategoryID = 123 AND ModeCategory=1 )