Using Parameters in SSRS with LIKE operator

Question

I am trying to create a report that has a list of various product groups as parameters. When selecting each group, a list of those related products should appear. I am currently getting a blank table.

The results for this product group display when i execute the query.

I have the list of parameters and values as follows:

i'm struggling to see where I have gone wrong, when i preview the report, I can select each parameter, but get an empty table in the report. I expect the way I have given each parameter a value may be the problem, but cannot figure it out.

This is the query in the report.

DECLARE @ProductCode VARCHAR(12)

SELECT STRC_CODE, STRC_DESC FROM DeFactoUser.F_ST_Products
WHERE STRC_CODE LIKE @ProductCode

I am using Visual Studio Data Tools 2012 to build the report.

If anyone could give me a clue, that would be greatly appreciated.

Are you sure you need to surround your parameter values with single quotes? — DavidG
It doesn't work without the quotes. I thought the quotes and wildcard would be necessary to allow the LIKE operator to work. — R_Avery_17
It seems that you are not mapping the parameter but declaring it on the SQL — Jayvee
remove the DECLARE @ProductCode VARCHAR(12) from your report query. When you put @ in front of a word, SSRS will automatically treat it as a parameter, no need to declare it — JamieD77

sepupic sepupic · Accepted Answer · 2017-09-18T14:18:23

It doesn't work without the quotes. I thought the quotes and wildcard would be necessary to allow the LIKE operator to work.

First of all you don't need to use quotes, I tried to reproduce it right now and it works without quotes:

Second, the query that SSRS sends to server looks like this:

exec sp_executesql N'select model
from [dbo].[cars]
where model like @car',N'@car nvarchar(3)',@car=N'ca%'

So the only thing it changes respect to your code is parameter type, it always declares it as unicode, i.e. nvarchar(...), so maybe it make sense to try in SSMS your code with parameter type changed to nvarchar(12):

declare ProductCode nvarchar(5) = N'PA.A%'

Try to execute it in SSMS and see if it returns smth or not

Using Parameters in SSRS with LIKE operator

1 Answers