Report Parameters Multi-Value and Stored Procedures

  • How can I pass a list of multi-values from a report parameter into a stored procedure and the data type still be an int? When multiple values are selected it becomes a string of 1,6,21...etc, correct? In the stored procedure this string of values need to be in the datatype of int.

    I am using the IN keyword in the where clause:

    ANDc.location in (@LocationKey) <-- this needs to be an int however muti-values are coming in as varchar

    @LocationKey is set as int datatype in the stored procedure

    I keep getting the following error due to the data types being different.

    Error converting data type varchar to int.

    There must be something simple that I have overlooked. Can anyone provide some insight?

    Thanks,

    Brian

  • In my experience the best way to handle this is to "split" the delimited list into a table and join to it. Check out this article[/url] on the most efficient way to do this.

  • I 2nd Jack's idea. The last time, I had similar need involving multi-valued parameter, I ended up going the subreport route. Anyways, good luck.

  • Thank you Jack for the helpful information. I created a function to split out the parameter data and this seems to best solution for what I need to accomplish.

  • Hello,

    Try this

    CREATE TABLE [dbo].[Test]

    (

    ID INT,

    NAME NVARCHAR(50)

    )

    INSERT INTO [dbo].[Test] VALUES (1,'Sample1')

    INSERT INTO [dbo].[Test] VALUES (2,'Sample2')

    INSERT INTO [dbo].[Test] VALUES (3,'Sample3')

    INSERT INTO [dbo].[Test] VALUES (4,'Sample4')

    CREATE PROCEDURE [dbo].[Sample]

    (

    @ID NVARCHAR(50)

    )

    BEGIN

    DECLARE @IDSTR NVARCHAR(100)

    SET @ID = '1,2'

    SET @IDSTR = CHAR(39) + REPLACE(@ID,',',CHAR(39) + ',' + CHAR(39)) + CHAR(39)

    SELECT ID

    ,NAME

    FROM [dbo].[Test]

    WHERE ','+ @IDSTR + ',' LIKE '%,' + CHAR(39)+ CAST(ID AS NVARCHAR) + CHAR(39)+',%'

    END

    Hope helpful...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply