where case 문을 이용한 조건 검색 쿼리문

/*=============================================================================================

-- Author : 장정훈

-- Description : 상품 목록을 가져온다.

-- Note :

-- Modify Log: :

-- Test :

exec QfsInventoryList_james 

'100054651'

, '0000019358'

, ''

, ''

, ''

, N''

============================================================================================= */

alter procedure dbo.QfsInventoryList_james

@customerNo varchar(10)

, @placeCode varchar(10)

, @searchStartDate char(8)

, @searchEndDate char(8)

, @searchOption nvarchar(50)

, @searchOptionDetail nvarchar(5)

as

begin

set nocount on

set transaction isolation level read uncommitted

declare @inventorySum table (

inv_no int

, qty int

)

insert into @inventorySum (inv_no, qty)

select inventory.inv_no, isnull(SUM(branchQty.qty), 0) as sumQty

from f_inventory inventory with (nolock, readuncommitted)

left outer join f_inventory_option inventoryOption

on inventory.inv_no = inventoryOption.inv_no

left outer join t_branch_qty branchQty 

on inventoryOption.sku_no = branchQty.sku_no

where inventory.cust_no = @customerNo

and inventory.pl_cd = @placeCode

and inventory.use_yn = 'Y'

group by inventory.inv_no

select inventory.cust_no

, inventory.pl_cd

, inventory.inv_no

, inventory.inv_code

, inventory.inv_name

, inventory.use_yn

, inventory.reg_dt

, inventorysum.qty

from f_inventory inventory with(nolock, readuncommitted)

left outer join @inventorySum inventorySum

on inventory.inv_no = inventorySum.inv_no

where

CASE WHEN @searchOption = 'inventoryname' THEN inventory.inv_name ELSE '' END

like CASE WHEN @searchOption = 'inventoryname' THEN '%'+@searchOptionDetail+'%' ELSE '' END

and CASE WHEN @searchOption = 'inventorycode' THEN inventory.inv_code ELSE '' END 

like CASE WHEN @searchOption = 'inventorycode' THEN '%'+@searchOptionDetail+'%' ELSE '' END

and CASE WHEN @searchStartDate <> '' THEN CONVERT(int, CONVERT(char(8), inventory.reg_dt, 112)) ELSE '' END 

between 

CASE WHEN (@searchStartDate <> '' and @searchEndDate <> '') THEN @searchStartDate ELSE '' END 

and 

CASE WHEN (@searchStartDate <> '' and @searchEndDate <> '') THEN @searchEndDate ELSE '' END

and inventory.cust_no = @customerNo

and inventory.pl_cd = @placeCode

and inventory.use_yn = 'Y'

end