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
总结以上是内存溢出为你收集整理的SQLServer存储过程详解全部内容，希望文章能够帮你解决SQLServer存储过程详解所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）