none
Removing Outer Single Quotes for WHERE IN Clause from Join(Parameters)

    Question

  • Hello,

    I am passing a multi-valued parameter into a dataset from a TypeBusiness dropdown list:
    =join(Parameters!prmTypeBusiness.Value,",")

    Here is the dataset query behind the TypeBusiness dropdown parameter list:
    SELECT     type_bus_code, bus_desc
    FROM       business
    WHERE     (type_bus_code IS NOT NULL)

    Here is the WHERE clause from the main dataset query (dsMainData) which gets the parameter values:
    WHERE  WEKAVG.BUSINESS_CODE IN(@prmTypeBusiness) 

    Here is the error I get when selecting values from the TypeBusiness dropdown list then previewing the report:
    "Cannot read the next data row for the dataset dsMainData.
    Conversion failed when converting the nvarchar value '1,2' to data type smallint."

    The value is a small int coming from the dataset query behind the TypeBusiness dropdown list, and the data type in the column expecting this value (in the main dataset query) is also a small int.

    I have tried many things, including a replace in the =join(Parameters!prmTypeBusiness.Value,","), a replace in the WHERE clause, a Split function, CAST to varchar, have researched this online and cannot solve this error.

    If someone could please help me save what little gray hairs I have left I would greatly appreciate any help, suggestion or point in the right direction.

    Thanks,
    Buster
    Friday, March 14, 2014 1:57 AM

All replies

  • You need to parse comma separated values. Kindly replace your WHERE clause with below mentioned code.

    WHERE  WEKAVG.BUSINESS_CODE IN(
    										SELECT 
    												 SplitedValues.Parameter.value('.', 'VARCHAR(2000)') AS Record  
    											FROM  
    											(
    												SELECT CAST ('<P>' + REPLACE(@prmTypeBusiness, ',', '</P><P>') + '</P>' AS XML) AS CVS 
    											) AS XMLData CROSS APPLY CVS.nodes('/P') AS SplitedValues(Parameter)
    							
    							  ) 

    Friday, March 14, 2014 4:53 AM
  • Hi,

    Try like this - http://social.technet.microsoft.com/wiki/contents/articles/19621.ssrs-multi-valued-parameter-as-stored-procedure-input.aspx


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Friday, March 14, 2014 7:35 AM
    Moderator
  • Thank you for the sample, but it returns no records. Is there something I need to change in this snippet you sent?
    Friday, March 14, 2014 4:32 PM
  • Your WHERE clause is fine. The problem is that you are trying to manually do the work that SSRS does for you. Don't do the Join on the multivalue parameter first, just pass it as is. SSRS will convert the array to the proper syntax for you.


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Friday, March 14, 2014 4:52 PM
  • Thank you for the sample, but it returns no records. Is there something I need to change in this snippet you sent?

     DECLARE @prmTypeBusiness VARCHAR(20) = '1,2,4' -- say selected parameter values
     DECLARE @business TABLE (type_bus_code INT, bus_desc VARCHAR(20))
     INSERT @business SELECT 1,'A'
     INSERT @business SELECT 2,'B'
     INSERT @business SELECT 3,'C'
     INSERT @business SELECT 4,'D'
    
    ;WITH CTE1 
    AS
    ( 
    SELECT CAST(('<i><j>' + REPLACE(@prmTypeBusiness, ',',  '</j></i><i><j>') + '</j></i>'  )  AS  XML) CSV 
    ),CTE2 
    AS
    ( 
    SELECT CAST(i.query('./text()') AS   VARCHAR(100))CSV 
    FROM CTE1  
    CROSS APPLY CSV.nodes('/i/j') As   x(i) 
    ) 
    SELECT type_bus_code,
           bus_desc
    FROM   @business
    WHERE  type_bus_code IN (SELECT * FROM CTE2) 
    


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Sunday, March 16, 2014 3:31 AM
    Moderator
  • Try :

    WHERE  Convert(Nvarchar(100), WEKAVG.BUSINESS_CODE) IN(@prmTypeBusiness) 

    Instead

    WHERE  WEKAVG.BUSINESS_CODE IN(@prmTypeBusiness) 

    Sunday, March 16, 2014 3:43 AM