CASE with different datatype

[simonwraight]

I have a dynamic search that will search by selected column but one column is int and I cant seem to get it working.I've read you need to seperate the datatypes but am still having difficulty.

This is what I have but it throws an error. If I use only one datatype it works.

CASE --varchar
WHEN @searchFor = 'SMB' THEN SMB
WHEN @searchFor = 'Location' THEN Location
END,
CASE --INT
WHEN @searchFor = 'IIN' THEN IIN
END
LIKE '%'+@searchStr+'%'

[simonwraight]

Ok I have cheated by casting the IIN as a varchar. This will work because it is not ordering but searching.
I would still be interested in a better solution if anyone can help.

[Old Pedant]

Well, first of all, you should use the alternate form of CASE:

CASE @searchFor
WHEN 'SMB' Then SMB
WHEN 'Location' Then Location
WHEN 'IIN' Then CONVERT( VARCHAR(20), IIN )
ELSE @searchStr
END
LIKE '%'+@searchStr+'%'

More like switch() in C/C++/Java/JavaScript coding. A bit more efficient.

But, really, isn't it a bit meaningless to search for a *STRING* in a INT field??

I mean, imagine that your IIN field has values such as 187, 9118, 2081 and the user asks to search for "8". Do you really want hits on all those integer values???

It's hard to know what the right answer is unless you can come up with a reasonable way of searching your IIN integer field. Would it be better to allow a search by bounds? That is, where IIN is BETWEEN a pair of entered values?

So maybe the convert to varchar is the best alternative until you come up with a better overall search scheme?