提供给大家一个分页存储过程，第一次公开！不好请直接批评！随便散点分

Question

本人自己写的一个分页存储过程，因为感觉网上其他的都太麻烦，必须指定主键，必须指定排序，等等，要传的参数太多。太烦！
本分页存储过程支持多表链接查询(left join,join,right join等等),支持别名,支持嵌套SELECT语句，支持任何字符集
/***************************************************************
分页存储过程 
Create BY Tellov 2008-05-28
[email protected]
注意:使用本存储过程,SQL语句中必须要带有Order BY 子句支持多表链接查询,支持别名,支持嵌套SELECT语句
注:语句中如果带有group by ,则页总数和记录数无法正确统计-- Modify BY Tellov 2008-12-10 修正了多字段排序且序不同时分页会出现返回数据集不正确的问题
-- Modify By Tellov 2009-04-10 将@temp从varchar(4000)改为varchar(max),修正了SQL语句太长会出现超过4000之外的字符丢失的问题。
***************************************************************
参数说明:
1.@sql 查询语句
2.@CurrentPage 当前页
3.@PageSize 页大小
4.@Counts 查询到的记录总数
5.@pageCount 页总数
***************************************************************/
ALTER PROCEDURE [dbo].[Sp1_Exec_Page]
(
@Sql nvarchar(4000), -- 要执行的SQL语句
@CurrentPage int = 1, -- 当前页
@PageSize int = 30, -- 每页纪录数
@counts int output, -- 纪录总数
@pageCount int output -- 页总数
)
--WITH ENCRYPTION
ASSet Lock_TimeOut 2000declare @sort nvarchar(500)
declare @variableName nvarchar(300)
declare @setVariableName nvarchar(2000)
declare @where nvarchar(4000)
declare @temp nvarchar(max)
declare @tempwhere nvarchar(2000)
select @sql = replace(@sql,'  ',' ')
select @where = ''
select @setVariableName = ''
select @variableName = ''
-- 取出orderBy
if CHARINDEX(' order by ',@sql) = 0
begin
select '使用此存储过程时查询语句中必须带上order by 子句';
end
else
begin
declare @wherelocation int,@orderbylocation int,@groupbylocation int -- 取最后一个order by 的位置
select @orderbylocation = CHARINDEX(' order by ',@sql)
while CHARINDEX(' order by ',@sql,@orderbylocation+10) <> 0
begin
select @orderbylocation = CHARINDEX(' order by ',@sql,@orderbylocation+10)
end -- 默认当前页
IF @CurrentPage < 1
SET @CurrentPage = 1 -- 获取总行数
select @temp = left(@Sql,@orderbylocation) 
select @temp = 'Select @counts = count(1) from ( ' + @temp + ') as __tt'
--print @temp
exec sp_executesql @temp,N'@counts int output',@counts output -- 获取总分页数
If @counts < @pageSize
Select @pageCount = 1
else
Select @pageCount = ((@Counts-1) / @pageSize) +1 -- 提高效率,如果是第一页直接返回
if @CurrentPage = 1
begin
declare @returntemp nvarchar(4000)
select @returntemp = 'select top ' + convert(nvarchar(20),@pageSize) + substring(@sql,CHARINDEX('select ',@sql) + 6,len(@sql))
exec(@returntemp)
--print @returntemp
end
else
begin
-- 取最后一个WHERE的位置
select @wherelocation = CHARINDEX(' where ',@sql)
while @wherelocation <> 0 and CHARINDEX(' on ',@sql,@wherelocation + 7) <> 0
begin
select @wherelocation = CHARINDEX(' where ',@sql,@wherelocation + 7)
end -- 取最后一个group by 的位置
select @groupbylocation = CHARINDEX(' group by ',@sql)
while CHARINDEX(' group by ',@sql,@groupbylocation+10) <> 0
begin
select @groupbylocation = CHARINDEX(' group by ',@sql,@groupbylocation+10)
end select @sort = substring(@sql,@orderbylocation+10,len(@sql)) -- 生成执行SQL时必须的语句
select @sort = @sort + ','
select @tempwhere = ''
while CHARINDEX(',',@sort) > 0
begin 
select @temp = substring(@sort,0,CHARINDEX(',',@sort))
select @temp = replace(@temp,' desc','')
select @temp = replace(@temp,' asc','')
select @setVariableName = @setVariableName + '@' + ltrim(replace(@temp,'.','')) + '=Convert(nvarchar(300),' + @temp + ',21),'
select @variableName = @variableName + '@' + ltrim(replace(@temp,'.','')) + ' nvarchar(300),'
--print @variableName
if @tempwhere <> ''
begin
select @where = @where + ' or (' + right(@tempwhere,len(@tempwhere)-4)
end select @where = @where + ' and ' + @temp
if CHARINDEX(' desc',@sort) <> 0
begin
select @where = @where + ' <= @' +  ltrim(replace(@temp,'.',''))
end
else
begin
select @where = @where + ' >= @' +  ltrim(replace(@temp,'.',''))
end

