--存储过程: CREATE PROC #AutoGeneration_Query_P @TABLENAME VARCHAR(50) AS BEGIN DECLARE @HOST_NAME VARCHAR(200) DECLARE @GET_DATE DATETIME DECLARE @SQLROC VARCHAR(8000) DECLARE @DESCRIPTION VARCHAR(4000) DECLARE @ROWCOUNT INT SELECT @SQLROC='',@DESCRIPTION='', @HOST_NAME=HOST_NAME(),@GET_DATE=GETDATE() SET @SQLROC=@SQLROC+'IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID(''SP_'+@TABLENAME+'_Query'') AND XTYPE IN (N''P''))'+CHAR(10) SET @SQLROC=@SQLROC+SPACE(5)+'DROP PROC SP_'+@TABLENAME+'_Query'+CHAR(10) SET @SQLROC=@SQLROC+'GO ' SET @DESCRIPTION=@DESCRIPTION+'/*+--------------------------------------+'+CHAR(10) SET @DESCRIPTION=@DESCRIPTION+'| 过程名称:SP_'+@TABLENAME+'_Query'+CHAR(10) SET @DESCRIPTION=@DESCRIPTION+'| 功能说明:根据条件获取表'+@TABLENAME+'的记录的分页存储过程'+CHAR(10) SET @DESCRIPTION=@DESCRIPTION+'| 入口参数:@SearchCondition,@OrderList,@PageSize,@PageIndex'+CHAR(10) SET @DESCRIPTION=@DESCRIPTION+'| 过程返回:返回记录数'+CHAR(10) SET @DESCRIPTION=@DESCRIPTION+'| 维护记录:Y/A'+CHAR(10) SET @DESCRIPTION=@DESCRIPTION+'| 使用案例:SP_'+@TABLENAME+'_Query'+CHAR(10) SET @DESCRIPTION=@DESCRIPTION+'| 工作站名:'+@HOST_NAME+''+CHAR(10) SET @DESCRIPTION=@DESCRIPTION+'| 联系方式:[email protected]'+CHAR(10) SET @DESCRIPTION=@DESCRIPTION+'| 创建日期:'+CONVERT(VARCHAR(20),@GET_DATE,120)+''+CHAR(10) SET @DESCRIPTION=@DESCRIPTION+'+--------------------------------------+*/'+CHAR(10) SELECT @SQLROC=@SQLROC+CHAR(10)+@DESCRIPTION+'CREATE PROC SP_'+@TABLENAME+'_Query' SET @SQLROC=@SQLROC+CHAR(13)+CHAR(10)+SPACE(4)+'@SearchCondition'+SPACE(20-LEN('@SearchCondition'))+'VARCHAR(2000), --查询条件' SET @SQLROC=@SQLROC+CHAR(13)+CHAR(10)+SPACE(4)+'@OrderList'+SPACE(20-LEN('@OrderList'))+'VARCHAR(1000), --排序列表' SET @SQLROC=@SQLROC+CHAR(13)+CHAR(10)+SPACE(4)+'@PageSize'+SPACE(20-LEN('@PageSize'))+'INT=10, --每页的大小' SET @SQLROC=@SQLROC+CHAR(13)+CHAR(10)+SPACE(4)+'@PageIndex'+SPACE(20-LEN('@PageIndex'))+'INT --要显示的页码' SET @SQLROC=@SQLROC+CHAR(10)+'AS'+CHAR(10)+'BEGIN' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'SET NOCOUNT ON' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'DECLARE @p1 int ' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'DECLARE @SQL VARCHAR(8000) ' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'DECLARE @PageCount INT' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'IF ISNULL(@SearchCondition,'''')<>'''' AND ISNULL(@OrderList,'''')=''''' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'BEGIN' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' SET @SQL=''SELECT * FROM '+@TABLENAME+' WHERE ''+@SearchCondition+'' '' ' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'END' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'IF ISNULL(@SearchCondition,'''')='''' AND ISNULL(@OrderList,'''')<>''''' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'BEGIN' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' SET @SQL=''SELECT * FROM '+@TABLENAME+' ORDER BY ''+@OrderList+'' '' ' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'END' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'IF ISNULL(@SearchCondition,'''')<>'''' AND ISNULL(@OrderList,'''')<>''''' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'BEGIN' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' SET @SQL=''SELECT * FROM '+@TABLENAME+' WHERE ''+@SearchCondition+'' ORDER BY ''+@OrderList+'' '' ' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'END' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'IF ISNULL(@SearchCondition,'''')='''' AND ISNULL(@OrderList,'''')='''' ' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'BEGIN' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' SET @SQL=''SELECT * FROM '+@TABLENAME+' '' ' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'END' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'--初始化分页游标' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'EXEC sp_cursoropen '+CHAR(10) SET @SQLROC=@SQLROC+' @cursor=@p1 OUTPUT, '+CHAR(10) SET @SQLROC=@SQLROC+' @stmt=@SQL,'+CHAR(10) SET @SQLROC=@SQLROC+' @scrollopt=1,'+CHAR(10) SET @SQLROC=@SQLROC+' @ccopt=1,'+CHAR(10) SET @SQLROC=@SQLROC+' @rowcount=@PageCount OUTPUT'+CHAR(10) SET @SQLROC=@SQLROC+' --计算总页数'+CHAR(10) SET @SQLROC=@SQLROC+' IF ISNULL(@PageSize,0)<1 '+CHAR(10) SET @SQLROC=@SQLROC+' SET @PageSize=10'+CHAR(10) SET @SQLROC=@SQLROC+' SET @PageCount=(@PageCount+@PageSize-1)/@PageSize'+CHAR(10) SET @SQLROC=@SQLROC+' IF ISNULL(@PageIndex,0)<1 OR ISNULL(@PageIndex,0)>@PageCount'+CHAR(10) SET @SQLROC=@SQLROC+' SET @PageIndex=1'+CHAR(10) SET @SQLROC=@SQLROC+' ELSE'+CHAR(10) SET @SQLROC=@SQLROC+' SET @PageIndex=(@PageIndex-1)*@PageSize+1'+CHAR(10) SET @SQLROC=@SQLROC+' --显示指定页的数据'+CHAR(10) SET @SQLROC=@SQLROC+' EXEC sp_cursorfetch @p1,16,@PageIndex,@PageSize'+CHAR(10) SET @SQLROC=@SQLROC+' --关闭分页游标'+CHAR(10) SET @SQLROC=@SQLROC+' EXEC sp_cursorclose @p1'+CHAR(10) SET @SQLROC=@SQLROC+SPACE(4)+'SET NOCOUNT OFF' SET @SQLROC=@SQLROC+CHAR(10)+'END' PRINT @SQLROC+CHAR(10)+'GO ' END GOCREATE PROC #SP_Generation_Query @TABLENAMES VARCHAR(8000) AS BEGIN DECLARE @I INT DECLARE @TABLENAME VARCHAR(100) SET @I=CHARINDEX(',',@TABLENAMES) WHILE @I>0 BEGIN SET @TABLENAME=LEFT(@TABLENAMES,@I-1) EXEC #AutoGeneration_Query_P @TABLENAME SET @TABLENAMES=RIGHT(@TABLENAMES,LEN(@TABLENAMES)-@I) SET @I=CHARINDEX(',',@TABLENAMES) END IF LEN(@TABLENAMES)>0 BEGIN EXEC #AutoGeneration_Query_P @TABLENAMES END END GO--测试 use northwind go #SP_Generation_Query 'Orders' go drop proc #SP_Generation_Query drop proc #AutoGeneration_Query_P IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID('SP_Orders_Query') AND XTYPE IN (N'P')) DROP PROC SP_Orders_Query GO /*+--------------------------------------+ | 过程名称:SP_Orders_Query | 功能说明:根据条件获取表Orders的记录的分页存储过程 | 入口参数:@SearchCondition,@OrderList,@PageSize,@PageIndex | 过程返回:返回记录数 | 维护记录:Y/A | 使用案例:SP_Orders_Query | 工作站名:ZLP | 联系方式:[email protected] | 创建日期:2006-12-25 17:32:52 +--------------------------------------+*/ CREATE PROC SP_Orders_Query @SearchCondition VARCHAR(2000), --查询条件 @OrderList VARCHAR(1000), --排序列表 @PageSize INT=10, --每页的大小 @PageIndex INT --要显示的页码 AS BEGIN SET NOCOUNT ON DECLARE @p1 int DECLARE @SQL VARCHAR(8000) DECLARE @PageCount INT IF ISNULL(@SearchCondition,'')<>'' AND ISNULL(@OrderList,'')='' BEGIN SET @SQL='SELECT * FROM Orders WHERE '+@SearchCondition+' ' END IF ISNULL(@SearchCondition,'')='' AND ISNULL(@OrderList,'')<>'' BEGIN SET @SQL='SELECT * FROM Orders ORDER BY '+@OrderList+' ' END IF ISNULL(@SearchCondition,'')<>'' AND ISNULL(@OrderList,'')<>'' BEGIN SET @SQL='SELECT * FROM Orders WHERE '+@SearchCondition+' ORDER BY '+@OrderList+' ' END IF ISNULL(@SearchCondition,'')='' AND ISNULL(@OrderList,'')='' BEGIN SET @SQL='SELECT * FROM Orders ' END --初始化分页游标 EXEC sp_cursoropen @cursor=@p1 OUTPUT, @stmt=@SQL, @scrollopt=1, @ccopt=1, @rowcount=@PageCount OUTPUT --计算总页数 IF ISNULL(@PageSize,0)<1 SET @PageSize=10 SET @PageCount=(@PageCount+@PageSize-1)/@PageSize IF ISNULL(@PageIndex,0)<1 OR ISNULL(@PageIndex,0)>@PageCount SET @PageIndex=1 ELSE SET @PageIndex=(@PageIndex-1)*@PageSize+1 --显示指定页的数据 EXEC sp_cursorfetch @p1,16,@PageIndex,@PageSize --关闭分页游标 EXEC sp_cursorclose @p1 SET NOCOUNT OFF END GO
其实那个存储过程效率是最高的啊.很多时候是效率第一吧!前台判断也多简单啊...--给另一个分页的吧!! CREATE PROCEDURE sp_page @tb varchar(50), --表名 @col varchar(50), --按该列来进行分页 @coltype int, --@col列的类型,0-数字类型,1-字符类型,2-日期时间类型 @orderby bit, --排序,0-顺序,1-倒序 @collist varchar(800),--要查询出的字段列表,*表示全部字段 @pagesize int, --每页记录数 @page int, --指定页 @condition varchar(800),--查询条件 @pages int OUTPUT --总页数 AS /* 功能描述:对指定表中满足条件的记录按指定列进行分页查询,分页可以顺序、倒序 查询可以指定页大小、指定查询任意页、指定输出字段列表,返回总页数 作 者:pbsql 版 本:1.10 最后修改:2004-11-29 */ DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800) IF @condition is null or rtrim(@condition)='' BEGIN--没有查询条件 SET @where1=' WHERE ' SET @where2=' ' END ELSE BEGIN--有查询条件 SET @where1=' WHERE ('+@condition+') AND '--本来有条件再加上此条件 SET @where2=' WHERE ('+@condition+') '--原本没有条件而加上此条件 END SET @sql='SELECT @pages=CEILING((COUNT(*)+0.0)/'+CAST(@pagesize AS varchar)+ ') FROM '+@tb+@where2 EXEC sp_executesql @sql,N'@pages int OUTPUT',@pages OUTPUT--计算总页数 IF @orderby=0 SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+ ' FROM '+@tb+@where1+@col+'>(SELECT MAX('+@col+') '+ ' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+ @col+' FROM '+@tb+@where2+'ORDER BY '+@col+') t) ORDER BY '+@col ELSE SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+ ' FROM '+@tb+@where1+@col+'<(SELECT MIN('+@col+') '+ ' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+ @col+' FROM '+@tb+@where2+'ORDER BY '+@col+' DESC) t) ORDER BY '+ @col+' DESC' IF @page=1--第一页 SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+' FROM '+@tb+ @where2+'ORDER BY '+@col+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END EXEC(@sql) GO
谢谢,我简化了一下…… DECLARE @tb varchar(50), --表名 @pagesize int, --每页记录数 @page int, --指定页 SELECT TOP @pagesize * FROM @tb WHERE (ID >( SELECT MAX(id) FROM ( SELECT TOP @pagesize*@page ID FROM @tb ORDER BY ID ) AS t ) ) ORDER BY ID 感觉控制得好的话,这个应该比第一个用游标的快。 现在明白我要问什么了 EXEC sp_cursoropen @cursor=@p1 OUTPUT,@stmt=@SQL,@scrollopt=1,@ccopt=1, @rowcount=@PageCount OUTPUT 执行打开游标就一定返回一个记录集? 哎…… 没办法了。 谢谢zlp321002(风中有只可爱的熊)。
CREATE PROC #AutoGeneration_Query_P
@TABLENAME VARCHAR(50)
AS
BEGIN
DECLARE @HOST_NAME VARCHAR(200)
DECLARE @GET_DATE DATETIME
DECLARE @SQLROC VARCHAR(8000)
DECLARE @DESCRIPTION VARCHAR(4000)
DECLARE @ROWCOUNT INT
SELECT @SQLROC='',@DESCRIPTION='',
@HOST_NAME=HOST_NAME(),@GET_DATE=GETDATE()
SET @SQLROC=@SQLROC+'IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID(''SP_'+@TABLENAME+'_Query'') AND XTYPE IN (N''P''))'+CHAR(10)
SET @SQLROC=@SQLROC+SPACE(5)+'DROP PROC SP_'+@TABLENAME+'_Query'+CHAR(10)
SET @SQLROC=@SQLROC+'GO '
SET @DESCRIPTION=@DESCRIPTION+'/*+--------------------------------------+'+CHAR(10)
SET @DESCRIPTION=@DESCRIPTION+'| 过程名称:SP_'+@TABLENAME+'_Query'+CHAR(10)
SET @DESCRIPTION=@DESCRIPTION+'| 功能说明:根据条件获取表'+@TABLENAME+'的记录的分页存储过程'+CHAR(10)
SET @DESCRIPTION=@DESCRIPTION+'| 入口参数:@SearchCondition,@OrderList,@PageSize,@PageIndex'+CHAR(10)
SET @DESCRIPTION=@DESCRIPTION+'| 过程返回:返回记录数'+CHAR(10)
SET @DESCRIPTION=@DESCRIPTION+'| 维护记录:Y/A'+CHAR(10)
SET @DESCRIPTION=@DESCRIPTION+'| 使用案例:SP_'+@TABLENAME+'_Query'+CHAR(10)
SET @DESCRIPTION=@DESCRIPTION+'| 工作站名:'+@HOST_NAME+''+CHAR(10)
SET @DESCRIPTION=@DESCRIPTION+'| 联系方式:[email protected]'+CHAR(10)
SET @DESCRIPTION=@DESCRIPTION+'| 创建日期:'+CONVERT(VARCHAR(20),@GET_DATE,120)+''+CHAR(10)
SET @DESCRIPTION=@DESCRIPTION+'+--------------------------------------+*/'+CHAR(10)
SELECT @SQLROC=@SQLROC+CHAR(10)+@DESCRIPTION+'CREATE PROC SP_'+@TABLENAME+'_Query'
SET @SQLROC=@SQLROC+CHAR(13)+CHAR(10)+SPACE(4)+'@SearchCondition'+SPACE(20-LEN('@SearchCondition'))+'VARCHAR(2000), --查询条件'
SET @SQLROC=@SQLROC+CHAR(13)+CHAR(10)+SPACE(4)+'@OrderList'+SPACE(20-LEN('@OrderList'))+'VARCHAR(1000), --排序列表'
SET @SQLROC=@SQLROC+CHAR(13)+CHAR(10)+SPACE(4)+'@PageSize'+SPACE(20-LEN('@PageSize'))+'INT=10, --每页的大小'
SET @SQLROC=@SQLROC+CHAR(13)+CHAR(10)+SPACE(4)+'@PageIndex'+SPACE(20-LEN('@PageIndex'))+'INT --要显示的页码'
SET @SQLROC=@SQLROC+CHAR(10)+'AS'+CHAR(10)+'BEGIN'
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'SET NOCOUNT ON'
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'DECLARE @p1 int '
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'DECLARE @SQL VARCHAR(8000) '
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'DECLARE @PageCount INT'
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'IF ISNULL(@SearchCondition,'''')<>'''' AND ISNULL(@OrderList,'''')='''''
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'BEGIN'
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' SET @SQL=''SELECT * FROM '+@TABLENAME+' WHERE ''+@SearchCondition+'' '' '
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'END'
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'IF ISNULL(@SearchCondition,'''')='''' AND ISNULL(@OrderList,'''')<>'''''
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'BEGIN'
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' SET @SQL=''SELECT * FROM '+@TABLENAME+' ORDER BY ''+@OrderList+'' '' '
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'END'
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'IF ISNULL(@SearchCondition,'''')<>'''' AND ISNULL(@OrderList,'''')<>'''''
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'BEGIN'
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' SET @SQL=''SELECT * FROM '+@TABLENAME+' WHERE ''+@SearchCondition+'' ORDER BY ''+@OrderList+'' '' '
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'END'
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'IF ISNULL(@SearchCondition,'''')='''' AND ISNULL(@OrderList,'''')='''' '
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'BEGIN'
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' SET @SQL=''SELECT * FROM '+@TABLENAME+' '' '
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'END'
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'--初始化分页游标'
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'EXEC sp_cursoropen '+CHAR(10)
SET @SQLROC=@SQLROC+' @cursor=@p1 OUTPUT, '+CHAR(10)
SET @SQLROC=@SQLROC+' @stmt=@SQL,'+CHAR(10)
SET @SQLROC=@SQLROC+' @scrollopt=1,'+CHAR(10)
SET @SQLROC=@SQLROC+' @ccopt=1,'+CHAR(10)
SET @SQLROC=@SQLROC+' @rowcount=@PageCount OUTPUT'+CHAR(10)
SET @SQLROC=@SQLROC+' --计算总页数'+CHAR(10)
SET @SQLROC=@SQLROC+' IF ISNULL(@PageSize,0)<1 '+CHAR(10)
SET @SQLROC=@SQLROC+' SET @PageSize=10'+CHAR(10)
SET @SQLROC=@SQLROC+' SET @PageCount=(@PageCount+@PageSize-1)/@PageSize'+CHAR(10)
SET @SQLROC=@SQLROC+' IF ISNULL(@PageIndex,0)<1 OR ISNULL(@PageIndex,0)>@PageCount'+CHAR(10)
SET @SQLROC=@SQLROC+' SET @PageIndex=1'+CHAR(10)
SET @SQLROC=@SQLROC+' ELSE'+CHAR(10)
SET @SQLROC=@SQLROC+' SET @PageIndex=(@PageIndex-1)*@PageSize+1'+CHAR(10)
SET @SQLROC=@SQLROC+' --显示指定页的数据'+CHAR(10)
SET @SQLROC=@SQLROC+' EXEC sp_cursorfetch @p1,16,@PageIndex,@PageSize'+CHAR(10)
SET @SQLROC=@SQLROC+' --关闭分页游标'+CHAR(10)
SET @SQLROC=@SQLROC+' EXEC sp_cursorclose @p1'+CHAR(10)
SET @SQLROC=@SQLROC+SPACE(4)+'SET NOCOUNT OFF'
SET @SQLROC=@SQLROC+CHAR(10)+'END'
PRINT @SQLROC+CHAR(10)+'GO '
END
GOCREATE PROC #SP_Generation_Query
@TABLENAMES VARCHAR(8000)
AS
BEGIN
DECLARE @I INT
DECLARE @TABLENAME VARCHAR(100)
SET @I=CHARINDEX(',',@TABLENAMES)
WHILE @I>0
BEGIN
SET @TABLENAME=LEFT(@TABLENAMES,@I-1)
EXEC #AutoGeneration_Query_P @TABLENAME
SET @TABLENAMES=RIGHT(@TABLENAMES,LEN(@TABLENAMES)-@I)
SET @I=CHARINDEX(',',@TABLENAMES)
END
IF LEN(@TABLENAMES)>0
BEGIN
EXEC #AutoGeneration_Query_P @TABLENAMES
END
END
GO--测试
use northwind
go
#SP_Generation_Query 'Orders'
go
drop proc #SP_Generation_Query
drop proc #AutoGeneration_Query_P IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID('SP_Orders_Query') AND XTYPE IN (N'P'))
DROP PROC SP_Orders_Query
GO
/*+--------------------------------------+
| 过程名称:SP_Orders_Query
| 功能说明:根据条件获取表Orders的记录的分页存储过程
| 入口参数:@SearchCondition,@OrderList,@PageSize,@PageIndex
| 过程返回:返回记录数
| 维护记录:Y/A
| 使用案例:SP_Orders_Query
| 工作站名:ZLP
| 联系方式:[email protected]
| 创建日期:2006-12-25 17:32:52
+--------------------------------------+*/
CREATE PROC SP_Orders_Query
@SearchCondition VARCHAR(2000), --查询条件
@OrderList VARCHAR(1000), --排序列表
@PageSize INT=10, --每页的大小
@PageIndex INT --要显示的页码
AS
BEGIN
SET NOCOUNT ON
DECLARE @p1 int
DECLARE @SQL VARCHAR(8000)
DECLARE @PageCount INT
IF ISNULL(@SearchCondition,'')<>'' AND ISNULL(@OrderList,'')=''
BEGIN
SET @SQL='SELECT * FROM Orders WHERE '+@SearchCondition+' '
END
IF ISNULL(@SearchCondition,'')='' AND ISNULL(@OrderList,'')<>''
BEGIN
SET @SQL='SELECT * FROM Orders ORDER BY '+@OrderList+' '
END
IF ISNULL(@SearchCondition,'')<>'' AND ISNULL(@OrderList,'')<>''
BEGIN
SET @SQL='SELECT * FROM Orders WHERE '+@SearchCondition+' ORDER BY '+@OrderList+' '
END
IF ISNULL(@SearchCondition,'')='' AND ISNULL(@OrderList,'')=''
BEGIN
SET @SQL='SELECT * FROM Orders '
END
--初始化分页游标
EXEC sp_cursoropen
@cursor=@p1 OUTPUT,
@stmt=@SQL,
@scrollopt=1,
@ccopt=1,
@rowcount=@PageCount OUTPUT
--计算总页数
IF ISNULL(@PageSize,0)<1
SET @PageSize=10
SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
IF ISNULL(@PageIndex,0)<1 OR ISNULL(@PageIndex,0)>@PageCount
SET @PageIndex=1
ELSE
SET @PageIndex=(@PageIndex-1)*@PageSize+1
--显示指定页的数据
EXEC sp_cursorfetch @p1,16,@PageIndex,@PageSize
--关闭分页游标
EXEC sp_cursorclose @p1
SET NOCOUNT OFF
END
GO
{
OleDbCommand command = new OleDbCommand();
PrepareCommand(command, commandType, commandText, commandParameterCollection); OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command);
if (tableName == null || (tableName.Length < 1))
dataAdapter.Fill(ds);
else
dataAdapter.Fill(ds, tableName);
command.Parameters.Clear(); return ds;
}
但是不能结帖……
执行了一下,返回的还是两个结果集
EXEC sp_cursoropen @cursor=@p1 OUTPUT,@stmt=@SQL,@scrollopt=1,@ccopt=1,@rowcount=@PageCount OUTPUT
打开存储过程游标就会返回一个结果集?
虽然全部执行之后第一个结果集没有记录……
在前台我是可以处理两个结果集的
主要是不想看到两个结果集
想在后台就返回一个结果集
没别的
或者别的存储过程也可以
只要是满足输入的几个参数,返回一个结果集跟几个值的分页存储过程就可以
费心了
谢谢。
CREATE PROCEDURE sp_page
@tb varchar(50), --表名
@col varchar(50), --按该列来进行分页
@coltype int, --@col列的类型,0-数字类型,1-字符类型,2-日期时间类型
@orderby bit, --排序,0-顺序,1-倒序
@collist varchar(800),--要查询出的字段列表,*表示全部字段
@pagesize int, --每页记录数
@page int, --指定页
@condition varchar(800),--查询条件
@pages int OUTPUT --总页数
AS
/*
功能描述:对指定表中满足条件的记录按指定列进行分页查询,分页可以顺序、倒序
查询可以指定页大小、指定查询任意页、指定输出字段列表,返回总页数
作 者:pbsql
版 本:1.10
最后修改:2004-11-29
*/
DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800)
IF @condition is null or rtrim(@condition)=''
BEGIN--没有查询条件
SET @where1=' WHERE '
SET @where2=' '
END
ELSE
BEGIN--有查询条件
SET @where1=' WHERE ('+@condition+') AND '--本来有条件再加上此条件
SET @where2=' WHERE ('+@condition+') '--原本没有条件而加上此条件
END
SET @sql='SELECT @pages=CEILING((COUNT(*)+0.0)/'+CAST(@pagesize AS varchar)+
') FROM '+@tb+@where2
EXEC sp_executesql @sql,N'@pages int OUTPUT',@pages OUTPUT--计算总页数
IF @orderby=0
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' FROM '+@tb+@where1+@col+'>(SELECT MAX('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@tb+@where2+'ORDER BY '+@col+') t) ORDER BY '+@col
ELSE
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' FROM '+@tb+@where1+@col+'<(SELECT MIN('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@tb+@where2+'ORDER BY '+@col+' DESC) t) ORDER BY '+
@col+' DESC'
IF @page=1--第一页
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+' FROM '+@tb+
@where2+'ORDER BY '+@col+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
EXEC(@sql)
GO
DECLARE
@tb varchar(50), --表名
@pagesize int, --每页记录数
@page int, --指定页
SELECT TOP @pagesize *
FROM @tb
WHERE (ID >(
SELECT MAX(id) FROM (
SELECT TOP @pagesize*@page ID FROM @tb ORDER BY ID
) AS t
)
)
ORDER BY ID 感觉控制得好的话,这个应该比第一个用游标的快。
现在明白我要问什么了
EXEC sp_cursoropen @cursor=@p1 OUTPUT,@stmt=@SQL,@scrollopt=1,@ccopt=1,
@rowcount=@PageCount OUTPUT
执行打开游标就一定返回一个记录集?
哎……
没办法了。
谢谢zlp321002(风中有只可爱的熊)。