Conditional WHERE clause

Question

I have, from what I can tell, a unique question. I would like to know if there's a way to construct a query in which the existence of the actual where clause itself is conditional.

I'm writing a stored procedure where I would like to pass in a filter (e.g. Col1=1 or Col2=3) and my query looks something like the following:

set @filter = 'Col1=1 or Col2=3' --assume these variables have been declared already
select * from @tbl where @filter --I need to use a table variable

Note: I tried using sp_executesql and sp_sqlexec but neither take a table variable. I would also like to avoid using an if-else statement. Please do not suggest either of these as your answer. :)

Answer 1

Dynamic SQL won't be able to see your table variable unless you also declare it and populate it within the same dynamic SQL scope. A #temp table will work fine, and I'm not sure why you "need" to use a table variable, but you can always do this:

SELECT * INTO #tbl FROM @tbl;

Anyway assuming you can change your process:

CREATE TABLE #tbl(Col1 INT, Col2 INT);

INSERT #tbl SELECT 1,4
  UNION ALL SELECT 2,3
  UNION ALL SELECT 5,8;

DECLARE @sql NVARCHAR(MAX);
DECLARE @filter NVARCHAR(255);

SET @sql = N'SELECT Col1, Col2 FROM #tbl';

SET @filter = N'Col1 = 1 OR Col2 = 3';

SET @sql = @sql + COALESCE(' WHERE ' + @filter, '');

EXEC sp_executesql @sql;

DROP TABLE #tbl;

Results:

Col1        Col2
----------- -----------
1           4
2           3

As an aside, you cannot do things like this:

select * from @tbl where @filter

You need to build such statements dynamically. SQL Server won't see @tbl or @filter as entities or where clauses.

Answer 2

sp_executesql works just fine with table variables. You just need to create a user defined table type first.

CREATE TYPE IdTable TABLE AS (Id int NOT NULL PRIMARY KEY)

GRANT EXECUTE ON TYPE::IdTable TO PUBLIC

GO

DECLARE @sql NVARCHAR(1000)
DECLARE @tableVar IdTable

INSERT INTO @tableVar (3)

SET @sql = N'Select id from @p1 where ' + @filter

EXEC sp_executesql @sql, '@p1 IdTable readonly', @p1=@tableVar