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 )

Answer 2

I believe this can be done using Dynamic SQL. See below:

CREATE PROCEDURE [dbo].[myProc]
@whereSql nvarchar(256)

AS
    EXEC('SELECT [fields] FROM [table] WHERE ' + @whereSql)
GO

That said, you should do some serious research on dynamic SQL before you actually use it. Here are a few links that I came across after a quick search:

• http://www.sommarskog.se/dynamic_sql.html

• http://msdn.microsoft.com/en-us/library/aa224806%28SQL.80%29.aspx

• http://www.itjungle.com/fhg/fhg100505-story02.html

Answer 3

Make sure you read this fully

www.sommarskog.se/dynamic_sql.html