Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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. :)

share|improve this question
3  
Why do you need to use table variable? Could you not use a temp table instead? –  Mikael Eriksson Jan 26 '13 at 9:32
    
Can you explain why "I would also like to avoid using an if-else statement". –  AlexKuznetsov Jan 27 '13 at 3:47
    
I probably should have stated that I would LIKE to use a table variable because I prefer to keep my SQL clean rather than dealing with a bunch of temp table cleanup and if-else statements. –  Chiramisu Jan 28 '13 at 17:26
add comment

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer
    
Brilliant use of COALESCE! I would have never thought of that. :) I also added set @filter = nullif(@filter,'') just before that to ensure an empty string is never passed in which would create an invalid SQL statement. I also if object_id(N'tempdb..#tbl') is not null drop table #tbl before the #tbl create to ensure I'm starting with a clean slate. –  Chiramisu Jan 28 '13 at 18:26
add comment

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
share|improve this answer
3  
The question is about SQL Server 2005 and user defined table types was introduced in SQL Server 2008. –  Mikael Eriksson Jan 26 '13 at 9:29
2  
Sorry, I missed that this was for sql 2005. Aaron Bertrand's answer above is your best bet. –  StrayCatDBA Jan 26 '13 at 17:12
    
@StrayCatDBA For future reference, would this create a permanent defined type in the database? Server? Thanks for the answer anyway! :) –  Chiramisu Jan 28 '13 at 17:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.