引用了zjcxc(邹健)的分页存储过程有问题,帮忙看下?

yumanqing 2009-04-03 03:42:12
引用了引用了zjcxc(邹健)的分页存储过程有问题,我改了下就OK了,谁能解释下:
存储过程如下
http://blog.csdn.net/zjcxc/archive/2003/12/29/20080.aspx
IF OBJECT_ID(N'dbo.p_show') IS NOT NULL
DROP PROCEDURE dbo.p_show
GO

/**//*--实现分页的通用存储过程

显示指定表、视图、查询结果的第X页
对于表中主键或标识列的情况,直接从原表取数查询,其它情况使用临时表的方法
如果视图或查询结果中有主键,不推荐此方法
如果使用查询语句,而且查询语句使用了order by,则查询语句必须包含top 语句

最后更新时间: 2008.01.20
--邹建 2003.09(引用请保留此信息)--*/

/**//*--调用示例
EXEC dbo.p_show
@QueryStr = N'tb',
@PageSize = 5,
@PageCurrent = 3,
@FdShow = 'id, colid, name',
@FdOrder = 'colid, name'
select id, colid from tb
order by colid, name


EXEC dbo.p_show
@QueryStr = N'
SELECT TOP 100 PERCENT
*
FROM dbo.sysobjects
ORDER BY xtype',
@PageSize = 5,
@PageCurrent = 2,
@FdShow = 'name, xtype',
@FdOrder = 'xtype, name'
--*/
CREATE PROC dbo.p_show
@QueryStr nvarchar(4000), -- 表名、视图名、查询语句
@PageSize int=10, -- 每页的大小(行数)
@PageCurrent int=1, -- 要显示的页
@FdShow nvarchar (4000) = N'', -- 要显示的字段列表,如果查询结果不需要标识字段,需要指定此值,且不包含标识字段
@FdOrder nvarchar (1000) = N'' -- 排序字段列表
AS
SET NOCOUNT ON
DECLARE
@FdName sysname, --表中的主键或表、临时表中的标识列名
@Id1 sysname, --开始和结束的记录号
@Id2 sysname,
@Obj_ID int --对象ID

--表中有复合主键的处理
DECLARE
@strfd nvarchar(2000), --复合主键列表
@strjoin nvarchar(4000), --连接字段
@strwhere nvarchar(2000) --查询条件


SELECT
@Obj_ID = OBJECT_ID(@QueryStr),
@FdShow = CASE
WHEN @FdShow > N'' THEN N' ' + @FdShow
ELSE N' *'
END,
@FdOrder = CASE
WHEN @FdOrder > N'' THEN N' ORDER BY ' + @FdOrder
ELSE N' '
END,
@QueryStr = CASE
WHEN @Obj_ID IS NULL THEN N' (' + @QueryStr + N')A'
ELSE N' ' + @QueryStr
END

-- 如果显示第一页,可以直接用 top 来完成
IF @PageCurrent = 1
BEGIN
SELECT
@Id1 = CAST(@PageSize as varchar(20))
EXEC(N'
SELECT TOP ' + @Id1 + N'
' + @FdShow + N'
FROM ' + @QueryStr + N'
' + @FdOrder
)
RETURN
END

-- 如果是表, 则检查表中是否有标识更或主键
IF @Obj_ID IS NULL OR OBJECTPROPERTY(@Obj_ID, 'IsTable') = 0
GOTO lb_usetemp
ELSE
BEGIN
SELECT
@Id1 = CAST(@PageSize as varchar(20)),
@Id2 = CAST((@PageCurrent - 1) * @PageSize as varchar(20))

-- 标识列
SELECT
@FdName = name
FROM dbo.syscolumns
WHERE id = @Obj_ID
AND status = 0x80
IF @@ROWCOUNT = 0 --如果表中无标识列,则检查表中是否有主键
BEGIN
DECLARE
@pk_number int

SELECT
@strfd = N'',
@strjoin = N'',
@strwhere = N''

SELECT
@strfd = @strfd
+ N',' + QUOTENAME(name),
@strjoin = @strjoin
+ N' AND A.' + QUOTENAME(name)
+ N'=B.' + QUOTENAME(name),
@strwhere = @strwhere
+ N' AND B.' + QUOTENAME(name) + N' IS NULL'
FROM(
SELECT
IX.id, IX.indid,
IXC.colid, ixc.keyno,
C.name
FROM dbo.sysobjects O,
dbo.sysindexes IX,
dbo.sysindexkeys IXC,
dbo.syscolumns C
WHERE O.parent_obj = @Obj_ID
AND O.xtype = 'PK'
AND O.name = IX.name
AND IX.id = @Obj_ID
AND IX.id = IXC.id
AND IX.indid = IXC.indid
AND IXC.id = C.id
AND IXC.colid = C.colid
)A
ORDER BY keyno

SELECT
@pk_number = @@ROWCOUNT,
@strfd = STUFF(@strfd, 1, 1, N''),
@strjoin = STUFF(@strjoin, 1, 5, N''),
@strwhere = STUFF(@strwhere, 1, 5, N'')

