/**
exec sp_queryByPage 2,1,'','',''
exec sp_queryByPage 2,1,null,'',''
*/
use Goods
if exists(select name from sysobjects
where name='sp_queryByPage' and type='P')
DROP procedure sp_queryByPage
go
create procedure sp_queryByPage
(@pageSize int,
@nowPage int,
@orderDay datetime =null,
@supplierID varchar(50),
@orderID varchar(50)
)
as
declare @rowBegin as int
select @rowBegin=(@nowPage-1)*@pageSize
declare @sql varchar(4000)
set @sql='
select top $pageSize *
from dbo.tb_order
where orderDay=isnull(''$orderDay'',orderDay)
and supplierID=CASE ''$supplierID'' WHEN '''' THEN supplierID ELSE ''$supplierID'' END
and orderID=CASE ''$orderID'' WHEN '''' THEN supplierID ELSE ''$orderID'' END
and orderID
not in(
select top $rowBegin orderID
from tb_order
where orderDay=isnull(''$orderDay'',orderDay)
and supplierID=CASE ''$supplierID'' WHEN '''' THEN supplierID ELSE ''$supplierID'' END
and orderID=CASE ''$orderID'' WHEN '''' THEN supplierID ELSE ''$orderID'' END
)
'
set @sql=replace(@sql,'$pageSize',@pageSize)
set @sql=replace(@sql,'$rowBegin',@rowBegin)
set @sql=replace(@sql,'$orderDay',@orderDay)
set @sql=replace(@sql,'$supplierID',@supplierID)
set @sql=replace(@sql,'$orderID',@orderID)
exec (@sql)
print(@sql)
/** exec sp_queryByPage 2,1,'','','' exec sp_queryByPage 2,1,null,'','' */ use Goods if exists(select name from sysobjects where name='sp_queryByPage' and type='P') DROP procedure sp_queryByPage go create procedure sp_queryByPage (@pageSize int, @no
/**
exec sp_queryByPage 2,1,'','',''
exec sp_queryByPage 2,1,null,'',''
*/
if exists(select name from sysobjects
where name='sp_queryByPage' and type='P')
DROP procedure sp_queryByPage
go
create procedure sp_queryByPage
(@pageSize int,
@nowPage int,
@orderDay datetime ,
@supplierID varchar(50),
@orderID varchar(50)
)
as
begin
declare @rowBegin as int
select @rowBegin=(@nowPage-1)*@pageSize
declare @sql varchar(4000)
declare @condition varchar(50)
set @condition=''
if(@orderDay!=null)
begin
set @condition=' and orderDay=isnull(''$orderDay'',orderDay)'
end
set @sql='
select top $pageSize *
from dbo.tb_order
where 1=1 '+@condition+'
and supplierID=CASE ''$supplierID'' WHEN '''' THEN supplierID ELSE ''$supplierID'' END
and orderID=CASE ''$orderID'' WHEN '''' THEN supplierID ELSE ''$orderID'' END
and orderID
not in(
select top $rowBegin orderID
from tb_order
where orderDay=isnull(''$orderDay'',orderDay)
and supplierID=CASE ''$supplierID'' WHEN '''' THEN supplierID ELSE ''$supplierID'' END
and orderID=CASE ''$orderID'' WHEN '''' THEN supplierID ELSE ''$orderID'' END
)
'
set @sql=replace(@sql,'$pageSize',@pageSize)
set @sql=replace(@sql,'$rowBegin',@rowBegin)
set @sql=replace(@sql,'$orderDay',@orderDay)
set @sql=replace(@sql,'$supplierID',@supplierID)
set @sql=replace(@sql,'$orderID',@orderID)
--select @sql
exec (@sql)
end
go
---exec sp_queryByPage 2,1,null,'','' 第一个不执行,因为类型不对
--exec sp_queryByPage 2,1,'','',''
--set @sql=replace(@sql,'$orderDay',@orderDay)
IF @orderDay IS NULL
set @sql=replace(@sql,''$orderDay'','NULL')
ELSE
set @sql=replace(@sql,'$orderDay',@orderDay)
ALTER procedure sp_queryByPage
(@pageSize int,
@nowPage int,
@orderDay datetime =null,
@supplierID varchar(50),
@orderID varchar(50)
)
as
declare @rowBegin as int
select @rowBegin=(@nowPage-1)*@pageSize
declare @sql varchar(4000)
set @sql='
select top $pageSize *
from dbo.tb_order
where orderDay=isnull('''+CONVERT(VARCHAR(30),@orderDay,121)+''',orderDay)
and supplierID=CASE ''$supplierID'' WHEN '''' THEN supplierID ELSE ''$supplierID'' END
and orderID=CASE ''$orderID'' WHEN '''' THEN supplierID ELSE ''$orderID'' END
and orderID
not in(
select top $rowBegin orderID
from tb_order
where orderDay=isnull('''+CONVERT(VARCHAR(30),@orderDay,121)+''',orderDay)
and supplierID=CASE ''$supplierID'' WHEN '''' THEN supplierID ELSE ''$supplierID'' END
and orderID=CASE ''$orderID'' WHEN '''' THEN supplierID ELSE ''$orderID'' END
)
'
set @sql=replace(@sql,'$pageSize',@pageSize)
set @sql=replace(@sql,'$rowBegin',@rowBegin)
set @sql=replace(@sql,'$orderDay',@orderDay)
set @sql=replace(@sql,'$supplierID',@supplierID)
set @sql=replace(@sql,'$orderID',@orderID)
--exec (@sql)
print(@sql)
go
exec sp_queryByPage 2,1,'','',''
/**
exec sp_queryByPage 2,1,'','',''
exec sp_queryByPage 2,1,null,'',''
*/
if exists(select name from sysobjects
where name='sp_queryByPage' and type='P')
DROP procedure sp_queryByPage
go
create procedure sp_queryByPage
(@pageSize int,
@nowPage int,
@orderDay datetime ,
@supplierID varchar(50),
@orderID varchar(50)
)
as
begin
declare @rowBegin as int
select @rowBegin=(@nowPage-1)*@pageSize
declare @sql varchar(4000)
declare @condition varchar(50)
set @condition=''
if(@orderDay!=null)
begin
set @condition=' and orderDay=isnull(''$orderDay'',orderDay)'
end
set @sql='
select top $pageSize *
from dbo.tb_order
where 1=1 '+@condition+'
and supplierID=CASE ''$supplierID'' WHEN '''' THEN supplierID ELSE ''$supplierID'' END
and orderID=CASE ''$orderID'' WHEN '''' THEN supplierID ELSE ''$orderID'' END
and orderID
not in(
select top $rowBegin orderID
from tb_order
where orderDay=isnull(''$orderDay'',orderDay)
and supplierID=CASE ''$supplierID'' WHEN '''' THEN supplierID ELSE ''$supplierID'' END
and orderID=CASE ''$orderID'' WHEN '''' THEN supplierID ELSE ''$orderID'' END
)
'
set @sql=replace(@sql,'$pageSize',@pageSize)
set @sql=replace(@sql,'$rowBegin',@rowBegin)
set @sql=replace(@sql,'$orderDay',@orderDay)
set @sql=replace(@sql,'$supplierID',@supplierID)
set @sql=replace(@sql,'$orderID',@orderID)
--select @sql
exec (@sql)
end
go
---exec sp_queryByPage 2,1,null,'','' 第一个不执行,因为类型不对
--exec sp_queryByPage 2,1,'','',''