SQL Take filter from a value from a column in a table

Question

I have a table with different columns. Each column contains a filter.

Course	Filter1	Filter2
First	and startdate > '20200101'	and active = 0
Second	and startdate > '20210101'	and active = -1

I want to dynamically create a SQL with those filter

DECLARE @filter1 as varchar(1000)
DECLARE @filter2 as varchar(1000)
DECLARE File_Cursor CURSOR FOR
SELECT filter1, filter2 FROM _table_filters

OPEN File_Cursor;

FETCH NEXT FROM File_Cursor INTO @filter1, @filter2;
WHILE @@FETCH_STATUS = 0
BEGIN
    declare @sql varchar(8000) = ' 
        select   statusdate  
        from t_table   
        where 1=1
        '''+@filter1+''' 
        '''+@filter2+''' 
    '
    exec(@sql)

    FETCH NEXT FROM File_Cursor INTO @filter1, @filter2;
END;

CLOSE File_Cursor;
DEALLOCATE File_Cursor;

However the query has filter with single quotes and I can't get it right.

select   statusdate  
from t_table   
where 1=1
'and startdate > '20200101'' 
'and active=0'

Which dbms are you using? (The above code is very product specific.) — jarlh, Sep 15 '21 at 18:05

score 0 · Answer 1 · answered Sep 15 '21 at 17:50

0

Just a guess, for instruction you don't need additional quotes

    declare @sql varchar(8000) = ' 
        select   statusdate  
        from t_table   
        where 1=1 ' + @filter1 + '  ' + @filter2

answered Sep 15 '21 at 17:50

armagedescu

1,758
2
20
31

Of course you are a genious! – Nikka Sep 16 '21 at 10:40

SQL Take filter from a value from a column in a table

1 Answers1