Search code examples
reporting-servicesparameterssql-like

SSRS query using like with parameter won't return anything if full value is supplied with parameter

I'm building a report where my user will need to supply EITHER the full a serial number or the last five characters of the serial number.

The WHERE clause of my query in SSRS is:

Where (Products.SerialNumber LIKE '%' + @prmSerialNumber)

I have a serial number in the database equal to '1C0127000000000000009132'.

If I supply '9132' as the parameter value, I get the appropriate results. But if I supply the whole serial number '1C0127000000000000009132' as the parameter value, I get no rows returned.

Is there a way to build this query so that the user can supply either the full serial number OR just the last few characters?

I have also tried: WHERE (Products.SerialNumber LIKE { fn CONCAT(N'%', @prmSerialNumber) }).

Same results

Solution

  • You could just use an OR

    WHERE (Products.SerialNumber LIKE '%' + @prmSerialNumber) 
OR (Products.SerialNumber = @prmSerialNumber)