Add WHERE conditions dynamically

Question

I'm trying to replicate a crystal report with a dynamic parameter. If I type a string into the parameter screen and click the arrow button, it adds the parameter to a list:

The resulting query looks like this, but the list can grow with additional OR @param clauses:

SELECT * FROM table_name WHERE @param LIKE 'cfe%' OR @param LIKE 'abr%'

How can I create a list like that in SSRS to contain several parameters? The query below is one possible example:

SELECT * FROM table_name WHERE
   @param LIKE 'cfe%'
OR @param LIKE 'abr%'
OR @param LIKE 'fez%'
OR @param LIKE 'zez%'

I tried using multiple values in the parameter, but as soon as I do I can't type in the parameter box:

I tried this but it did not work. I also tried to use CONTAINS but there are no indexes for a view.

hi @4444 thanks for my editing question but an answer is always better ... — Andy K, Oct 03 '16 at 14:07
Hi Andy. I don't know the answer to your question. I improved and formatted it as best I could to attract those who *can* answer it. — 4444, Oct 03 '16 at 14:08
Questions with broken links don't attract as many answers. You should have stopped at "thanks for editing my question" — dazedandconfused, Oct 03 '16 at 14:10
@AndyK, it is unclear how your parameter should filter your table. You can create a `Text` parameter without available and default values so you can type the value of the parameter, but what is the column you need to use in the where clause to filter the rows? Is it dynamic based on your parameter selection? — alejandro zuleta, Oct 03 '16 at 14:14
hi @alejandrozuleta the parameter is @Param. With CR, you can replicate the parameter several times but with different information. Example: `WHERE @param LIKE 'CFE%'` -> 1 param But if I want to have another string with the same parameter in addition to the first one, I can add another string like in the gif `WHERE @param LIKE 'CFE%' OR @param LIKE 'ABR%'` -> 2 params. Have a look at the gif, it is more straightforward I want to do the same in SSRS. With the textbox, it is doing what it should but I can have one string only. Let me know if it does not make sense — Andy K, Oct 03 '16 at 14:37
I don't see any difference in the two queries you posted.. what is your exact requirement? — Siva, Oct 03 '16 at 16:37
@Siva Andy is trying to recreate the behavior in the displayed GIF, but in SSRS instead of Crystal. Dynamically add parameters to a list then generate a query based on that input. — 4444, Oct 03 '16 at 19:49

JesalynOlson · Accepted Answer · 2016-10-04T20:12:46.617

2

You can use a single parameter string. The user separates entries in the list with a comma. The query got a lot worse though but I have you an example of variable number of prefixes with comma separated.

I hope this helps. Its not as simple or as pretty.

In the dataset, use a similar query or proc that uses the parameter like so and it should work:

Declare @prefixes varchar(1000)

set @prefixes='abc,defg,efgh,hij,jkl,mno'

declare @sql nvarchar(max) = ''

declare @currentint int

set @currentint = 1

declare @maxint int

set @maxint = len(@prefixes) - len(replace(@prefixes, ',', '')) + 1

declare @currentcommaposition int


set @sql = 'IF OBJECT_ID(''tempdb..#tempTest'') IS NOT NULL DROP TABLE #tempTest
create table #tempTest
(
ID INT,
name varchar(100)
)

