Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Nov 2007
    Posts
    40
    Thanks
    9
    Thanked 0 Times in 0 Posts

    CASE with different datatype

    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.

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

  • #2
    New Coder
    Join Date
    Nov 2007
    Posts
    40
    Thanks
    9
    Thanked 0 Times in 0 Posts
    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.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,596
    Thanks
    80
    Thanked 4,630 Times in 4,593 Posts
    Well, first of all, you should use the alternate form of CASE:
    Code:
    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?


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •