Search code examples
sql-server-2008stored-procedurescasecase-sensitivecollate

Case and COLLATE SQL_Latin1_General_CP1_CI_AS in where statement

I am working on a Stored Procedure that has two input parameters that I need to use COLLATE SQL_Latin1_General_CP1_CI_AS so that I can query non-case sensitive results; however, I would also like to allow for a Show All Option with the inputs. I have not found a way for the Collate to work in a case statement. Note: As a work around I will code with 4 different If statements for the different scenarios of choosing a value and/or show all, but would like something cleaner if possible. Current Code below

Declare 
@INCo as bCompany,
@MatBeg as bMatl,
@MatEnd as bMatl,
@Catg as bGroup,
@Model as VarChar(20),
@PatternM as VarChar(20),
@SellOn as VarChar(20),
@PatternS as VarChar(20),
@ShowZero as bYN

set @INCo = '1'
set @MatBeg = '0'
set @MatEnd = 'ZZZZZZZZZZ'
set @Catg = '0'
set @Model = '637'
set @SellOn = 'EBAY'
set @ShowZero = 'Y' 


begin
set @PatternM = '%' + @Model + '%';
set @PatternS = '%' + @SellOn + '%';

select i.INCo, i.Material, h.Description, h.Category, c.Description as CatgDesc, i.Booked as Quantity, i.PhyLoc, p.Storage, 
    i.udModelFit as FitsModel, i.udSellPriceNew as LikeNewSellPrice, i.udSellPriceUsed as UsedSellPrice, i.udSellingOn as SellingOn

from INMT i
    left outer join HQMT h on i.MatlGroup=h.MatlGroup and i.Material=h.Material
    left outer join HQMC c on h.MatlGroup=c.MatlGroup and h.Category=c.Category
    left outer join udPhysicalloc p on i.PhyLoc=p.Physicalloc

where i.INCo = (CASE when @INCo <> 0 then @INCo else i.INCo END)
    and i.Material >= @MatBeg and i.Material <= @MatEnd
    and c.Category = (CASE when @Catg <> 0 then @Catg else c.Category END)
    and i.udModelFit COLLATE SQL_Latin1_General_CP1_CI_AS like @PatternM
    and i.udSellingOn COLLATE SQL_Latin1_General_CP1_CI_AS like @PatternS
    and i.Booked >= (CASE when @ShowZero = 'N' then 1 else 0 END)
END

This code works works great if I only care about having non-case sensitive results for @Model and @SellOn. However, like the other parameters I have, I would like to include something that allows to show all results for that parameter. Something like:

i.udModelFit = (CASE when @Model <> 'ALL' then COLLATE SQL_Latin1_General_CP1_CI_AS like @PatternM else i.udModelFit END)

So as an example I would like to input @Model = 'ALL' and @SellOn = 'eBay' and results would be any Model with Sell On = EBAY (non-case sensitive)

Update: I was able to modify Where to the following and I am getting the desired results now.

where i.INCo = (CASE when @INCo <> 0 then @INCo else i.INCo END)
    and i.Material >= @MatBeg and i.Material <= @MatEnd
    and c.Category = (CASE when @Catg <> 0 then @Catg else c.Category END)
    and (
        (@Model IS NULL or i.udModelFit COLLATE SQL_Latin1_General_CP1_CI_AS like @PatternM) 
        or ((i.udModelFit like '%' or i.udModelFit IS NULL) and @Model = 'ALL')
        )
    and (
        (@SellOn IS NULL or i.udSellingOn COLLATE SQL_Latin1_General_CP1_CI_AS like @PatternS)
        or ((i.udSellingOn like '%' or i.udSellingOn IS NULL) and @SellOn = 'ALL')
        )
    and i.Booked >= (CASE when @ShowZero = 'N' then 1 else 0 END)
Solution

  • You can use and( (...) or (...) ) e.g.

    and (@Model = 'ALL' or i.udModelFit like @PatternM SQL_Latin1_General_CP1_CI_AS)

    If you want to toggle different search options based on input, you may want to consider writing your procedure using dynamic sql, and/or adding option (recompile).

    Catch-all query reference: