sql server 2008 - Tsql: Search multiple fields per row for a given set of string values -

sql server 2008 - Tsql: Search multiple fields per row for a given set of string values -

i having problem wrapping head around next problem, having reporting total sales promotions in generic plenty way can handle routine user requests via subscription. (or allow users info on demand)

i trying write query ssrs study take input text parameter allows multipule values. illustration (‘code1,code2,code3’)… , searches 4 sale “id” fields in illustration table below – returning each [documentno], [lineno_] combination 1 of provided codes appears in 1 of id fields. 4 fields cannot null, can empty string. possible more 1 code apply order line, 1 in each field. each [documentno], [lineno_] combination should appear 1 time in output, can sum [qty] , right total.

[documentno] [varchar](20) not null [lineno_] [int] not null [salesdiscountreasonid] [varchar](20) not null [couponid] [varchar](30) not null [campaignid] [varchar](20) not null [promotioncodeid] [varchar](20) not null [qty] [int] not null

i cannot alter sturcture of info in info warehouse, , cannot expect users routinely inquire study maintain track of type of promotion coupon, campaign, salesdiscount, promocode used implement sale since don’t setup sales or info entry of orders.

i think can want like:

where (','+@codes+',' '%,'+salesdiscountreasonid+',%' or ','+@codes+',' '%,'+couponid+',%' or ','+@codes+',' '%,'+campaignid+',%' or ','+@codes+',' '%,'+promotioncodeid+',%' )

the additional comma used avoid confusion between, say, 10 , 100.

if constructing sql, much, much, much improve do:

where salesdiscountreasonid in (''' + replace(@codes, ',', ''',''') + ''') or couponid in (''' + replace(@codes, ',', ''',''') + ''') or campaignid in (''' + replace(@codes, ',', ''',''') + ''') or promotioncodeid in (''' + replace(@codes, ',', ''',''') + ''')'

one reason in can create utilize of indexes , perform much better.

sql-server-2008 tsql ssrs-2008