Why is my variable getting converted

  • I have the following query:

    Declare @Memberid as varchar (50)

    Declare @ProcCode as Varchar (15)

    set @memberid = -1

    Set @ProcCode = 'G0202'

    Select top 100 * from [QICC-TEST].dbo.tblClaims_eligible

    where Membid = COALESCE(NULLIF( @memberid,-1), Membid)

    and ProcCode = COALESCE(NULLIF( @ProcCode,-1), ProcCode)

    When I pass in a ProcCode that does not contain a letter, the query works beautifully, when I run the query using a parameter that does contain a character, I get the following error:

    Server: Msg 245, Level 16, State 1, Line 5

    Syntax error converting the varchar value 'G0202' to a column of data type int.

    In the original table, the data type for this column is Varchar (15), so I tried the following code to fix the problem:

    Declare @Memberid as varchar (50)

    Declare @ProcCode as Varchar (15)

    set @memberid = -1

    Set @ProcCode = 'G0202'

    Select top 100 * from [QICC-TEST].dbo.tblClaims_eligible

    where Membid = COALESCE(NULLIF( @memberid,-1), Membid)

    and ProcCode = COALESCE(NULLIF( @ProcCode,-1), cast(ProcCode as varchar(15)))

    I get the same error message, any suggestions?

    Thank you,

    Richard

  • You are comparing two different datatypes with NULLIF( @memberid,-1). As INT has a higher precedence than VARCHAR, the VARCHAR will be implicitly converted to an INT before the comparison is done.

    You can get around this by using NULLIF( @memberid, '-1')

     

  • Not sure what you mean, I have tried the following:

    Declare @Memberid as varchar (50)

    Declare @ProcCode as Varchar (15)

    set @memberid = -1

    Set @ProcCode = 'G0202'

    Select top 100 * from [QICC-TEST].dbo.tblClaims_eligible

    where Membid = COALESCE(NULLIF( @memberid,-1), Membid)

    and ProcCode = (NULLIF( @ProcCode,-1))

    Still erroring out, what am I missing?

  • Declare @Memberid as varchar (50)

    Declare @ProcCode as Varchar (15)

    set @memberid = '-1'

    Set @ProcCode = 'G0202'

    Select top 100 * from [QICC-TEST].dbo.tblClaims_eligible

    where Membid = COALESCE(NULLIF( @memberid, '-1'), Membid)

    and ProcCode = COALESCE(NULLIF( @ProcCode, '-1'), ProcCode)

     

  • I got it now, thank you for your help.

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

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