超经典的多条件查询

solution 1 the drawback is performance is slow

 

use testdb;
go
create proc dbo.usp_GetOrders
@OrderID as int=null,
@CustomerID as nchar(5)=null,
@EmployeeID as int=null,
@OrderDate as DateTime=null
with recompile
as
select OrderID,CustomerID,EmployeeID,OrderDate,filler
from dbo.orders
where (OrderID=@OrderID or @OrderID is null)
and (CustomerID=@CustomerID or @CustomerID is null)
and (EmployeeID=@EmployeeID or @EmployeeID is null)
and (OrderDate=@OrderDate or @OrderDate is null)
go

 

 

 

solution 2

--if you want this pro work well always,you must guarantee all column is not null
--when you use the coalesce function please place the input parameter as the first parameter
--drawback in performance too
ALTER PROC dbo.usp_GetOrders
@OrderID AS INT = NULL,
@CustomerID AS NCHAR(5) = NULL,
@EmployeeID AS INT = NULL,
@OrderDate AS DATETIME = NULL
WITH RECOMPILE
AS

SELECT OrderID, CustomerID, EmployeeID, OrderDate, filler
FROM dbo.Orders
WHERE OrderID = COALESCE(@OrderID, OrderID)
AND CustomerID = COALESCE(@CustomerID, CustomerID)
AND EmployeeID = COALESCE(@EmployeeID, EmployeeID)
AND OrderDate = COALESCE(@OrderDate, OrderDate);
GO

 

 

solution 3

--in 'case when' structure,must base on 'is not null' logic to around the limitation of all columns must not be null
--advantage :1 the column can be null 2good performance
alter PROC dbo.usp_GetOrders
@OrderID as int=null,
@CustomerID as nvarchar(5)=null,
@EmployeeID as int=null,
@OrderDate as datetime=null
as
declare @sql as nvarchar(4000);
set @sql=
N'select orderid,customerid,employeeid,orderdate,filler'
+N' from dbo.orders '
+N' where 1=1 '
+case when @OrderID is not null then N' and OrderID=@oid'
else N'' end
+case when @CustomerID is not null then N' and CustomerID=@cid '
else N'' end
+case when @EmployeeID is not null then N' and EmployeeID=@eid'
else N'' end
+case when @orderdate is not null then N' and OrderDate=@dt'
else N'' end;
exec sp_executesql
@sql,
N'@oid as int,@cid as nchar(5),@eid as int,@dt as datetime',
@oid=@OrderID,
@cid=@CustomerID,
@eid=@EmployeeID,
@dt=@OrderDate;
go