I have a SQL stored procedure of the form
SELECT [fields] FROM [table] WHERE @whereSql
An expression of non-boolean type specified in a context where a condition is expected
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 )