SQL Query WHERE equals int or varchar without breaking?

Question

There is an instance where the filters I have are being dynamically plugged into my SQL query and sometimes cannot tell prior if they are int or varchar. Is there a way to allow either with a CASE WHEN statement?

In my table (ent for entity), the column company is a string/varchar and the entid column is an int. I cannot seem to get this CASE IsNumeric statement to work for cases when an int is the filter, or the string is the filter.

This works:

select 
    ent.company 
from 
    ent 
where 
    ent.company in
         (CASE 
             WHEN IsNumeric(CONVERT(VARCHAR(100), 'ABIS, Inc.')) = 0 
               THEN CONVERT(VARCHAR(100), 'ABIS, Inc.') 
               ELSE '99' 
          END,
          CASE 
             WHEN IsNumeric(CONVERT(VARCHAR(100), 'Schulte Building Systems')) = 0 
               THEN CONVERT(VARCHAR(100), 'Schulte Building Systems') 
               ELSE '1423' 
          END)

This is not working:

select 
    ent.company 
from 
    ent 
where 
    ent.entid in 
        (CASE 
            WHEN IsNumeric(CONVERT(VARCHAR(100), 'ABIS, Inc.')) = 0 
              THEN CONVERT(VARCHAR(100), 'ABIS, Inc.') 
              ELSE '99' 
         END,
         CASE 
            WHEN IsNumeric(CONVERT(VARCHAR(100), 'Schulte Building Systems')) = 0 
              THEN CONVERT(VARCHAR(100), 'Schulte Building Systems') 
              ELSE '1423' 
         END)

...but for the int equals if I change the 0's to 1's it works (and if I use company equals, it returns nothing). Any idea why it's failing?

Show execution plan for both, I assume there is cast before `CASE` take place. — Lukasz Szozda, Sep 09 '15 at 15:52
I mean [this](http://sqlblog.com/blogs/jonathan_kehayias/image_6E693012.png), not your queries but how query optimizer will execute them — Lukasz Szozda, Sep 09 '15 at 15:56
Never mind, in second query in CASE you return `NVARCHAR` and you compare with `INT` which cause this error. — Lukasz Szozda, Sep 09 '15 at 16:02
In your case statement return always numbers `WHEN IsNumeric(CONVERT(VARCHAR(100), 'ABIS, Inc.')) = 0 THEN **CONVERT(VARCHAR(100), 'ABIS, Inc.')** - change to INT id not string ELSE '99' ` — Lukasz Szozda, Sep 09 '15 at 16:08

score 0 · Answer 1 · answered Sep 09 '15 at 16:14

Solved it. I was never asking about the actual column - just the filter.

select ent.entid, ent.company from ent where 

ent.entid in
(
    CASE IsNumeric(entid) WHEN 1 then '99' else 'ABIS, Inc.' End,
    CASE IsNumeric(entid) WHEN 1 then '1423' else 'Schulte Building Systems' End    
)

and

select ent.entid, ent.company from ent where 

ent.company in
(
    CASE IsNumeric(company) WHEN 1 then '99' else 'ABIS, Inc.' End,
    CASE IsNumeric(company) WHEN 1 then '1423' else 'Schulte Building Systems' End  
)

both work.

SQL Query WHERE equals int or varchar without breaking?

1 Answers1