IF @pk_number = 0
GOTO lb_usetemp --如果表中无主键,则用临时表处理
ELSE IF @pk_number = 1
BEGIN
SELECT
@FdName = @strfd
GOTO lb_useidentity -- 使用单一主键
END
ELSE
GOTO lb_usepk -- 使用复合主键
END
END

/**//*--使用标识列或主键为单一字段的处理方法--*/
lb_useidentity:
EXEC(N'
SELECT TOP ' + @Id1 + N'
' + @FdShow + N'
FROM '+@QueryStr + N'
WHERE ' + @FdName + ' NOT IN(
SELECT TOP ' + @Id2 + N'
' + @FdName + '
FROM ' + @QueryStr + N'
' + @FdOrder + N')
' + @FdOrder + N'
')
RETURN

/**//*--表中有复合主键的处理方法--*/
lb_usepk:
EXEC(N'
SELECT
' + @FdShow + N'
FROM(
SELECT TOP ' + @Id1 + N'
A.*
FROM ' + @QueryStr + N' A
LEFT JOIN(
SELECT TOP ' + @Id2 + N'
' + @strfd + N'
FROM ' + @QueryStr + N'
' + @FdOrder + N'
)B
ON ' + @strjoin + N'
WHERE ' + @strwhere + N'
' + @FdOrder + N'
)A
' + @FdOrder + N'
')
RETURN

/**//*--用临时表处理的方法--*/
lb_usetemp:
SELECT
@FdName = QUOTENAME(N'ID_' + CAST(NEWID() as varchar(40))),
@Id1 = CAST(@PageSize * (@PageCurrent-1) as varchar(20)),
@Id2 = CAST(@PageSize * @PageCurrent-1 as varchar(20))

EXEC(N'
SELECT
' + @FdName + N' = IDENTITY(int, 0, 1),
' + @FdShow + N'
INTO #tb
FROM(
SELECT TOP 100 PERCENT
*
FROM ' + @QueryStr + N'
' + @FdOrder + N'
)A
' + @FdOrder + N'

SELECT
' + @FdShow + N'
FROM #tb
WHERE ' + @FdName + ' BETWEEN ' + @Id1 + ' AND ' + @Id2 + N'
'
)
GO


我在C#中调用时候写

try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT * ");
strSql.Append(" FROM Customer ");
strSql.Append(" WHERE "+this.LinkSql(cusList));
SqlParameter[] parameters ={
new SqlParameter("@QueryStr",SqlDbType.Char,4000), //改为3000
new SqlParameter("@PageSize",SqlDbType.Int),
new SqlParameter("@PageCurrent",SqlDbType.Int),
new SqlParameter("@FdShow",SqlDbType.Char,4000), //改为200
new SqlParameter("@FdOrder",SqlDbType.Char,1000), //改为200
};
parameters[0].Value = ”SELECT * FROM Customer“;
parameters[1].Value = 30;
parameters[2].Value = PageCurrent;
parameters[3].Value = "";
parameters[4].Value = " cCode desc ";

DataSet ds = DbHelperSQL.RunProcedure("PageQuery", parameters, "CustomerList");
return ds;
}
catch (SqlException ex)
{
throw new Exception(ex.Message);
}


如果存储过程参数不改小一点,老是报语法错误或者“除非同时指定了 TOP,否则 ORDER BY 子句在视图、内嵌函数、派生表和子查询中无效。”
虽然改下可以了,但是实在不明白为什么?这样能运行稳定吗?
...全文
126 8 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
等不到来世 2009-04-07
  • 打赏
  • 举报
回复
SqlDbType.Char --> SqlDbType.NVarChar
wts_net 2009-04-07
  • 打赏
  • 举报
回复
学习
Teng_s2000 2009-04-07
  • 打赏
  • 举报
回复
学习啦!
  • 打赏
  • 举报
回复
在select 中指定了 top 和order by,
那这个参数的每页数量和排序字段又有什么关系呢?加入不一致呢?

貌似你理解完整啊,@QueryStr = N'SELECT TOP 100 PERCENT * FROM dbo.sysobjects ORDER BY xtype'
相当于存储过程中的第一个参数'tb' 也就是把这个查询的结果集作为一张表,

这个参数的每页数量和排序字段,是在存储过程中处理的
yumanqing 2009-04-03
  • 打赏
  • 举报
回复
我看邹健老师的调用示例

EXEC dbo.p_show
@QueryStr = N'
SELECT TOP 100 PERCENT
*
FROM dbo.sysobjects
ORDER BY xtype',
@PageSize = 5,
@PageCurrent = 2,
@FdShow = 'name, xtype',
@FdOrder = 'xtype, name'
--*/



在select 中指定了 top 和order by,
那这个参数的每页数量和排序字段又有什么关系呢?加入不一致呢?
dobear_0922 2009-04-03
  • 打赏
  • 举报
回复
SqlDbType.Char --> SqlDbType.VarChar
dtxh168 2009-04-03
  • 打赏
  • 举报
回复
除非同时指定了 TOP,否则 ORDER BY 子句在视图、内嵌函数、派生表和子查询中无效

很明显,你在子查询中使用了order by 但是前面的select 没有使用top

解决方法,要么把order by 去掉,要么加top n
yygyogfny 2009-04-03
  • 打赏
  • 举报
回复
奇怪

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