页面的一个搜索的功能,条件有:手机,姓名,email ,一次只能通过一种条件来搜索。
这三个都是订单表的字段,
下面是个通过手机号来搜索订单的存储过程,通常情况下我要写三个存储过程来对应每一种条件搜索!
但是这样很不好,代码写多了没意思!
我现在要三个存储过程合为一,应该怎么呢?
Create proc Pro_Fujet_Orders_SearchByTel
@index int,
@size int,
@tel varchar(11),
@TotalRecord int output,
@PageRecord int output
as
begin
select top(@size) * from
(
select
Row_Number() over( order by CreateOn desc) as RowNumber,
OrderNum,
OrdersId,
payDate,
Paymoney,
[status]
from orders
where tel=@tel
) as a
where a.RowNumber>(@index-1)*@sizeselect @TotalRecord=count(OrdersId) from Orders where tel=@tel
select @pageRecord=cast( CEILING(CAST(@TotalRecord as decimal(10,2)) / @size) as int)end
这三个都是订单表的字段,
下面是个通过手机号来搜索订单的存储过程,通常情况下我要写三个存储过程来对应每一种条件搜索!
但是这样很不好,代码写多了没意思!
我现在要三个存储过程合为一,应该怎么呢?
Create proc Pro_Fujet_Orders_SearchByTel
@index int,
@size int,
@tel varchar(11),
@TotalRecord int output,
@PageRecord int output
as
begin
select top(@size) * from
(
select
Row_Number() over( order by CreateOn desc) as RowNumber,
OrderNum,
OrdersId,
payDate,
Paymoney,
[status]
from orders
where tel=@tel
) as a
where a.RowNumber>(@index-1)*@sizeselect @TotalRecord=count(OrdersId) from Orders where tel=@tel
select @pageRecord=cast( CEILING(CAST(@TotalRecord as decimal(10,2)) / @size) as int)end
@index int,
@size int,
@tel varchar(11),
@TotalRecord int output,
@PageRecord int output
as
begin
select top(@size) * from
(
select
Row_Number() over( order by CreateOn desc) as RowNumber,
OrderNum,
OrdersId,
payDate,
Paymoney,
[status]
from orders
where tel=(case isnull(@tel,'')='' then tel else @tel end)
and
姓名=(case isnull(@姓名,'')='' then 姓名 else @姓名 end )) as a
where a.RowNumber>(@index-1)*@sizeselect @TotalRecord=count(OrdersId) from Orders where tel=@tel
select @pageRecord=cast( CEILING(CAST(@TotalRecord as decimal(10,2)) / @size) as int)end
@index int,
@size int,
@tel varchar(11),
@TotalRecord int output,
@PageRecord int output
as
begin
select top(@size) * from
(
select
Row_Number() over( order by CreateOn desc) as RowNumber,
OrderNum,
OrdersId,
payDate,
Paymoney,
[status]
from orders
where tel=(case isnull(@tel,'')='' then tel else @tel end)
and
姓名=(case isnull(@姓名,'')='' then 姓名 else @姓名 end )
) as a
where a.RowNumber>(@index-1)*@sizeselect @TotalRecord=count(OrdersId) from Orders where tel=@tel
select @pageRecord=cast( CEILING(CAST(@TotalRecord as decimal(10,2)) / @size) as int)end
不明白为什么在where中要用 and
Create proc Pro_Fujet_Orders_SearchByTel
@index int,
@size int,
@tel varchar(11)='', --手机
@email varchar(30)='', --Email
@name nvarchar(30)='', --姓名
@TotalRecord int output,
@PageRecord int output
as
begin
select top(@size) * from
(
select
Row_Number() over( order by CreateOn desc) as RowNumber,
OrderNum,
OrdersId,
payDate,
Paymoney,
[status]
from orders
where (@tel='' or tel=@tel) --Add
and (@email='' or Email=@email) --Add
and (@name='' or Name=@name) --Add
) as a
where a.RowNumber>(@index-1)*@sizeselect @TotalRecord=count(OrdersId) from Orders where tel=@tel
select @pageRecord=cast( CEILING(CAST(@TotalRecord as decimal(10,2)) / @size) as int)end