Post reply

LIKE statement with Parameter Problem

  • February 15, 2013 at 1:19 pm

    #270234

    Hi, I am trying to add a LIKE statement to a query in report builder 3.0 with a parameter like the one below. It allows the user to input an ID and returns records that have that id in their list of IDs by using the like statement with wildcards.

    SELECT

    CORRIDOR.OBJECTID

    ,CORRIDOR.STATUS

    ,CORRIDOR.DIMENSION

    ,CORRIDOR.WIDTH

    ,CORRIDOR.ID

    FROM

    CORRIDOR

    WHERE ID LIKE '%' + @ID + '%'

    I get an error "Conversion Failed when Converting the varchar value '%' to data type int"

    I am not sure why the wildcard is converting to int. my @ID parameter is text. Please help. Thanks!

  • February 18, 2013 at 3:51 am

    #1588774

    what is the datatype of the column 'ID' in the database table ?

    BI Developer
    SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
    Please visit... ApplyBI

  • February 18, 2013 at 6:52 am

    #1588825

    nvarchar(250)

  • February 18, 2013 at 10:40 pm

    #1589034

    This is the exact error you would get if the parameter's datatype is Integer. Double check that.

    Another thing you can try is using

    like '%' + Cast(@ID as nvarchar(20)) + '%'

    instead of

    like '%' + @ID + '%'

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

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