存储过程允许传入空值的问题。
本帖最后由 wuchaochang 于 2013-06-19 16:27:19 编辑
CREATE PROC [dbo].[P_Condition_Search]
(
@CustStoreID nvarchar(max),
@CustCustomerName nvarchar(max),
@CustCustomerID nvarchar(max),
@CustCity nvarchar(max),
@CustState nvarchar(max),
@CustLastPurchase datetime,
@CustTotalPurchase real,
@CustDistance real
)
as
begin
select * from [dbo].[V_Customer_Store] where(
[StoreID] like '%'+@CustStoreID+'%'
and [CustomerID] like '%'+@CustCustomerID+'%'
and [Firstname] like '%'+@CustCustomerName+'%'
or [Lastname] like '%'+@CustCustomerName+'%'
and [City] like '%'+@CustCity+'%'
and [State] like'%'+@CustState+'%'
and [Last_purchase] like @CustLastPurchase
and [Total_Purchase] >= @CustTotalPurchase
and [Disstance] <= @CustDistance)
end
CREATE PROC [dbo].[P_Condition_Search]
(
@CustStoreID nvarchar(max),
@CustCustomerName nvarchar(max),
@CustCustomerID nvarchar(max),
@CustCity nvarchar(max),
@CustState nvarchar(max),
@CustLastPurchase datetime,
@CustTotalPurchase real,
@CustDistance real
)
as
begin
declare @str varchar(max)='';
declare @where varchar(max)='';
set @str ='select * from [dbo].[V_Customer_Store] ';
if( @CustStoreID is not null
or @CustCustomerName is not null
or @CustCustomerID is not null
or @CustCity is not null
or @CustState is not null
or @CustLastPurchase is not null
or @CustTotalPurchase is not null
or @CustDistance is not null
)
begin
set @where=' where '
+case when @CustStoreID is not null then
'[StoreID] like %'+@CustStoreID+'%'
else ''
end
+case when @CustCustomerID is not null and @CustStoreID is not null
then 'and [CustomerID] like %'+@CustCustomerID+'%'
when @CustCustomerID is not null and @CustStoreID is null
then ' [CustomerID] like %'+@CustCustomerID+'%'
else ''
end
+…… --剩下的条件自己代码类似
end
Exec (@str+@where)