insert into #tempTest
(id,name)
select 1,''abcd''
union
select 2, ''defghijk''
union
select 3,''efghoot''
union
select 4,''hijack''
union
select 5,''jklmo''
union
select 6,''mnopoly''
union
select 7,''pqrstuv''
union
select 8,''tubool''
IF OBJECT_ID(''tempdb..#testresults'') IS NOT NULL DROP TABLE #testresults
create table #testresults
(
 id int, name varchar(100)
 )
 declare @prefixes varchar(100) = ''' + @prefixes + ',''' + char(10) + ' declare @currentint int declare @maxint int = ' + convert(varchar(10),@maxint) + char(10)

while ( @currentint <= @maxint )
begin

set @sql = @sql  + 'set @currentint = ' + convert(varchar(10),@currentint) + ' declare @suffix' + convert(varchar(2), @currentint) + ' VARCHAR(100)' + char(10)
+ 'set @suffix' + convert(varchar(2), @currentint) + '= substring(@prefixes,0,charindex('','',@prefixes))' + char(10)
+ 
'set @prefixes=Right(@prefixes,len(@prefixes)-charindex('','',@prefixes))' + char(10) +
'insert into #testresults (id, name)
select id, name from #temptest t where t.name like @suffix' + convert(varchar(2), @currentint) + ' + ''%''' + char(10)
+ 'if (@currentint = @maxint) begin select * from #testresults end ' + char(10)

set @currentint = @currentint + 1

end

exec sp_executesql @sql

edited Oct 04 '16 at 20:12

answered Oct 03 '16 at 15:03

JesalynOlson

503
3
9

Hi @Alwaysariyana, it would work if I was not looking at suffix but full strings. As you can see, I'm using a lot of `LIKE 'abr%'` or any suffixes I will need – Andy K Oct 03 '16 at 15:05
Do you have a defined max number of prefixes or is it always variable? – JesalynOlson Oct 03 '16 at 16:54
Hi @Alwaysariyana, always variable – Andy K Oct 04 '16 at 06:57
1

Your best bet will be dynamic SQL then, now is the length of the entered prefixes always the same or is that variable as well to? – JesalynOlson Oct 04 '16 at 13:33
it would be variables – Andy K Oct 04 '16 at 13:39
never done that to be honest. if you have an idea on how, I'm all ears – Andy K Oct 04 '16 at 14:25
1

@AndyK I edited the answer to use dynamic SQL. It is not as pretty but it should work. You'll have to finagle so it correctly filters your data but it at least gives you an idea on how to use dynamic sql for the result you want. – JesalynOlson Oct 04 '16 at 20:14
Hi @Alwaysariyana thanks but I cannot accept the answer. It is way too heavy and I can barely understand your code. My aim is to get simple answers and most of all that someone can reproduce. I'm afraid, it is not the case here – Andy K Oct 05 '16 at 08:45
Your problem is not simple so the answers cannot be. The only other thing I can say is the other option would be to have separate parameters that they enter in the prefixes but that again only lets your users have up to a certain number. This will be your simplest solution which is less dynamic then my answer above. – JesalynOlson Oct 05 '16 at 16:51

score 1 · Answer 2 · answered Oct 05 '16 at 16:57

The second option for you will be having parameters for each suffix a user can answer and allow them to be blank or null as the default. This will limit the number of prefixes the user can enter, but I do think you should be able to guesstimate the max number a user would enter. Or the user can run the report multiple times and when they do they export to excel to mash the reports together if they want to.

This is a bit easier to understand for the developer but more work for the user.

So in your stored procedure you will then use a statement like below:

select *
from dbo.Test t
WHERE 
 ( ISNULL(@Prefix1,'') <> '' AND t.TestName LIKE @Prefix1 + '%')
OR
 ( ISNULL(@Prefix2,'') <> '' AND t.TestName LIKE @Prefix2 + '%')
OR
 ( ISNULL(@Prefix3,'') <> '' AND t.TestName LIKE @Prefix3 + '%')
OR
 ( ISNULL(@Prefix4,'') <> '' AND t.TestName LIKE @Prefix4 + '%')
OR
 ( ISNULL(@Prefix5,'') <> '' AND t.TestName LIKE @Prefix5 + '%')
OR
 ( ISNULL(@Prefix6,'') <> '' AND t.TestName LIKE @Prefix6 + '%')
OR
 ( ISNULL(@Prefix7,'') <> '' AND t.TestName LIKE @Prefix7 + '%')
OR
 ( ISNULL(@Prefix8,'') <> '' AND t.TestName LIKE @Prefix8 + '%')
OR
 ( ISNULL(@Prefix9,'') <> '' AND t.TestName LIKE @Prefix9 + '%')
OR
 ( ISNULL(@Prefix10,'') <> '' AND t.TestName LIKE @Prefix10 + '%')

Add WHERE conditions dynamically

2 Answers2