请教下各位高手:我这里有一个通用分页存储过程,代码如下CREATE PROCEDURE sp_Paging
@tblName varchar(255) , -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 主关键字字段名
@orderfield varchar(255)='', -- 排序的字段名
@pageSize int = 10, -- 页尺寸
@pageIndex int = 1, -- 页码
@recordCount int = 0 output, -- 返回记录总数
@pageCount int = 0 output, --分页总数
@orderType int = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL nvarchar(3000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型 begin
if @strWhere !=''
set @strSQL = 'select @recordCount = count(*) from ' + @tblName + ' where '+@strWhere
else
set @strSQL = 'select @recordCount = count(*) from ' + @tblName
end exec sp_executesql @strSQL,N'@recordCount int out ',@recordCount out if @recordCount % @pageSize = 0
set @pageCount = @recordCount / @pageSize
else
set @pageCount = @recordCount / @pageSize + 1
--以上代码的意思是计算出记录是总数和求出总共的页数 begin
if @orderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by ' + @orderfield +' desc'
--如果@orderType不是0,就执行降序,这句很重要!
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by ' + @orderfield +' asc'
end if @pageIndex = 1
begin
if @strWhere != '' set @strSQL = 'select top ' + str(@pageSize) +' '+@strGetFields+ ' from ' + @tblName + ' where ' + @strWhere + ' ' + @strOrder
else set @strSQL = 'select top ' + str(@pageSize) +' '+@strGetFields+ ' from '+ @tblName + ' '+ @strOrder
--如果是第一页就执行以上代码,这样会加快执行速度
end
else
begin
if @strWhere != ''
set @strSQL = 'select top ' + str(@pageSize) +' '+@strGetFields+ ' from '
+ @tblName + ' where ' + @fldName + '' + @strTmp + '('
+ substring(@fldName, charindex('.',@fldName)+1, len(@fldName)) + ') from (select top ' + str((@pageIndex-1)*@pageSize) + ' '
+ @fldName + ' from ' + @tblName + ' where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
else
set @strSQL = 'select top ' + str(@pageSize) +' '+@strGetFields+ ' from '
+ @tblName + ' where ' + @fldName + '' + @strTmp + '('
+ substring(@fldName, charindex('.',@fldName)+1, len(@fldName)) + ') from (select top ' + str((@pageIndex-1)*@pageSize) + ' '
+ @fldName + ' from ' + @tblName + '' + @strOrder + ') as tblTmp)'
+ @strOrder
end
end
--print @strSQL
exec sp_executesql @strSQL
GO但发现一个奇怪的问题,当pageIndex>=2时,就抛错
然后我把sql语句打印出来到企业管理器里执行是没有问题的,但是到查询分析器就跑错
麻烦高手能帮我看下,谢谢
@tblName varchar(255) , -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 主关键字字段名
@orderfield varchar(255)='', -- 排序的字段名
@pageSize int = 10, -- 页尺寸
@pageIndex int = 1, -- 页码
@recordCount int = 0 output, -- 返回记录总数
@pageCount int = 0 output, --分页总数
@orderType int = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL nvarchar(3000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型 begin
if @strWhere !=''
set @strSQL = 'select @recordCount = count(*) from ' + @tblName + ' where '+@strWhere
else
set @strSQL = 'select @recordCount = count(*) from ' + @tblName
end exec sp_executesql @strSQL,N'@recordCount int out ',@recordCount out if @recordCount % @pageSize = 0
set @pageCount = @recordCount / @pageSize
else
set @pageCount = @recordCount / @pageSize + 1
--以上代码的意思是计算出记录是总数和求出总共的页数 begin
if @orderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by ' + @orderfield +' desc'
--如果@orderType不是0,就执行降序,这句很重要!
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by ' + @orderfield +' asc'
end if @pageIndex = 1
begin
if @strWhere != '' set @strSQL = 'select top ' + str(@pageSize) +' '+@strGetFields+ ' from ' + @tblName + ' where ' + @strWhere + ' ' + @strOrder
else set @strSQL = 'select top ' + str(@pageSize) +' '+@strGetFields+ ' from '+ @tblName + ' '+ @strOrder
--如果是第一页就执行以上代码,这样会加快执行速度
end
else
begin
if @strWhere != ''
set @strSQL = 'select top ' + str(@pageSize) +' '+@strGetFields+ ' from '
+ @tblName + ' where ' + @fldName + '' + @strTmp + '('
+ substring(@fldName, charindex('.',@fldName)+1, len(@fldName)) + ') from (select top ' + str((@pageIndex-1)*@pageSize) + ' '
+ @fldName + ' from ' + @tblName + ' where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
else
set @strSQL = 'select top ' + str(@pageSize) +' '+@strGetFields+ ' from '
+ @tblName + ' where ' + @fldName + '' + @strTmp + '('
+ substring(@fldName, charindex('.',@fldName)+1, len(@fldName)) + ') from (select top ' + str((@pageIndex-1)*@pageSize) + ' '
+ @fldName + ' from ' + @tblName + '' + @strOrder + ') as tblTmp)'
+ @strOrder
end
end
--print @strSQL
exec sp_executesql @strSQL
GO但发现一个奇怪的问题,当pageIndex>=2时,就抛错
然后我把sql语句打印出来到企业管理器里执行是没有问题的,但是到查询分析器就跑错
麻烦高手能帮我看下,谢谢
解决方案 »
- 怎么做这种多表查询带条件判断的
- 有没有数据库熟悉的朋友想来微软外包干
- 视图问题?
- 帮我写条插入命令
- java连接sqlserver2000的问题!到主机的tcp/ip 连接失败
- 求助数据字段的替换
- 急啊,我想重建日志文件,有什么办法????????帮帮啊,给200分,解决后再加
- 我正在公网机房,我的SQL只可以管道连接,不可TCP/IP,我按照大力的方法设置了代理,端口,采用IP,还是NO,我在线,急啊
- 小弟脑拙,请各位高手救急!!!1
- 请问谁会给出最好,最简单,最容易实际的方法?
- 新手第一次使用触发器,不太会用,请教几个问题
- Microsoft SQL Server 2008 R2 SP2 - Express Edition安装问题
第 1 行: ' ' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: ' ' 附近有语法错误。
但print这个sql,然后到企业管理器中执行是没有问题的
第 1 行: ' ' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: ' ' 附近有语法错误。
但print这个sql,然后在企业管理器中执行是没有问题的,在查询分析器就抛以上错误
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 主关键字字段名
@orderfield varchar(255)='', -- 排序的字段名
@pageSize int = 10, -- 页尺寸
@pageIndex int = 1, -- 页码
@recordCount int = 0 output, -- 返回记录总数
@pageCount int = 0 output, --分页总数
@orderType int = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where) 这里定义的东西不需要赋初始值 @tblName varchar(255) , -- 表名
@strGetFields varchar(1000) , -- 需要返回的列
@fldName varchar(255), -- 主关键字字段名
@orderfield varchar(255), -- 排序的字段名
@pageSize int , -- 页尺寸
@pageIndex int , -- 页码
@recordCount int output, -- 返回记录总数
@pageCount int output, --分页总数
@orderType int , -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) -- 查询条件 (注意: 不要加 where) 这些值得在调用时传入.exec proc_name 跟你需要的变量 '' , ''--参照下面的写法.
D. 使用 OUTPUT 参数
OUTPUT 参数允许外部过程、批处理或多条 Transact-SQL 语句访问在过程执行期间设置的某个值。下面的示例创建一个存储过程 (titles_sum),并使用一个可选的输入参数和一个输出参数。首先,创建过程:USE pubs
GO
IF EXISTS(SELECT name FROM sysobjects
WHERE name = 'titles_sum' AND type = 'P')
DROP PROCEDURE titles_sum
GO
USE pubs
GO
CREATE PROCEDURE titles_sum @@TITLE varchar(40) = '%', @@SUM money OUTPUT
AS
SELECT 'Title Name' = title
FROM titles
WHERE title LIKE @@TITLE
SELECT @@SUM = SUM(price)
FROM titles
WHERE title LIKE @@TITLE
GO接下来,将该 OUTPUT 参数用于控制流语言。 说明 OUTPUT 变量必须在创建表和使用该变量时都进行定义。
参数名和变量名不一定要匹配,不过数据类型和参数位置必须匹配(除非使用 @@SUM = variable 形式)。 DECLARE @@TOTALCOST money
EXECUTE titles_sum 'The%', @@TOTALCOST OUTPUT
IF @@TOTALCOST < 200
BEGIN
PRINT ' '
PRINT 'All of these titles can be purchased for less than $200.'
END
ELSE
SELECT 'The total cost of these titles is $'
+ RTRIM(CAST(@@TOTALCOST AS varchar(20)))下面是结果集:Title Name
------------------------------------------------------------------------
The Busy Executive's Database Guide
The Gourmet Microwave
The Psychology of Computer Cooking(3 row(s) affected)Warning, null value eliminated from aggregate.
All of these titles can be purchased for less than $200.
然后在查询分析器中执行
EXEC sp_Paging
@tblName = 'Employees',
@strGetFields = '*',
@fldName = 'Employeeid',
@orderfield = 'Employeeid',
@pageSize = '3',
@pageIndex = '2',
@recordCount = null,
@pageCount = null,
@orderType = '1',
@strWhere = ''使用northwind库
错误依然是
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: ' ' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: ' ' 附近有语法错误。
@tbname sysname, --要分页显示的表名
@FieldKey nvarchar(1000), --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段
@PageCurrent int=1, -->0表示要显示的页码,如果为0表示仅清理缓存数据的临时表,不返回数据,其他值代表重建缓存数据的临时表
@PageSize int=10, --每页的大小(记录数)
@FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@Where nvarchar(1000)='', --查询条件
@UserName sysname='', --调用查询的用户名
@PageCount int OUTPUT --总页数
AS
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(@PageSize,0)<1 SET @PageSize=10
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
IF ISNULL(@Where,N'')=N''
SET @Where=N''
ELSE
SET @Where=N'WHERE ('+@Where+N')'--分页数据缓存临时表状态检测
DECLARE @tempTable sysname,@TempField sysname,@TempTableDate datetime
SET @tempTable=QUOTENAME(N'##'
+RTRIM(LEFT(HOST_NAME(),50))
+N'_'+RTRIM(LEFT(CASE WHEN ISNULL(@UserName,N'')=N'' THEN SUSER_SNAME() ELSE @UserName END,50))
+N'_'+RTRIM(@tbname))SELECT @TempField=QUOTENAME(c.name),
@TempTableDate=DATEADD(Hour,1,o.crdate) --临时表的有效缓存时间为1小时,创建时间超过1小时的临时表会被重建
FROM tempdb..sysobjects o,tempdb..syscolumns c
WHERE o.id=c.id
AND o.id=OBJECT_ID(N'tempdb..'+@tempTable)
AND c.status=0x80
IF @@ROWCOUNT>0
IF ISNULL(@PageCurrent,0)<1 OR @TempTableDate<GETDATE()
BEGIN
EXEC('DROP TABLE '+@tempTable)
IF @PageCurrent=0 RETURN
END
ELSE
GOTO lb_TempTable_Created
ELSE
SELECT @TempField=QUOTENAME(NEWID())--创建分页数据缓存临时表
EXEC(N'SELECT *,IDENTITY(decimal(18,0),0,1) as '+@TempField
+N' INTO '+@tempTable
+N' FROM(SELECT TOP 100 PERCENT '+@FieldKey
+N' FROM '+@tbname
+N' '+@Where
+N' ORDER BY NEWID())a')
SET @PageCount=(@@ROWCOUNT+@PageSize-1)/@PageSize
GOTO lb_ShowDatalb_TempTable_Created:
--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
IF @PageCount IS NULL
BEGIN
DECLARE @sql nvarchar(4000)
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
ENDlb_ShowData:
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1--计算分页显示的TOPN值
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=(@PageCurrent-1)*@PageSize,
@TopN1=@PageCurrent*@PageSize-1--生成主键(惟一键)处理条件
DECLARE @Field sysname
SET @Where=N''
WHILE CHARINDEX(N',',@FieldKey)>0
SELECT @Field=LEFT(@FieldKey,CHARINDEX(N',',@FieldKey)-1),
@FieldKey=STUFF(@FieldKey,1,CHARINDEX(N',',@FieldKey),N''),
@Where=@Where
+N' AND a.'+@Field+N'=b.'+@Field,
@FieldShow=REPLACE(@FieldShow,@Field,N'a.'+@Field)
SELECT @Where=STUFF(@Where+N' AND a.'+@FieldKey+N'=b.'+@FieldKey,1,5,N''),
@FieldShow=REPLACE(@FieldShow,@FieldKey,N'a.'+@FieldKey)
IF @FieldShow=N'*' SET @FieldShow=N'a.*'--执行查询
EXEC(N'SELECT '+@FieldShow
+N' FROM '+@tbname
+N' a,'+@tempTable
+N'b WHERE (b.'+@TempField
+N' BETWEEN '+@TopN
+N' AND '+@TopN1
+N') AND ('+@Where
+N') ORDER BY b.'+@TempField)
print @strSQL 的结果发来看看
打印出来是这样的
set @strSQL = 'select top ' + str(@pageSize) +' '+@strGetFields+ ' from '
+ @tblName + ' where ' + @fldName + '' + @strTmp + '('
+ substring(@fldName, charindex('.',@fldName)+1, len(@fldName)) + ') from (select top ' + str((@pageIndex-1)*@pageSize) + ' '
+ @fldName + ' from ' + @tblName + '' + @strOrder + ') as tblTmp)'
+ @strOrder
end
上面这段脚本,len(@fldName)) + ') from (select top ' 这个from前面有一个全角的空格。
+ @fldName + ' from ' + @tblName + '' + @strOrder + ') as tblTmp)' 这个from前面有一个全角的空格其他地方没错误,已经调试通过。鼠标王