CREATE procedure p_show2( @select_list varchar(1000)='*',--不需要select@table_name varchar(100),@where varchar(1000)='',--不需要where@primary_key varchar(100),--当是表联合时，加表名前缀.@order_by varchar(200),--需要完整的子句 order by ...@page_size smallint=20,--每页记录@page_index int=1,--页索引@do_count bit=0,@iPageCount int = 1 OUTPUT, --总的记录数@pageCount int = 1 output ----查询结果分页后的总页数)--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 @strTmp nvarchar(4000)declare @QueryStr nvarchar(4000)
set @QueryStr='select * from '+@table_name+' where '+@whereprint(@QueryStr)set @strTmp='select @iPageCount=count(*) from '+@table_name+' where '+@where----取得查询结果总数量-------print(@strTmp)exec sp_executesql @strTmp,N'@iPageCount int out ',@iPageCount out--print(@backinfo)
declare @tmpCounts intif @iPageCount = 0begin set @pageCount = 0endelsebegin set @tmpCounts = @iPageCount --取得分页总数 set @pageCount=(@tmpCounts+@page_size-1)/@page_size --print(@pageCount)end
declare @sql_str varchar(8000)declare @record_min intdeclare @new_where varchar(1000),@newin_where varchar(1000)if @where=''--重新为梳理，此过程时性能的考虑，因此不使用 where 1=1 再追加条件。beginselect @new_where=''select @newin_where=''endelsebeginselect @new_where=' and '+@whereselect @newin_where=' where '+@whereend
if @do_count=1select @sql_str='select count(*) from '+@table_name+@newin_whereelseif @page_index=1if @where=''select @sql_str='select top '+convert(varchar,@page_size)+ ' '+@select_list+' from '+@table_name+' '+@order_byelseselect @sql_str='select top '+convert(varchar,@page_size)+ ' '+@select_list+' from '+@table_name+' where '+@where+' '+@order_byelsebeginselect @record_min=(@page_index-1)*@page_sizeselect @sql_str='select top '+convert(varchar,@page_size)+' '+@select_list+' from '+@table_name+' where '+@primary_key+' not in (select '+stuff(@primary_key,1,charindex('.',@primary_key),'')select @sql_str=@sql_str+' from (select top '+convert(varchar,@record_min)+' '+@primary_key+' from '+@table_name+@newin_where+' '+@order_by+') temptable0000)'select @sql_str=@sql_str+@new_where+' '+@order_byend--print @sql_strexec(@sql_str)GO
