October 10, 2006 at 8:32 am
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
October 10, 2006 at 8:45 am
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')
October 10, 2006 at 9:25 am
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?
October 10, 2006 at 9:40 am
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)
October 10, 2006 at 10:55 am
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