SQL分页存储过程
create procedure [dbo].[UP_PagerData]
(
@Tables varchar(400),
@PrimaryKey varchar(100),--当是表联合时,加表名前缀.
@Sort varchar(200),--需要完整的子句 order by ...
@CurrentPage int=1,--页索引
@PageSize smallint=20,--每页记录
@Fields varchar(1000)='*',--不需要select
@Filter varchar(1000)='',--不需要where
@docount bit=0,--1只统计总数
@lineCount varchar(20)='0' output
)
as
/*
过程名:通用存储过程分页
使用示例:
单表sql调用:exec sp_pagination_test 'tb_login','login_id','order by login_dt desc','1','50','*','login_ist=0 and long_3=0','0'
多表sql调用:exec sp_pagination_test 'tb_login a,tb_code_province b','a.login_id',' order by a.login_dt desc',1,20,'*',' a.pro_id=b.pro_id and a.login_name like ''%%''','a.login_id,a.login_name,b.pro_name','a.login_id',0
备注:外部程序调用不需要转义单引号
原型结构:select top 20 select_list
from tablename
where z_id not in(select z_id from (select top 100 z_id from tablename order by order_by) temptable)
and ...
order by order_by
*/
declare @sql_str varchar(8000)
declare @record_min int
declare @new_where varchar(1000),@newin_where varchar(1000)
if @Filter=''--重新为梳理,此过程时性能的考虑,因此不使用 where 1=1 再追加条件。
begin
select @new_where=''
select @newin_where=''
end
else
begin
select @new_where=' and '+@Filter
select @newin_where=' where '+@Filter
end
declare @cTemp NVarChar(1000)
CREATE TABLE #temp(linecount INT)
set @cTemp = 'insert into #temp (linecount) select count(*) from '+@Tables+@newin_where
exec (@cTemp)
select @lineCount = linecount from #temp
drop table #temp
if @docount=1
select @sql_str='select count(*) from '+@Tables+@newin_where
else
if @CurrentPage=1
if @Filter=''
select @sql_str='select top '+convert(varchar,@PageSize)+ ' '+@Fields+' from '+@Tables+' '+@Sort
else
select @sql_str='select top '+convert(varchar,@PageSize)+ ' '+@Fields+' from '+@Tables+' where '+@Filter+' '+@Sort
else
begin
select @record_min=(@CurrentPage-1)*@PageSize
select @sql_str='select top '+convert(varchar,@PageSize)+' '+@Fields+' from '+@Tables+' where '+@PrimaryKey+' not in (select '+stuff(@PrimaryKey,1,charindex('.',@PrimaryKey),'')
select @sql_str=@sql_str+' from (select top '+convert(varchar,@record_min)+' '+@PrimaryKey+' from '+@Tables+@newin_where+' '+@Sort+') temptable0000)'
select @sql_str=@sql_str+@new_where+' '+@Sort
end
print @lineCount
exec(@sql_str)
create PROCEDURE [dbo].[UP_PageData2005]
(
@TableName varchar(350), --表名
@ReFieldsStr varchar(3000) = '*', --字段名(全部字段为*)
@OrderString varchar(200), --排序字段(必须!支持多字段不用加order by)
@WhereString varchar(500) =N'', --条件语句(不用加where)
@PageSize int, --每页多少条记录
@PageIndex int = 1 , --指定当前为第几页
@TotalRecord int output --返回总记录数
)
AS
BEGIN
--处理开始点和结束点
Declare @StartRecord int;
Declare @EndRecord int;
Declare @TotalCountSql nvarchar(500);
Declare @SqlString nvarchar(2000);
set @StartRecord = (@PageIndex-1)*@PageSize + 1
set @EndRecord = @StartRecord + @PageSize - 1
SET @TotalCountSql= N'select @TotalRecord = count(*) from ' + @TableName;--总记录数语句
SET @SqlString = N'(select row_number() over (order by '+ @OrderString +') as rowId,'+@ReFieldsStr+' from '+ @TableName;--查询语句
--
IF (@WhereString! = '' or @WhereString!=null)
BEGIN
SET @TotalCountSql=@TotalCountSql + ' where '+ @WhereString;
SET @SqlString =@SqlString+ ' where '+ @WhereString;
END
--第一次执行得到
--IF(@TotalRecord is null)
-- BEGIN
EXEC sp_executesql @totalCountSql,N'@TotalRecord int out',@TotalRecord output;--返回总记录数
-- END
----执行主语句
set @SqlString ='select * from ' + @SqlString + ') as t where rowId between ' + ltrim(str(@StartRecord)) + ' and ' + ltrim(str(@EndRecord));
print @SqlString
Exec(@SqlString)
END