SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using LIKE in drop down parameter


Using LIKE in drop down parameter

Author
Message
Amy.G
Amy.G
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 290
I have a report generated from a stored procedure. There is a code called "cip" with different lengths of code. 2-digit is a higher level than 4-digit, etc. In the stored procedure, I wrote the WHERE statement as "cipcode like @cip+'%'" and if I set @cip = '51', I get all cip codes starting with 51, regardless of length. This is what I want.

This works in SSRS up to a point. Since most people do not have cip codes memorized, this needs to be a drop down list. When '51' is selected from the drop down list (really the label is selected), the LIKE part doesn't seem to work anymore, and I just get the one row result of 51, instead of multiple rows for 5102, 510201, etc.

I've tried multiple things, and nothing quite works. Can anyone help? Thank you.
geoff5
geoff5
SSC Veteran
SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)

Group: General Forum Members
Points: 244 Visits: 543
Just to be clear, are you passing the value from the report parameter to the parameters property of the dataset based on your stored proc? Or did you set up a separate dataset filter based on the parameter?
Amy.G
Amy.G
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 290
The report data is populated from a stored procedure. But in order to populate the drop down list, I set up another dataset from a query.
 
SELECT DISTINCT cipcode, ciptitle
FROM dbo.q_Outcomes
WHERE LEN(cipcode) = 2
ORDER BY cipcode;


Then set the @cip parameter "Available Values" to pull from this query. This works, but the LIKE part of the query in the original procedure goes away, and only one value is returned.

I hope this answered the question.
geoff5
geoff5
SSC Veteran
SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)

Group: General Forum Members
Points: 244 Visits: 543
Although I cannot tell you why the procedure you are using does not work -- I would have done the same thing more than likely -- I can suggest an alternate way to code the WHERE clause in the stored procedure to eliminate the LIKE comparison from the mix. Try the following:

WHERE LEFT(cipcode, LEN(@cip)) = @cip



Using this code if a person chooses a two-digit option, it will match the left two digits of the cipcode column to the @cip variable. If they select a four-digit code, it will match the left four digits, and so forth. (I know you are only providing two-digit options in the report parameter, but this approach will allow for other options should you want to allow longer parts of the code to match.)

This approach should be functionally identical to the LIKE comparison you used, but perhaps it will work better in this context.
Amy.G
Amy.G
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 290
Geoff,

To be perfectly honest, I'm not really sure why you're solution worked, but it did. Wait, I think I got it, but I don't know if I'd ever have come up with that. Very clever. Hopefully I'll remember this. Thank you so much!

Amy
wolfkillj
wolfkillj
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1454 Visits: 2582
geoff5 (9/27/2013)
Although I cannot tell you why the procedure you are using does not work -- I would have done the same thing more than likely -- I can suggest an alternate way to code the WHERE clause in the stored procedure to eliminate the LIKE comparison from the mix. Try the following:

WHERE LEFT(cipcode, LEN(@cip)) = @cip



Using this code if a person chooses a two-digit option, it will match the left two digits of the cipcode column to the @cip variable. If they select a four-digit code, it will match the left four digits, and so forth. (I know you are only providing two-digit options in the report parameter, but this approach will allow for other options should you want to allow longer parts of the code to match.)

This approach should be functionally identical to the LIKE comparison you used, but perhaps it will work better in this context.


This code will have a negative effect on performance if the OP's table is very large. Typically, a query like this over a large table will be supported by an index on [cipcode], but wrapping the column [cipcode] in the LEFT() function in a WHERE clause will prevent SQL Server from performing a seek on this index. Instead, it will have to scan the entire index to evaluate the result of the LEFT() function for every row to filter out those that don't satisfy the WHERE condition. This may not be a big deal of the OP's table is not very large (SQL Server will opt to scan small tables even when it could perform a seek because scans can be less expensive than seeks on small tables), but scanning a larger table every time the query runs could be disastrous.

Back to the original problem, is it possible that the [cipcode] values you select to populate the parameter drop-down list have trailing spaces, Amy.G? This is very likely if the [cipcode] column in the database is a char() type with a length greater than 2. For example, if [cipcode] is a char(5) type, the value '51' will be padded with 3 trailing blanks. The LEN function still returns 2 for the value '51 ', but the expression @cip + '%' in your WHERE clause would evaluate as '51 ' + '%' (i.e., those blanks would be included in the resulting string) and the LIKE comparison would fail for values such as '51542' and '5154'. The LIKE comparison still works for '51 ', which is why you get that row only. The same thing will happen if the parameter @cip is specified as a char() type with length greater than 2. Geoff5's code works because the LEFT strips off the trailing blanks from [cipcode], with the major drawback identified above, and the equality operator disregards any trailing blanks or spaces in @cip.

Try this instead:
WHERE cipcode LIKE RTRIM(@cip) + '%'



RTRIM strips those troublesome trailing blanks from @cip (any trailing blanks in [cipcode] don't matter because they will not affect the outcome of the LIKE comparison) and applying it to the variable rather than the column value avoids the problem explained above.

Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search