if @tempwhere <> ''
begin
select @where = @where + ')'
end select @tempwhere = @tempwhere + ' and ' + @temp + ' = @' + ltrim(replace(@temp,'.','')) select @sort = substring(@sort,CHARINDEX(',',@sort)+1,len(@sort))
end select @variableName = left(@variableName,len(@variableName)-1)
select @setVariableName = left(@setVariableName,len(@setVariableName)-1)
select @where = '(' + substring(@where,6,len(@where))+ ')' --print @where
--print @setVariableName
--print @variableName -- 设置RowCount
declare @strStartRow int
Select @strStartRow = ((@CurrentPage - 1)*@PageSize + 1)
Set RowCount @strStartRow --print @sql -- 最终执行Sql
select @temp = 'DECLARE ' + @variableName + '
SET ROWCOUNT ' + Convert(nvarchar(20),@strStartRow) + '
Select ' + @setVariableName + substring(@Sql,charindex(' from ',@Sql),8000) + '
SET ROWCOUNT ' + Convert(nvarchar(20),@PageSize) + '
'
--print @temp
--print @sql
--print @wherelocation if @wherelocation <> 0
select @temp = @temp + STUFF(@sql,@wherelocation+7,0,@where + ' and ')
else
begin
if @groupbylocation <> 0
select @temp = @temp + STUFF(@sql,@groupbylocation + 1,0,' where ' + @where + ' ')
else if @orderbylocation <> 0
select @temp = @temp + STUFF(@sql,@orderbylocation + 1,0,' where ' + @where + ' ')
end exec(@temp) --print @temp
end
end

Answer 1

