Problem with multi-selection parameters. 1

[Peager]

I have a R/S RDL where I want to use a multi-selection list on a parameter to allow the end user to select multiple divisions. I tested it initially with an SQL script dataset that looked something like this:

Select * from some_table where Div in @DivList

Worked slick as snot on a brass door knob.

Then, continuing the development environment, I move my select logic into a stored procedure something like this:

Create PROCEDURE [dbo].[GLWorkingTrialBalance] 
    @DivList VarChar(30), 
        :
        :
Select * from Some_File where Div in (@DivList)

If I run the report with only one division selected all works well but when I select more than one, I get an error:

Query execution failed for data set 'My_Dataset'
Error converting data type varchar to numeric

My best guess is that I need to change something on the SQL Procedure but as to what, I don't know.

Any input would be much appreciated.

Paul

 

[xlbo]

What data type is the parameter set to in RS ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Peager]

I have tried it with both String and Integer. Both with the same results. The code is actually a 2 digit integer.

Paul

 

[Peager]

OK. I have finally found a solution.... The problem was that the stored procedure was not able to evaluate a string passed in a parameter.

The key to the solution is the following (which I stole from:

http://en.csharp-online.net/Building_Reports_in_SQL_Server_2005—Working_with_Multivalued_Parameters

)

CREATE FUNCTION [dbo].[Util_ConvStrToTable](@RepParam NVARCHAR(4000), @Delim CHAR(1)= ',')
RETURNS @VALUES TABLE (Param NVARCHAR(30))
AS
BEGIN
   DECLARE @chrind INT
   DECLARE @Piece NVARCHAR(4000)
   SELECT @chrind = 1
   WHILE @chrind > 0
      BEGIN
         SELECT @chrind = CHARINDEX(@Delim,@RepParam)
         IF @chrind > 0
            SELECT @Piece = LEFT(@RepParam,@chrind - 1)
         ELSE
            SELECT @Piece = @RepParam
         INSERT @VALUES(Param) VALUES(@Piece)
         SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
         IF LEN(@RepParam) = 0 BREAK
      END
   RETURN
END

This function takes a string and returns it as an in-memory table.... slick stuff.

Then, in your own procedure the 'IN' clause looks like this:

Create PROCEDURE [dbo].[GLWorkingTrialBalance] 
    @DivList VARCHAR(4000),     -- = '4,10,62,63',
         :
    SELECT DISTINCT Div, Dpt, Acct, Sub
    FROM Journal 
    WHERE Div IN (SELECT Param FROM dbo.Util_ConvStrToTable(@DivList,','))

Pretty elegant solution with much thanks to the MSDN site.

Paul

 

[xlbo]

Thanks for posting the solution back

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question