网站地图
【www.gdgbn.com--游戏活动】
最简单的net分页程序
CREATE procedure [dbo].[sp_getPageControl]
(
@TableNames nvarchar(200), --表名，可以是多个表，但不能用别名
@PrimaryKey nvarchar(100), --主键，可以为空，但@Order为空时该值不能为空
@Fields nvarchar(1000) = "*", --要取出的字段，可以是多个表的字段，可以为空，为空表示select *
@PageSize int, --每页记录数
@CurrentPage int, --当前页，0表示第1页
@RecNums int output, --记录个数
@PageNums int output, -- 页数
@Filter nvarchar(1000) = "", --条件，可以为空，不用填 where
@Group nvarchar(200) = "", --分组依据，可以为空，不用填 group by
@Order nvarchar(200) = "" --排序，可以为空，为空默认按主键升序排列，不用填 order by
)
as
set nocount on
begin
declare @SortColumn nvarchar(1000)
declare @Operator nchar(2)
declare @SortTable nvarchar(200)
declare @SortName nvarchar(200)
declare @sql nvarchar(4000)
declare @sqlTemp nvarchar(4000)
if @Fields = ""
set @Fields = "*"
if @Filter = ""
set @Filter = "Where 1=1"
else
set @Filter = "Where 1=1 and " + @Filter
if @Group <>""
set @Group = "GROUP BY " + @Group
if @Order <> ""
begin
declare @pos1 int, @pos2 int
set @Order = replace(replace(@Order, " asc", " ASC"), " desc", " DESC")
if charindex(" DESC", @Order) > 0
if charindex(" ASC", @Order) > 0
begin
if charindex(" DESC", @Order) < charindex(" ASC", @Order)
set @Operator = "<="
else
set @Operator = ">="
end
else
set @Operator = "<="
else
set @Operator = ">="
set @SortColumn = replace(replace(replace(@Order, " ASC", ""), " DESC", ""), " ", "")
set @pos1 = charindex(",", @SortColumn)
if @pos1 > 0
set @SortColumn = substring(@SortColumn, 1, @pos1-1)
set @pos2 = charindex(".", @SortColumn)
if @pos2 > 0
begin
set @SortTable = substring(@SortColumn, 1, @pos2-1)
if @pos1 > 0
set @SortName = substring(@SortColumn, @pos2+1, @pos1-@pos2-1)
else
set @SortName = substring(@SortColumn, @pos2+1, len(@SortColumn)-@pos2)
end
else
begin
set @SortTable = @TableNames
set @SortName = @SortColumn
end
end
else
begin
set @SortColumn = @PrimaryKey
set @SortTable = @TableNames
set @SortName = @SortColumn
set @Order = @SortColumn
set @Operator = ">="
end
declare @type nvarchar(50)
declare @prec int
select @type=t.name, @prec=c.prec
from sysobjects o
join syscolumns c on o.id=c.id
join systypes t on c.xusertype=t.xusertype
where o.name = @SortTable and c.name = @SortName
if charindex("char", @type) > 0
set @type = @type + "(" + cast(@prec as nvarchar) + ")"
declare @TopRows int
set @TopRows = @PageSize * @CurrentPage + 1
--print @TopRows
--print @Operator
-- 统计记录个数
set @sqlTemp = "Select @counts=count(*) from " + @TableNames + " " + @Filter
----取得查询结果总数量-----
exec sp_executesql @sqlTemp,N"@counts int out",@RecNums out
--取得分页总数
if @RecNums <= @pageSize
set @PageNums = 1
else
set @PageNums = (@RecNums / @pageSize) + 1
select @sql = "declare @SortColumnBegin " + @type + "
set rowcount " + convert(char,@TopRows) + "
select @SortColumnBegin=" + @SortColumn + " from " + @TableNames + " " + @Filter + " " + @Group + " order by " + @Order + "
set rowcount " + convert(char,@PageSize) + "
select " + @Fields + " from " + @TableNames + " " + @Filter + " and " + @SortColumn + " " + @Operator + "@SortColumnBegin " + @Group + " order by " + @Order
--print @sql
------返回查询结果-----
exec sp_executesql @sql
end
看了这个分页程序后你会有什么感想呢,是不是为最简单的呢啊.