generate where clause dynamically with dynamic column names

Question

Hi,

I have data in the SQL 2005 table as below.

Table Name: FilterData
CategoryID ColumnID  AnswerCode
--------------------------------
0349       Q15       02
0349       Q15       03
0349       Q16       04

Table Name: TransactionData
CategoryID Q15
---------------------------------
0349       01  
0349       02
0349       03
0349       04
0349       05
0349       06

FilterData table has information about what columns will be used to filter select statements on TransactionData. So when i execute Select * from TransactionData, i want to build Where Clause from FilterData table so that i can fire it on TransactionData table for that CategoryID.

Can someone please help me out that how can i do this?

Answer 1

This is the where clause for the above criteria. However, you need to clarify what you mean by dynamic? Will you be receiving 2 sets of two criteria - i.e. (Q15 and '02','03'), and ('Q16' and '04')? Also, you should make these query parameters to prevent SQL Injection.

Where

(

ColumnId = 'Q15' and AnswerCode IN('02','03')

)

OR

(

ColumnId = 'Q16' and AnswerCode IN('04')

)

Answer 2

If I understand your question correctly, the code below should work. Note that I've created a function to help with the string concatenation. I'm not sure if you wanted to AND or OR your conditions. I've used AND in my sample. You can easily adjust it for OR if that's appropriate.

/* Set up sample data */
create table FilterData (
    CategoryID char(4),
    ColumnID char(3),
    AnswerCode char(2)
)

insert into FilterData
    (CategoryID, ColumnID, AnswerCode)
    values
    ('0349','Q15','02')
insert into FilterData
    (CategoryID, ColumnID, AnswerCode)
    values
    ('0349','Q15','03')
insert into FilterData
    (CategoryID, ColumnID, AnswerCode)
    values
    ('0349','Q16','04')
go

/* Helper function to concatenate all AnswerCodes for a given ColumnID */
create function dbo.fnStringAnswerCodes(@ColumnID char(3))
returns varchar(1000)
as
begin
    declare @CodeString varchar(1000)
    set @CodeString = @ColumnID + ' in ('

    select @CodeString = @CodeString + '''' + AnswerCode + ''','
        from FilterData
        where ColumnID = @ColumnID

    /* Remove trailing comma and add closing parens */
    select @CodeString = left(@CodeString, len(@CodeString)-1) + ')'

    return @CodeString
end
go

declare @CategoryID char(4)
declare @SQLString varchar(1000)
declare @WhereClause varchar(1000)

set @CategoryID = '0349'
set @SQLString = 'select * from TransactionData '
set @WhereClause = 'where CategoryID=''' + @CategoryID + ''' and '


select @WhereClause = @WhereClause + dbo.fnStringAnswerCodes(ColumnID) + ' and '
    from FilterData
    where CategoryID = @CategoryID
    group by ColumnID

/* Remove Trailing 'AND' */
set @WhereClause = LEFT(@WhereClause, len(@WhereClause)-3)

set @SQLString = @SQLString + @WhereClause
select @SQLString

/* Last step would be to dynamically execute the string we built */
/* exec sp_ExecuteSQL @SQLString */

/* Clean Up */
drop function dbo.fnStringAnswerCodes
drop table FilterData