Case When在Where可变查询条件中的应用

/*根据flag的标识值, 用不同的where条件进行查询订单列表*/

CREATE PROCEDURE  [dbo].[usp_GetOrderList]

@orderNo nvarchar(20),

@flag int

as

SELECT a.OrderId,a.OrderNo,a.OrderDate,a.CustomerId,b.CustomerName

FROM Orders a

INNER JOIN Customers b on b.CustomerId = a.CustomerId

WHERE (  CASE

                   WHEN @flag =1 and a.OrderNo like '%' + @orderNo +'%'  then  'ok'

                   WHEN @flag=2 and a.OrderNo like '_' + @orderNo + '%'  then  'ok'

           ) ='ok'

                   

注意: case   when   只能返回值,不能返一个true   /   false   

        你也可以用exec(@str)的方法来,对@str进行select where可变条件的组合.

       如:  exec('select a,b,c from table1 where x=z ...')