好多代码呵呵 看看我的
ALTER PROC PageView
@tbname sysname, --要分页显示的表名
@FieldKey sysname, --用于定位记录的主键(惟一键)字段,只能是单个字段
@PageCurrent int=1, --要显示的页码
@PageSize int=10, --每页的大小(记录数)
@FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar(1000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
--用于指定排序顺序
@Where nvarchar(1000)='', --查询条件
@PageCount int OUTPUT --总页数
AS
DECLARE @sql nvarchar(4000)
SET NOCOUNT ON
--检查对象是否有效
IF OBJECT_ID(@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(@Where,N'')=N''
SET @Where=N''
ELSE
SET @Where=N'WHERE ('+@Where+N')'--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
IF @PageCount IS NULL
BEGIN
SET @sql=N'SELECT @PageCount=COUNT(*)'
+N' FROM '+@tbname
+N' '+@Where
EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT
SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
END--计算分页显示的TOPN值
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=@PageSize,
@TopN1=@PageCurrent*@PageSize--第一页直接显示
IF @PageCurrent=1
EXEC(N'SELECT TOP '+@TopN
+N' '+@FieldShow
+N' FROM '+@tbname
+N' '+@Where
+N' '+@FieldOrder)
ELSE
BEGIN
SELECT @PageCurrent=@TopN1,
@sql=N'SELECT @n=@n-1,@s=CASE WHEN @n<'+@TopN
+N' THEN @s+N'',''+QUOTENAME(RTRIM(CAST('+@FieldKey
+N' as varchar(8000))),N'''''''') ELSE N'''' END FROM '+@tbname
+N' '+@Where
+N' '+@FieldOrder
SET ROWCOUNT @PageCurrent
EXEC sp_executesql @sql,
N'@n int,@s nvarchar(4000) OUTPUT',
@PageCurrent,@sql OUTPUT
SET ROWCOUNT 0
IF @sql=N''
EXEC(N'SELECT TOP 0'
+N' '+@FieldShow
+N' FROM '+@tbname)
ELSE
BEGIN
SET @sql=STUFF(@sql,1,1,N'')
--执行查询
EXEC(N'SELECT TOP '+@TopN
+N' '+@FieldShow
+N' FROM '+@tbname
+N' WHERE '+@FieldKey
+N' IN('+@sql
+N') '+@FieldOrder)
END
END

Answer 2

关键 你使用 in,not in，性能极差

Answer 3

就是拼SQL语句太累了。如果条件特别多，转换很烦躁

Answer 4

支持一下...没时间细看...-- Modify By Tellov 2009-04-10 将@temp从varchar(4000)改为varchar(max),修正了SQL语句太长会出现超过4000之外的字符丢失的问题。
-------------------
仅能用于SQL Server 2005以上版本...通用性不够好吧...

Answer 5

我也展示一下我的代码，可能更简单,SQL2005if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ListTextFriendLink]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_ListTextFriendLink]
GO
Create PROCEDURE [dbo].[sp_ListTextFriendLink]--读取全站的文字友情链接  spFE_ListTextFriendLink 1,1,0
@ChanelID int,--频道ID
@PageIndex int =1,--start with 1
@PageRowCount int=0
AS
begin
SELECT * from 
(
select * ,row_number() over (order by ID asc) as RowNum 
from TableName where ChanelID=@ChanelID
) TempPK where RowNum>(@PageIndex-1)*@PageRowCount and RowNum<=@PageIndex*@PageRowCount             
--2) get counter          
Select count(1)as RecordCount from TableName where ChanelID=@ChanelID
end
go

Answer 6

用拼的SQL总的还是不太好。我以前试过一个语句用拼的方法，可能是条件太多吧。结果拼下来已经大于数据库所规定的最大字符数了。

Answer 7

这种情况直到我用了快一年才发生，因为SQL语句太长，一条SQL突破了3000个字符了。所以后来修正了，把@temp改成了nvarchar(max)不过改成nvarchar(max)之后，只能用SQL2005以上的版本才能跑了。如果将@temp设置成为nvarchar(4000),那2000也可以跑的，只要你不要也弄个3000以上的SQL语句去查表就行(这种情况还是非常少见的)。

Answer 8

楼主啊，我之前做的是统计局的项目，SQL语句基本上都是非常长的，有些是因为自动生成的SQL去进行统计报表分析，而且数据量非常大，别说3000个字符，5000个字符以上都有呢，所以考虑东西不只是自己遇到过的。当然可能一般的项目也不会有很长的，不过还是支持楼主贴出来分享。

Answer 9

不过SQL05 直接带着分页技术
大家不觉得可以考虑下马？
Such as
：
Set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER  procedure [dbo].[SP_AllDiskPaging] 
(@startIndex int,
@endIndex int)
asbegin
 with temptbl as (
SELECT ROW_NUMBER() OVER (ORDER BY DriID desc)AS Row, * from Diskdata where AllFlag=1  )
 SELECT * FROM temptbl where Row between @startIndex and @endIndex
end

Answer 10

发现问题:
使用此存储过程时查询语句中必须带上order by 子句'因为你的程序逻辑问题,order by 必须使用 ' order by '
即:'空格order空格by空格'
才能匹配
假设我的sql语句中order by中间多了一个空格,将无法匹配.