none
SSRS Multi parameter - Integer type RRS feed

  • Question

  • Hi

    I'm using SSRS 2008 R2, ONe of the input parameters I'm passing is Multi value input parameter.

    On db end my stored proc has input param wiht similar name and data type as varchar, while the column as int types

    e.g.

    Declare @MultiParam varchar(1000) ='1010,102,125'

    Column type being integer is creating problem.

    For this I tried couple of approaches but couldn't get around

    - I get this error

    Arithmetic overflow error converting varchar to data type numeric.

    sample code

    declare @PnNo varchar(2000) = '1010,102,125'
    select @pnNo= SUBSTRING(@PnNo,1,LEN(@PnNo)-1)
    select @PnNo

    select * from F4801_World
    where intColumn IN (@PnNo)

    Monday, September 7, 2015 4:57 PM

Answers

  • If you need to use a stored procedure, then you would need to use a split function. See samples in this thread

    Once you have a splitter function, your query will become:

    select * from F4801_Word where intColumn in (select cast(Item as Int) from dbo.fnSplit(@pnNo, ','))

     

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Proposed as answer by Jason A Long Monday, September 7, 2015 7:12 PM
    • Marked as answer by StSingh Monday, September 7, 2015 8:12 PM
    Monday, September 7, 2015 6:12 PM
    Moderator