0

My data set has the following where clause:

WHERE a11.PDPOID LIKE '%' + @POID + '%'

My parameter @POID properties are set to single value. The report works, but if I change the parameter properties to allow multiple values then I get an error:

Query execution failed for dataset 'DataSetDtRcvd'. incorrect syntax near ','.

A screen shot of the report's parameter properties

enter image description here

enter image description here

  • Have you seen this question on Stack Overflow? It looks like the values for the parameter are passed as a comma-delimited list, which will break if you splice it into a LIKE predicate. – Jon of All Trades Aug 22 '19 at 18:59
  • It seems like you are going to provide a list of values then why do you use LIKE '%' + @POID + '%' ? For the list of values you need to do WHERE a11.PDPOID IN (@POID). – Alexander Suprun Aug 22 '19 at 19:25
1

You're running into this problem because of the difference in how SSRS passes multi-value parameters when using a text query vs. using a stored procedure for your dataset.

When using a text query, the parameter is actually replaced with a literal list of strings before executing the query, e.g. PDPOID IN (@PDPOID) is translated to PDPOID IN ('aaa', 'bbb', 'ccc') (assuming you've selected 'aaa', 'bbb', and 'ccc').

For a stored procedure, the selected values are turned into a comma-separated list, and passed as a single string/character parameter. So if your stored procedure has a parameter named @PDPOID, then the parameter would be set to the single value 'aaa,bbb,ccc'.

The easiest way to make this work is to change your report to use a stored procedure for the query, then use the STRING_SPLIT function (or a suitable homebrew string splitting function if you're running a version older than SQL Server 2016). For example:

CREATE PROCEDURE POReport
    @POID varchar(max)
AS

SELECT *
FROM POs a11
WHERE EXISTS (SELECT p.value FROM STRING_SPLIT(@POID,',') p WHERE a11.PDPOID LIKE '%' + p.value + '%')
1

You could achieve this by changing your dataset query to an expression and use the Join operator on the multi-valued parameter, for example, your expression would look like

= "Select * " &
  "from table t " &
  "where a11.PDPOID LIKE '%" & Join(Parameters!TestParm.Label, "%' OR a11.PDPOID LIKE 
  '%") & "%'"

Replace the Select * from table t with your query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.