none
error converting data type nvarchar to int RRS feed

  • Question

  • Hi here is the below stored procedure and when i pass the single value to the @getValue parameter it is not giving any error but when i select multiple values it is resulting the error as it is showing in the title

    ALTER PROCEDURE [dbo].[spGetValues]
    (
      @getValue int = 100
     ,@userId     varchar(200) = null 
    )
    AS

    declare @runTime                 as datetime = getutcdate()
    declare @rowCnt                   as int

    select
       csk    
      , ccode 
      , cdesc  
    from
     dbo.code
    where
      ctype =  'status_csk'
      and csortnum >= case  @getValue
                           when 100 then 0
                           when 101 then 10000
                           when 102 then 100000
                         end 
      and csortnum <  case  @getValue
                           when 100 then 10000
                           when 101 then 100000
                           when 102 then 200000
                         end 
    order by
      csortnum

    --=======================================================
    --=======================================================
    set @rowCnt = @@ROWCOUNT  
    insert into dbo.report
    (
      rname
    , rincrement
    , rdate
    , ruserid
    , rurl
    , rsecs
    , rscnt
    , rparm1
    , rparm2
    )
    values
    (
      OBJECT_NAME(@@PROCID)
    , 0
    , GETUTCDATE()
    , SUSER_NAME()
    , null
    , DATEDIFF(S,@runTime  ,GETUTCDATE())       
    , @rowCnt
    , 'RptUser:' + isnull(@userId , '')
    , 'TcType: ' + cast(@getValue as varchar(6))
    )

    so if anyone knows what's the problem please let me know.

    Tuesday, October 15, 2019 6:04 AM

All replies

  • How are you passing multiple values to @getValue parameter. Please give example.


    Regards,
    Vaibhav

    Tuesday, October 15, 2019 6:35 AM
  • Hi actually i'm new to this one and i think the values are coming from rparam2 in dbo.report.
    Tuesday, October 15, 2019 6:44 AM
  • Hi actually i'm new to this one and i think the values are coming from rparam2 in dbo.report.

    Based on your code, I guess @getValue should have only integer value and then it should work.

    You have a case statment which checks if the value is 100 or 101 or 102 and also you are inserting the passed integer value in rparam2 column of dbo.report table. 

    If you pass multiple values something like this for example, it will fail with data type conversion error. 

    declare @getValue int = '100, 200'
    
    select  case  @getValue
                           when 100 then 0
                           when 101 then 10000
                           when 102 then 100000
                         end 
    
    -- Error: Conversion failed when converting the varchar value '100, 200' to data type int.
    You can provide the script that you are running and that gives the error.


    Regards,
    Vaibhav

    Tuesday, October 15, 2019 7:26 AM
  • But as i check the table dbo.report the rparam2 is of varchar data type and again in the inserting values statement we are casting it. is that the problem for that?
    Tuesday, October 15, 2019 7:31 AM
  • No, not a problem. @getValue is INT and while inserting data to target column which is varchar, we need to cast which is correct. 

    You may be passing incorrect values or multiple values to SP, that might be the issue.

    How are you running the procedure? Can you share the code.


    Regards,
    Vaibhav

    Tuesday, October 15, 2019 7:41 AM
  • This should answer your question and provide the solution

    http://www.enabledbusinesssolutions.com/blogs/tales-from-the-crypt-passing-multiple-int-values-into-a-variable/

    Modify as needed.


    Jayanth Kurup - www.enabledbusiness.com - If the post was helpful or answered your query please press the "Vote as helpful" or "Propose/Mark as Answer" within the Post. - All we can try and do is be polite and helpful.

    Tuesday, October 15, 2019 8:12 AM
  • Yeah when you pass the single value it runs fine but pass '101,102' you will get error same like if you pass in report it will give an error there.
    Tuesday, October 15, 2019 9:06 AM
  • Hi that's needed but where exactly i need to modify after looking into my sp?
    Tuesday, October 15, 2019 9:44 AM
  • You can not pass a multi value parameter to a stored procedure, in a directly way it is not possible, independend of the used data type.

    You can only solve it by concat the parameter in SSRS, pass it as one string to the SP and there you have to work with dynamic SQL; not a nice solution.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, October 15, 2019 10:01 AM
    Moderator
  • As i'm passing the @getValue parameter from sp to SSRS not from SSRS to sp
    Tuesday, October 15, 2019 10:09 AM
  • Hi VinaySJ,

     

    Would you please refer to https://www.telerik.com/forums/how-to-pass-multi-value-parameters-to-a-stored-procedure-datasource?

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, October 16, 2019 9:15 AM