通用存储过程之四: 生成某一个表分页的存储过程

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+'| 联系方式：zlp321001@hotmail.com'+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
GO

CREATE 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

--测试
#SP_Generation_Query 't'

drop proc #SP_Generation_Query
drop proc #AutoGeneration_Query_P 

--结果
/**//*

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID('SP_t_Query') AND XTYPE IN (N'P'))
     DROP PROC SP_t_Query
GO 
/*+--------------------------------------+
| 过程名称：SP_t_Query
| 功能说明：根据条件获取表t的记录的分页存储过程
| 入口参数：@SearchCondition,@OrderList,@PageSize,@PageIndex
| 过程返回：返回记录数
| 维护记录：Y/A
| 使用案例：SP_t_Query
| 工作站名：RICHWAY-ZJ
| 联系方式：zlp321001@hotmail.com
| 创建日期：2006-08-31 12:32:03
+--------------------------------------+*/
CREATE PROC SP_t_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 t WHERE '+@SearchCondition+' '  
        END
        IF ISNULL(@SearchCondition,'')=''  AND ISNULL(@OrderList,'')<>''
        BEGIN
         SET @SQL='SELECT * FROM t ORDER BY '+@OrderList+' ' 
        END
        IF ISNULL(@SearchCondition,'')<>''  AND ISNULL(@OrderList,'')<>''
        BEGIN
         SET @SQL='SELECT * FROM t WHERE '+@SearchCondition+' ORDER BY '+@OrderList+' ' 
        END
        IF ISNULL(@SearchCondition,'')=''  AND ISNULL(@OrderList,'')='' 
        BEGIN
         SET @SQL='SELECT * FROM t '  
        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 


*/