日期:2014-05-17 浏览次数:20484 次
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&nb