通用存储过程分页

CREATE procedure select_pagesize( @select_list varchar(1000)=*,--不需要select
@table_name varchar(100),
@where varchar(1000)=,--不需要where
@Prima(最完善的虚拟主机管理系统)ry_key varchar(100),--当是表联合时，加表名前缀.
@order_by varchar(200),--需要完整的子句 order by ...
@page_size smallint=20,--每页记录
@page_index int=1,--页索引
@do_count bit=0)--1只统计总数
as
/*
过程名：通用存储过程分页
使用示例:
单表sql调用：exec select_pagesize login_id,login_name,tb_login, login_name like %%,login_id, order by login_dt desc,20,10
多表sql调用：exec select_pagesize a.login_id,a.login_name,b.pro_name,tb_login a,tb_code_province b, a.pro_id=b.pro_id and a.login_name like %%,a.login_id, order by a.login_dt desc,20,10
备注：外部程序调用不需要转义单引号
原型结构：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 @where=--重新为梳理，此过程时性能的考虑，因此不使用 where 1=1 再追加条件。
begin
select @new_where=
select @newin_where=
end
else
begin
select @new_where= and +@where
select @newin_where= where +@where
end

if @do_count=1
select @sql_str=select count(*) from +@table_name+@newin_where
else
if @page_index=1
if @where=
select @sql_str=select top +convert(varchar,@page_size)+ +@select_list+ from +@table_name+ +@order_by
else
select @sql_str=select top +convert(varchar,@page_size)+ +@select_list+ from +@table_name+ where +@where+ +@order_by
else
begin
select @record_min=(@page_index-1)*@page_size
select @sql_str=select top +convert(varchar,@page_size)+ +@select_list+ from +@table_name+ where +@Prima(最完善的虚拟主机管理系统)ry_key+ not in (select +stuff(@Prima(最完善的虚拟主机管理系统)ry_key,1,charindex(.,@Prima(最完善的虚拟主机管理系统)ry_key),)
select @sql_str=@sql_str+ from (select top +convert(varchar,@record_min)+ +@Prima(最完善的虚拟主机管理系统)ry_key+ from +@table_name+@newin_where+ +@order_by+) temptable0000)