@tblName varchar(255), -- 表名 @OrderfldName varchar(255), -- 排序字段名 @PageSize int = 10, -- 页尺寸 @PageIndex int = 1, -- 页码 @IsReCount bit = 1, -- 返回记录总数, 非 0 值则返回 @OrderType bit = 1, -- 设置排序类型, 非 0 值则降序 @strWhere varchar(1000)='' -- 查询条件 (注意: 不要加 where) AS declare @strSQL varchar(6000) -- 主语句 declare @strTmp varchar(400) -- 临时变量 declare @strOrder varchar(400) -- 排序类型 if @OrderType != 0 begin set @strTmp = '<(select min' set @strOrder = ' order by [' + @OrderfldName +'] desc' end else begin set @strTmp = '>(select max' set @strOrder = ' order by [' + @OrderfldName +'] asc' end set @strSQL = 'select top ' + str(@PageSize) + ' * from [' + @tblName + '] where [' + @OrderfldName + ']' + @strTmp + '([' + @OrderfldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @OrderfldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)' + @strOrder if @strWhere != '' set @strSQL = 'select top ' + str(@PageSize) + ' * from [' + @tblName + '] where [' + @OrderfldName + ']' + @strTmp + '([' + @OrderfldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @OrderfldName + '] from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder if @PageIndex = 1 begin set @strTmp = '' if @strWhere != '' set @strTmp = ' where ' + @strWhere set @strSQL = 'select top ' + str(@PageSize) + ' * from [' + @tblName + ']' + @strTmp + ' ' + @strOrder end if @IsReCount != 0 set @strSQL = @strSQL+' select count(1) as Total from [' + @tblName + ']'if @strWhere!='' set @strSQL = @strSQL+' where ' + @strWhere print(@strSQL) exec (@strSQL) GO
//分页功能实现 using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Text; public class Pager { private Pager() { } public static String getPager(int total, int pagesize, int pagenum,string path) { int count = total / pagesize; if (total % pagesize > 0) count++; StringBuilder sb = new StringBuilder(); sb.Append("<DIV class=\"pages-nav\" style=\"margin: 10px 7px 0px 0px;padding:0 0 20px 0px!important;padding-bottom:0;\">"); if (pagenum == 1) sb.Append("<SPAN class=\"next\">? 上一页</SPAN>"); else sb.Append("<A href=\"" + path + "?pagenum=" + (pagenum - 1) + "\" class=\"next\">? 上一页</A>"); int bound1 = ((pagenum - 2) <= 0) ? 1 : (pagenum - 2); int bound2 = ((pagenum + 2) >= count) ? count : (pagenum + 2); if (bound1 > 1) sb.Append("<SPAN>...</SPAN>"); for (int i = bound1; i <= bound2; i++) { if (i == pagenum) sb.Append("<SPAN class=\"current\">" + i + "</SPAN>"); else sb.Append("<a href='" + path + "?pagenum=" + i + "'>" + i + "</a>"); } if (bound2 < count) sb.Append("<SPAN>...</SPAN>"); if (pagenum == count) sb.Append("<SPAN class=\"next\"> 下一页 ?</SPAN>"); else sb.Append("<A href=\"" + path + "?pagenum=" + (pagenum + 1) + "\" class=\"next\">下一页 ?</A>"); sb.Append("</DIV>"); return sb.ToString(); } }
我刚好又个demo 是以前一个高手教我,发给我的 你如果需要 晚上回家发给你 留下邮箱
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROC [dbo].[PROCE_PageView2000] ( @tbname nvarchar(100), --要分页显示的表名 @FieldKey nvarchar(1000), --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段 @PageCurrent int=1, --要显示的页码 @PageSize int=10, --每页的大小(记录数) @FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段 @FieldOrder nvarchar(1000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC @WhereString nvarchar(1000)='', --查询条件 @RecordCount int OUTPUT --总页数 ) AS SET NOCOUNT ON --检查对象是否有效 --IF OBJECT_ID(convert(sysname,@tbname)) IS NULL --BEGIN -- RAISERROR(N'对象"%s"不存在',1,16,@tbname) -- RETURN --END --IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0 -- AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0 -- AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0 --BEGIN -- RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname) -- RETURN --END--分页字段检查 IF ISNULL(@FieldKey,N'')='' BEGIN RAISERROR(N'分页处理需要主键(或者惟一键)',1,16) RETURN END--其他参数检查及规范 IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1 IF ISNULL(@PageSize,0)<1 SET @PageSize=10 IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*' IF ISNULL(@FieldOrder,N'')=N'' SET @FieldOrder=N'' ELSE SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder) IF ISNULL(@WhereString,N'')=N'' SET @WhereString=N'' ELSE SET @WhereString=N'WHERE ('+@WhereString+N')'--如果@RecordCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@RecordCount赋值) IF @RecordCount IS NULL BEGIN DECLARE @sql nvarchar(4000) SET @sql=N'SELECT @RecordCount=COUNT(*)' +N' FROM '+@tbname +N' '+@WhereString EXEC sp_executesql @sql,N'@RecordCount int OUTPUT',@RecordCount OUTPUT END--计算分页显示的TOPN值 DECLARE @TopN varchar(20),@TopN1 varchar(20) SELECT @TopN=@PageSize, @TopN1=(@PageCurrent-1)*@PageSize--第一页直接显示 IF @PageCurrent=1 EXEC(N'SELECT TOP '+@TopN +N' '+@FieldShow +N' FROM '+@tbname +N' '+@WhereString +N' '+@FieldOrder) ELSE BEGIN --处理别名 IF @FieldShow=N'*' SET @FieldShow=N'a.*' --生成主键(惟一键)处理条件 DECLARE @Where1 nvarchar(4000),@Where2 nvarchar(4000), @s nvarchar(1000),@Field sysname SELECT @Where1=N'',@Where2=N'',@s=@FieldKey WHILE CHARINDEX(N',',@s)>0 SELECT @Field=LEFT(@s,CHARINDEX(N',',@s)-1), @s=STUFF(@s,1,CHARINDEX(N',',@s),N''), @Where1=@Where1+N' AND a.'+@Field+N'=b.'+@Field, @Where2=@Where2+N' AND b.'+@Field+N' IS NULL', @WhereString=REPLACE(@WhereString,@Field,N'a.'+@Field), @FieldOrder=REPLACE(@FieldOrder,@Field,N'a.'+@Field), @FieldShow=REPLACE(@FieldShow,@Field,N'a.'+@Field) SELECT @WhereString=REPLACE(@WhereString,@s,N'a.'+@s), @FieldOrder=REPLACE(@FieldOrder,@s,N'a.'+@s), @FieldShow=REPLACE(@FieldShow,@s,N'a.'+@s), @Where1=STUFF(@Where1+N' AND a.'+@s+N'=b.'+@s,1,5,N''), @Where2=CASE WHEN @WhereString='' THEN N'WHERE (' ELSE @WhereString+N' AND (' END+N'b.'+@s+N' IS NULL'+@Where2+N')' --执行查询 EXEC(N'SELECT TOP '+@TopN +N' '+@FieldShow +N' FROM '+@tbname +N' a LEFT JOIN(SELECT TOP '+@TopN1 +N' '+@FieldKey +N' FROM '+@tbname +N' a '+@WhereString +N' '+@FieldOrder +N')b ON '+@Where1 +N' '+@Where2 +N' '+@FieldOrder) END
CREATE PROCEDURE GetRecordFromPage @SQL varchar(8000), @PageSize int = 10, @PageIndex int = 1, @strOut varchar(6000) output @RecordCount int = 0 output, @PageCount int =0 output AS declare @strSQL varchar(8000) declare @strTmp varchar(8000) declare @strOrder varchar(500) declare @tblName varchar(255) declare @fldName varchar(255) declare @strWhere varchar(8000) declare @tmpOrder varchar(255) declare @strFilds varchar(8000) declare @intFilds int declare @intOrder int declare @intSQL int declare @intWhere int declare @intTable int declare @strRsSql nvarchar(4000) set @intOrder=CharIndex('order by',@SQL) set @intSQL=Len(@SQL) set @intFilds=CharIndex('select ',@SQL) set @strFilds=SubString(@SQL,@intFilds+7,@intSQL-@intFilds-1) set @strFilds=SubString(@strFilds,1,CharIndex(' from ',@strFilds)) set @tblName=SubString(@SQL,CharIndex(' from ',@SQL)+6,@intSQL-CharIndex(' from ',@SQL)+6) set @intTable=CharIndex(' ',@tblName) if @intTable>0 begin set @tblName=SubString(@tblName,1,@intTable-1) end set @intWhere=CharIndex('where ',@SQL) if @intWhere>0 begin if @intOrder>0 begin set @strWhere=SubString(@SQL,@intWhere+6,@intOrder-@intWhere-7) end else begin set @strWhere=SubString(@SQL,@intWhere+6,@intSQL-@intWhere-5) end end else begin set @strWhere='' end if @intOrder>0 begin set @strOrder=' '+SubString(@SQL,@intOrder,@intSQL-@intOrder+1) set @tmpOrder=SubString(@strOrder,11,Len(@strOrder)-10) if CharIndex(' ',@tmpOrder)>0 begin set @fldName=SubString(@tmpOrder,1,CharIndex(' ',@tmpOrder)-1) end else begin set @fldName=@tmpOrder end if CharIndex(' desc ',@SQL)>0 begin set @strTmp='<(select min' end else begin set @strTmp='>(select max' end end else begin set @fldName='ID' set @strTmp = '>(select max' set @strOrder = ' order by [' + @fldName +'] asc' end set @strSQL = 'select top ' + LTrim(str(@PageSize)) + ' '+@strFilds+' from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + LTrim(str((@PageIndex-1)*@PageSize)) + ' [' + @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)' + @strOrder if @strWhere != '' set @strSQL = 'select top ' + LTrim(str(@PageSize)) + ' '+@strFilds+' from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + LTrim(str((@PageIndex-1)*@PageSize)) + ' [' + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder if @PageIndex = 1 begin set @strTmp = '' if @strWhere != '' set @strTmp = ' where (' + @strWhere + ')' set @strSQL = 'select top ' + LTrim(str(@PageSize)) + ' '+@strFilds+' from [' + @tblName + ']' + @strTmp + ' ' + @strOrder end exec (@strSQL) if @strWhere!='' begin set @strRsSql='select @RecordCount=count('+@fldName+') from ['+@tblName +'] where ('+@strWhere+')' end else begin set @strRsSql='select @RecordCount=count('+@fldName+') from ['+@tblName +']' end EXEC sp_executesql @strRsSql,N'@RecordCount int out',@RecordCount out SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize) set @strOut=@strSQL GO
-----------------------------------
--用途:支持任意排序的分页存储过程
--说明:
------------------------------------CREATE PROCEDURE UP_GetRecordByPageOrder
@tblName varchar(255), -- 表名
@OrderfldName varchar(255), -- 排序字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsReCount bit = 1, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 1, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1000)='' -- 查询条件 (注意: 不要加 where)
AS declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(400) -- 临时变量
declare @strOrder varchar(400) -- 排序类型 if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @OrderfldName +'] desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @OrderfldName +'] asc'
end set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @OrderfldName + ']' + @strTmp + '(['
+ @OrderfldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @OrderfldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
+ @strOrder if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @OrderfldName + ']' + @strTmp + '(['
+ @OrderfldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @OrderfldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder if @PageIndex = 1
begin
set @strTmp = ''
if @strWhere != ''
set @strTmp = ' where ' + @strWhere set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + ']' + @strTmp + ' ' + @strOrder
end
if @IsReCount != 0
set @strSQL = @strSQL+' select count(1) as Total from [' + @tblName + ']'if @strWhere!=''
set @strSQL = @strSQL+' where ' + @strWhere
print(@strSQL)
exec (@strSQL)
GO
//分页功能实现
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text; public class Pager
{
private Pager()
{
} public static String getPager(int total, int pagesize, int pagenum,string path)
{
int count = total / pagesize; if (total % pagesize > 0)
count++; StringBuilder sb = new StringBuilder();
sb.Append("<DIV class=\"pages-nav\" style=\"margin: 10px 7px 0px 0px;padding:0 0 20px 0px!important;padding-bottom:0;\">");
if (pagenum == 1)
sb.Append("<SPAN class=\"next\">? 上一页</SPAN>");
else
sb.Append("<A href=\"" + path + "?pagenum=" + (pagenum - 1) + "\" class=\"next\">? 上一页</A>");
int bound1 = ((pagenum - 2) <= 0) ? 1 : (pagenum - 2);
int bound2 = ((pagenum + 2) >= count) ? count : (pagenum + 2); if (bound1 > 1)
sb.Append("<SPAN>...</SPAN>"); for (int i = bound1; i <= bound2; i++)
{
if (i == pagenum)
sb.Append("<SPAN class=\"current\">" + i + "</SPAN>");
else
sb.Append("<a href='" + path + "?pagenum=" + i + "'>" + i + "</a>");
} if (bound2 < count)
sb.Append("<SPAN>...</SPAN>"); if (pagenum == count)
sb.Append("<SPAN class=\"next\"> 下一页 ?</SPAN>");
else
sb.Append("<A href=\"" + path + "?pagenum=" + (pagenum + 1) + "\" class=\"next\">下一页 ?</A>"); sb.Append("</DIV>");
return sb.ToString(); }
}
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROC [dbo].[PROCE_PageView2000]
(
@tbname nvarchar(100), --要分页显示的表名
@FieldKey nvarchar(1000), --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段
@PageCurrent int=1, --要显示的页码
@PageSize int=10, --每页的大小(记录数)
@FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar(1000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
@WhereString nvarchar(1000)='', --查询条件
@RecordCount int OUTPUT --总页数
)
AS
SET NOCOUNT ON
--检查对象是否有效
--IF OBJECT_ID(convert(sysname,@tbname)) IS NULL
--BEGIN
-- RAISERROR(N'对象"%s"不存在',1,16,@tbname)
-- RETURN
--END
--IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0
-- AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0
-- AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0
--BEGIN
-- RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)
-- RETURN
--END--分页字段检查
IF ISNULL(@FieldKey,N'')=''
BEGIN
RAISERROR(N'分页处理需要主键(或者惟一键)',1,16)
RETURN
END--其他参数检查及规范
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1
IF ISNULL(@PageSize,0)<1 SET @PageSize=10
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
IF ISNULL(@FieldOrder,N'')=N''
SET @FieldOrder=N''
ELSE
SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)
IF ISNULL(@WhereString,N'')=N''
SET @WhereString=N''
ELSE
SET @WhereString=N'WHERE ('+@WhereString+N')'--如果@RecordCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@RecordCount赋值)
IF @RecordCount IS NULL
BEGIN
DECLARE @sql nvarchar(4000)
SET @sql=N'SELECT @RecordCount=COUNT(*)'
+N' FROM '+@tbname
+N' '+@WhereString
EXEC sp_executesql @sql,N'@RecordCount int OUTPUT',@RecordCount OUTPUT
END--计算分页显示的TOPN值
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=@PageSize,
@TopN1=(@PageCurrent-1)*@PageSize--第一页直接显示
IF @PageCurrent=1
EXEC(N'SELECT TOP '+@TopN
+N' '+@FieldShow
+N' FROM '+@tbname
+N' '+@WhereString
+N' '+@FieldOrder)
ELSE
BEGIN
--处理别名
IF @FieldShow=N'*'
SET @FieldShow=N'a.*' --生成主键(惟一键)处理条件
DECLARE @Where1 nvarchar(4000),@Where2 nvarchar(4000),
@s nvarchar(1000),@Field sysname
SELECT @Where1=N'',@Where2=N'',@s=@FieldKey
WHILE CHARINDEX(N',',@s)>0
SELECT @Field=LEFT(@s,CHARINDEX(N',',@s)-1),
@s=STUFF(@s,1,CHARINDEX(N',',@s),N''),
@Where1=@Where1+N' AND a.'+@Field+N'=b.'+@Field,
@Where2=@Where2+N' AND b.'+@Field+N' IS NULL',
@WhereString=REPLACE(@WhereString,@Field,N'a.'+@Field),
@FieldOrder=REPLACE(@FieldOrder,@Field,N'a.'+@Field),
@FieldShow=REPLACE(@FieldShow,@Field,N'a.'+@Field)
SELECT @WhereString=REPLACE(@WhereString,@s,N'a.'+@s),
@FieldOrder=REPLACE(@FieldOrder,@s,N'a.'+@s),
@FieldShow=REPLACE(@FieldShow,@s,N'a.'+@s),
@Where1=STUFF(@Where1+N' AND a.'+@s+N'=b.'+@s,1,5,N''),
@Where2=CASE
WHEN @WhereString='' THEN N'WHERE ('
ELSE @WhereString+N' AND ('
END+N'b.'+@s+N' IS NULL'+@Where2+N')' --执行查询
EXEC(N'SELECT TOP '+@TopN
+N' '+@FieldShow
+N' FROM '+@tbname
+N' a LEFT JOIN(SELECT TOP '+@TopN1
+N' '+@FieldKey
+N' FROM '+@tbname
+N' a '+@WhereString
+N' '+@FieldOrder
+N')b ON '+@Where1
+N' '+@Where2
+N' '+@FieldOrder)
END
@SQL varchar(8000),
@PageSize int = 10,
@PageIndex int = 1,
@strOut varchar(6000) output
@RecordCount int = 0 output,
@PageCount int =0 output
AS
declare @strSQL varchar(8000)
declare @strTmp varchar(8000)
declare @strOrder varchar(500)
declare @tblName varchar(255)
declare @fldName varchar(255)
declare @strWhere varchar(8000)
declare @tmpOrder varchar(255)
declare @strFilds varchar(8000)
declare @intFilds int
declare @intOrder int
declare @intSQL int
declare @intWhere int
declare @intTable int
declare @strRsSql nvarchar(4000) set @intOrder=CharIndex('order by',@SQL)
set @intSQL=Len(@SQL)
set @intFilds=CharIndex('select ',@SQL)
set @strFilds=SubString(@SQL,@intFilds+7,@intSQL-@intFilds-1)
set @strFilds=SubString(@strFilds,1,CharIndex(' from ',@strFilds))
set @tblName=SubString(@SQL,CharIndex(' from ',@SQL)+6,@intSQL-CharIndex(' from ',@SQL)+6)
set @intTable=CharIndex(' ',@tblName)
if @intTable>0
begin
set @tblName=SubString(@tblName,1,@intTable-1)
end
set @intWhere=CharIndex('where ',@SQL)
if @intWhere>0
begin
if @intOrder>0
begin
set @strWhere=SubString(@SQL,@intWhere+6,@intOrder-@intWhere-7)
end
else
begin
set @strWhere=SubString(@SQL,@intWhere+6,@intSQL-@intWhere-5)
end
end
else
begin
set @strWhere=''
end
if @intOrder>0
begin
set @strOrder=' '+SubString(@SQL,@intOrder,@intSQL-@intOrder+1)
set @tmpOrder=SubString(@strOrder,11,Len(@strOrder)-10)
if CharIndex(' ',@tmpOrder)>0
begin
set @fldName=SubString(@tmpOrder,1,CharIndex(' ',@tmpOrder)-1)
end
else
begin
set @fldName=@tmpOrder
end
if CharIndex(' desc ',@SQL)>0
begin
set @strTmp='<(select min'
end
else
begin
set @strTmp='>(select max'
end
end
else
begin
set @fldName='ID'
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
end
set @strSQL = 'select top ' + LTrim(str(@PageSize)) + ' '+@strFilds+' from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + LTrim(str((@PageIndex-1)*@PageSize)) + ' ['
+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + LTrim(str(@PageSize)) + ' '+@strFilds+' from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + LTrim(str((@PageIndex-1)*@PageSize)) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
if @PageIndex = 1
begin
set @strTmp = ''
if @strWhere != ''
set @strTmp = ' where (' + @strWhere + ')'
set @strSQL = 'select top ' + LTrim(str(@PageSize)) + ' '+@strFilds+' from ['
+ @tblName + ']' + @strTmp + ' ' + @strOrder
end
exec (@strSQL)
if @strWhere!=''
begin
set @strRsSql='select @RecordCount=count('+@fldName+') from ['+@tblName
+'] where ('+@strWhere+')'
end
else
begin
set @strRsSql='select @RecordCount=count('+@fldName+') from ['+@tblName
+']'
end
EXEC sp_executesql @strRsSql,N'@RecordCount int out',@RecordCount out
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
set @strOut=@strSQL
GO
这个实在些