|
?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
?
CREATE proc [dbo].[up_Page2005]
?@TableName varchar(50),??????? --表名
?@FIELDS varchar(5000) = '*',??? --字段名(全部字段为*)
?@OrderField varchar(5000),??????? --排序字段(必须!支持多字段)
?@sqlWhere varchar(5000) = NULL,--条件语句(不用加where)
?@pageSize int,??????????????????? --每页多少条记录
?@pageIndex int = 1,??????????? --指定当前为第几页
?@TotalPage int output??????????? --返回总页数
AS
begin
?
??? Begin Tran --开始事务
?
??? Declare @sql nvarchar(4000);
??? Declare @totalRecord int;???
?
??? --计算总记录数
?
??? IF (@SqlWhere='' OR @sqlWhere=NULL)
??????? SET @sql = 'select @totalRecord = count(*) from ' + @TableName
??? else
??????? SET @sql = 'select @totalRecord = count(*) from ' + @TableName + ' with(nolock) where ' + @sqlWhere
?
??? EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数???????
?
??? --计算总页数
??? SELECT @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)
?
??? IF (@SqlWhere='' OR @sqlWhere=NULL)
??????? SET @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @FIELDS + ' from ' + @TableName
??? else
??????? SET @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @FIELDS + ' from ' + @TableName + ' with(nolock) where ' + @SqlWhere???
?
??? --处理页数超出范围情况
??? IF @PageIndex<=0
??????? SET @pageIndex = 1
?
??? IF @pageIndex>@TotalPage
??????? SET @pageIndex = @TotalPage
?
???? --处理开始点和结束点
??? Declare @StartRecord int
??? Declare @EndRecord int
?
??? SET @StartRecord = (@pageIndex-1)*@PageSize + 1
??? SET @EndRecord = @StartRecord + @pageSize - 1
?
??? --继续合成sql语句
??? SET @Sql = @Sql + ') as t where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' +? Convert(varchar(50),@EndRecord)
???? print @sql
??? Exec(@Sql)
??? ---------------------------------------------------
??? IF @@Error <> 0
????? Begin
??????? RollBack Tran
??????? RETURN -1
????? End
???? Else
????? Begin
??????? Commit Tran
??????? RETURN @totalRecord ---返回记录总数
????? End
end
?
?
/****** 对象:? StoredProcedure [dbo].[up_Page2005V2]??? 脚本日期: 05/21/2008 11:27:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:??????? <Author,Name>
-- Create date: <Create Date,>
-- Description:??? <Description,>
-- =============================================
CREATE PROCEDURE [dbo].[up_Page2005V2]
??? @TableName varchar(50),??????????? --指定当前为第几页
?@totalRecord int = 0,
?@TotalPage int output??????????? --返回总页数
AS
BEGIN
?
???? Begin Tran --开始事务
?
??? Declare @sql nvarchar(4000);
?
??? IF @totalRecord<=0 begin
??????? --计算总记录数
?
??????? IF (@SqlWhere='' OR @sqlWhere=NULL)
??????????? SET @sql = 'select @totalRecord = count(*) from ' + @TableName
??????? else
??????????? SET @sql = 'select @totalRecord = count(*) from ' + @TableName + ' with(nolock) where ' + @sqlWhere
?
??????? EXEC sp_executesql @sql,@totalRecord OUTPUT--计算总记录数
??? end
?
??? --计算总页数
??? SELECT @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)
?
??? IF (@SqlWhere='' OR @sqlWhere=NULL)
??????? SET @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,@EndRecord)
???? print @sql
??? Exec(@Sql)
??? ---------------------------------------------------
??? IF @@Error <> 0
????? Begin
??????? RollBack Tran
??????? RETURN -1
????? End
???? Else
????? Begin
??????? Commit Tran
??????? RETURN @totalRecord ---返回记录总数
????? End
END
?
GO
?
/****** 对象:? StoredProcedure [dbo].[up_Page2005V2_Join]??? 脚本日期: 05/21/2008 11:27:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[up_Page2005V2_Join]
??? @TableName varchar(150),
?@TotalPage int output??????????? --返回总页数
AS
BEGIN
?
???? Begin Tran --开始事务
?
??? Declare @sql nvarchar(4000);
?
??? IF @totalRecord<=0 begin
??????? --计算总记录数
?
??????? IF (@SqlWhere='' OR @sqlWhere=NULL)
??????????? SET @sql = 'select @totalRecord = count(*) from ' + @TableName
??????? else
??????????? SET @sql = 'select @totalRecord = count(*) from ' + @TableName + '? where ' + @sqlWhere
?
??????? EXEC sp_executesql @sql,' + @FIELDS + ' from ' + @TableName + ' where ' + @SqlWhere???
?
??? --处理页数超出范围情况
??? IF @PageIndex<=0
??????? SET @pageIndex = 1
?
??? IF @pageIndex>@TotalPage
??????? SET @pageIndex = @TotalPage
?
???? --处理开始点和结束点
??? Declare @StartRecord int
??? Declare @EndRecord int
?
??? SET @StartRecord = (@pageIndex-1)*@PageSize + 1
??? SET @EndRecord = @StartRecord + @pageSize - 1
?
??? --继续合成sql语句
??? SET @Sql = @Sql + ') as t where rowId between ' + Convert(varchar(50),@EndRecord)
???? print @sql
?
??? Exec(@Sql)
??? ---------------------------------------------------
??? IF @@Error <> 0
????? Begin
??????? RollBack Tran
??????? RETURN -1
????? End
???? Else
????? Begin
??????? Commit Tran
??????? RETURN @totalRecord ---返回记录总数
????? End
END
?
USE [game]
GO
/****** 对象:? StoredProcedure [dbo].[page]??? 脚本日期: 05/21/2008 11:37:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
?
CREATE proc [dbo].[page]
@RecordCount int output,
@ReturnCount bit,
@QueryStr nvarchar(1000)='table1',--表名、视图名、查询语句
@PageSize int=20,? --每页的大小(行数)
@PageCurrent int=2,? --要显示的页 从0开始
@FdShow nvarchar (2000)='*',--要显示的字段列表
@IdentityStr nvarchar (100)='id',--主键
@WhereStr nvarchar (2000)='1=1',
@FdOrder nvarchar(100)='desc' --排序??? 只能取desc或者asc
AS
?
SET nocount ON
?
declare
?
@sql nvarchar(2000)
?
IF @WhereStr = '' begin
?SET @WhereStr = '1=1'
end
?
IF @ReturnCount=1 begin
?declare @tsql nvarchar(200)
?SET @tsql=N'select @RecordCount = count(*) from ' + @QueryStr + ' where ' + @WhereStr
?exec sp_executesql @tsql,N'@RecordCount int output',@RecordCount output
end
?
IF @PageCurrent = 0 begin
?SET @sql = 'select top ' + cast(@PageSize AS nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' ' + @FdOrder
end
?
else begin
?IF upper(@FdOrder) = 'DESC' begin
? SET @sql = 'select top ' + cast(@PageSize AS nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' and ' + @IdentityStr + '< ( select min(' + @IdentityStr + ') from (select top ' + cast(@PageSize*@PageCurrent AS nvarchar(10)) + ' ' + @IdentityStr + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' desc) as t) order by ' + @IdentityStr + ' desc'
?end
?else begin
? SET @sql = 'select top ' + cast(@PageSize AS nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' and ' + @IdentityStr + '> ( select max(' + @IdentityStr + ') from (select top ' + cast(@PageSize*@PageCurrent AS nvarchar(10)) + ' ' + @IdentityStr + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' asc) as t) order by ' + @IdentityStr + ' asc'
?end
end
--print @sql
execute(@sql)
?
?
--/*-----存储过程 分页处理 孙伟 2005-03-28创建 -------*/
--/*-----存储过程 分页处理 浪尘 2008-9-1修改----------*/
--/*----- 对数据进行了2分处理使查询前半部分数据与查询后半部分数据性能相同 -------*/
?
ALTER PROCEDURE proc_paged_2part_selectMax
(
@tblName???? nvarchar(200),??????? ----要显示的表或多个表的连接
@fldName???? nvarchar(500) = '*',??? ----要显示的字段列表
@pageSize??? int = 10,??????? ----每页显示的记录个数
@page??????? int = 1,??????? ----要显示那一页的记录
@fldSort??? nvarchar(200) = NULL,??? ----排序字段列表或条件
@Sort??????? bit = 0,??????? ----排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ')
@strCondition??? nvarchar(1000) = NULL,??? ----查询条件,不需where
@ID??????? nvarchar(150),??????? ----主表的主键
@Dist???????????????? bit = 0,?????????? ----是否添加查询字段的 DISTINCT 默认0不添加/1添加
@pageCount??? int = 1 output,??????????? ----查询结果分页后的总页数
@Counts??? int = 1 output??????????????? ----查询到的记录数
)
AS
SET NOCOUNT ON
Declare @sqlTmp nvarchar(1000)??????? ----存放动态生成的SQL语句
Declare @strTmp nvarchar(1000)??????? ----存放取得查询结果总数的查询语句
Declare @strID???? nvarchar(1000)??????? ----存放取得查询开头或结尾ID的查询语句
?
Declare @strSortType nvarchar(10)??? ----数据排序规则A
Declare @strFSortType nvarchar(10)??? ----数据排序规则B
?
Declare @SqlSelect nvarchar(50)???????? ----对含有DISTINCT的查询进行SQL构造
Declare @SqlCounts nvarchar(50)????????? ----对含有DISTINCT的总数查询进行SQL构造
?
declare @timediff datetime? --耗时测试时间差
SELECT @timediff=getdate()
?
IF @Dist? = 0
begin
??? SET @SqlSelect = 'select '
??? SET @SqlCounts = 'Count(*)'
end
else
begin
??? SET @SqlSelect = 'select distinct '
??? SET @SqlCounts = 'Count(DISTINCT '+@ID+')'
end
?
IF @Sort=0
begin
??? SET @strFSortType=' ASC '
??? SET @strSortType=' DESC '
end
else
begin
??? SET @strFSortType=' DESC '
??? SET @strSortType=' ASC '
end
?
--------生成查询语句--------
--此处@strTmp为取得查询结果数量的语句
IF @strCondition IS NULL OR @strCondition=''???? --没有设置显示条件
begin
??? SET @sqlTmp =? @fldName + ' From ' + @tblName
??? SET @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName
??? SET @strID = ' From ' + @tblName
end
else
begin
??? SET @sqlTmp = + @fldName + 'From ' + @tblName + ' where (1>0) ' + @strCondition
??? SET @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName + ' where (1>0) ' + @strCondition
??? SET @strID = ' From ' + @tblName + ' where (1>0) ' + @strCondition
end
?
----取得查询结果总数量-----
exec sp_executesql @strTmp,N'@Counts int out ',@Counts out
declare @tmpCounts int
IF @Counts = 0
??? SET @tmpCounts = 1
else
??? SET @tmpCounts = @Counts
?
??? --取得分页总数
??? SET @pageCount=(@tmpCounts+@pageSize-1)/@pageSize
?
??? /**//**//**//**当前页大于总页数 取最后一页**/
??? IF @page>@pageCount
??????? SET @page=@pageCount
?
??? --/*-----数据分页2分处理-------*/
??? declare @pageIndex int --总数/页大小
??? declare @lastcount int --总数%页大小?
?
??? SET @pageIndex = @tmpCounts/@pageSize
??? SET @lastcount = @tmpCounts%@pageSize
??? IF @lastcount > 0
??????? SET @pageIndex = @pageIndex + 1
??? else
??????? SET @lastcount = @pagesize
?
??? --//***显示分页
??? IF @strCondition IS NULL OR @strCondition=''???? --没有设置显示条件
??? begin
??????? IF @pageIndex<2 OR @page<=@pageIndex / 2 + @pageIndex % 2?? --前半部分数据处理
??????????? begin
??????????????? IF @page=1
??????????????????? SET @strTmp=@SqlSelect+' top '+ CAST(@pageSize AS VARCHAR(4))+' '+ @fldName+' from '+@tblName
??????????????????????? +' order by '+ @fldSort +' '+ @strFSortType
??????????????? else
??????????????? begin
??????????????????? IF @Sort=1
??????????????????? begin
??????????????????? SET @strTmp=@SqlSelect+' top '+ CAST(@pageSize AS VARCHAR(4))+' '+ @fldName+' from '+@tblName
??????????????????????? +' where '+@ID+' <(select min('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) AS Varchar(20)) +' '+ @ID +' from '+@tblName
??????????????????????? +' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)'
??????????????????????? +' order by '+ @fldSort +' '+ @strFSortType
??????????????????? end
??????????????????? else
??????????????????? begin
??????????????????? SET @strTmp=@SqlSelect+' top '+ CAST(@pageSize AS VARCHAR(4))+' '+ @fldName+' from '+@tblName
??????????????????????? +' where '+@ID+' >(select max('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) AS Varchar(20)) +' '+ @ID +' from '+@tblName
??????????????????????? +' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)'
??????????????????????? +' order by '+ @fldSort +' '+ @strFSortType
??????????????????? end
??????????????? end
??????????? end
??????? else
??????????? begin
??????????? SET @page = @pageIndex-@page+1 --后半部分数据处理
??????????????? IF @page <= 1 --最后一页数据显示
??????????????????? SET @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount AS VARCHAR(4))+' '+ @fldName+' from '+@tblName
??????????????????????? +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
??????????????? else
??????????????????? IF @Sort=1
??????????????????? begin
??????????????????? SET @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize AS VARCHAR(4))+' '+ @fldName+' from '+@tblName
??????????????????????? +' where '+@ID+' >(select max('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount AS Varchar(20)) +' '+ @ID +' from '+@tblName
??????????????????????? +' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)'
??????????????????????? +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
??????????????????? end
??????????????????? else
??????????????????? begin
??????????????????? SET @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize AS VARCHAR(4))+' '+ @fldName+' from '+@tblName
??????????????????????? +' where '+@ID+' <(select min('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount AS Varchar(20)) +' '+ @ID +' from '+@tblName
??????????????????????? +' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)'
??????????????????????? +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
??????????????????? end
??????????? end
??? end
?
??? else --有查询条件
??? begin
??????? IF @pageIndex<2 OR @page<=@pageIndex / 2 + @pageIndex % 2?? --前半部分数据处理
??????? begin
??????????????? IF @page=1
??????????????????? SET @strTmp=@SqlSelect+' top '+ CAST(@pageSize AS VARCHAR(4))+' '+ @fldName+' from '+@tblName
??????????????????????? +' where 1=1 ' + @strCondition + ' order by '+ @fldSort +' '+ @strFSortType
??????????????? else IF(@Sort=1)
??????????????? begin
??????????????????? SET @strTmp=@SqlSelect+' top '+ CAST(@pageSize AS VARCHAR(4))+' '+ @fldName+' from '+@tblName
??????????????????????? +' where '+@ID+' <(select min('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) AS Varchar(20)) +' '+ @ID +' from '+@tblName
??????????????????????? +' where (1=1) ' + @strCondition +' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)'
??????????????????????? +' '+ @strCondition +' order by '+ @fldSort +' '+ @strFSortType
??????????????? end
??????????????? else
??????????????? begin
??????????????????? SET @strTmp=@SqlSelect+' top '+ CAST(@pageSize AS VARCHAR(4))+' '+ @fldName+' from '+@tblName
??????????????????????? +' where '+@ID+' >(select max('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) AS Varchar(20)) +' '+ @ID +' from '+@tblName
??????????????????????? +' where (1=1) ' + @strCondition +' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)'
??????????????????????? +' '+ @strCondition +' order by '+ @fldSort +' '+ @strFSortType
??????????????? end
??????? end
??????? else
??????? begin
??????????? SET @page = @pageIndex-@page+1 --后半部分数据处理
??????????? IF @page <= 1 --最后一页数据显示
??????????????????? SET @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount AS VARCHAR(4))+' '+ @fldName+' from '+@tblName
??????????????????????? +' where (1=1) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
??????????? else IF(@Sort=1)
??????????????????? SET @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize AS VARCHAR(4))+' '+ @fldName+' from '+@tblName
??????????????????????? +' where '+@ID+' >(select max('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount AS Varchar(20)) +' '+ @ID +' from '+@tblName
??????????????????????? +' where (1=1) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)'
??????????????????????? +' '+ @strCondition+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
??????????? else
??????????????????? SET @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize AS VARCHAR(4))+' '+ @fldName+' from '+@tblName
??????????????????????? +' where '+@ID+' <(select min('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount AS Varchar(20)) +' '+ @ID +' from '+@tblName
??????????????????????? +' where (1=1) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)'
??????????????????????? +' '+ @strCondition+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
??????? end
??? end
?
------返回查询结果-----
exec sp_executesql @strTmp
SELECT datediff(ms,@timediff,ge
tdate()) AS 耗时
--print @strTmp
SET NOCOUNT OFF
GO
?
调用方法：exec proc_paged_2part_selectMax ‘tb_testTable’,'ID,userName,userPWD,userEmail’,10,100000,’ID’,null,0
----STR的用法CopyCREATE TABLE t1 (Col1 float,Col2 float,Col3 float)INSERT INTO t1 Values(123.45,123.45,123.45)SELECT STR(Col1,6,1),STR(Col2,2),STR(Col3)FROM t1
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
