CREATEPROC #AutoGeneration_Query_P @TABLENAMEVARCHAR(50) AS BEGIN DECLARE@HOST_NAMEVARCHAR(200) DECLARE@GET_DATEDATETIME DECLARE@SQLROCVARCHAR(8000) DECLARE@DESCRIPTIONVARCHAR(4000) 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:分页排序方式'+CHAR(10) SET@DESCRIPTION=@DESCRIPTION+'| @PageSize:页大小, @PageIndex:当前页'+CHAR(10) SET@DESCRIPTION=@DESCRIPTION+'| 过程返回:返回记录数'+CHAR(10) SET@DESCRIPTION=@DESCRIPTION+'| 维护记录:Y/A'+CHAR(10) SET@DESCRIPTION=@DESCRIPTION+'| 使用案例:SP_'+@TABLENAME+'_Query '''','''',20,10'+CHAR(10) SET@DESCRIPTION=@DESCRIPTION+'| 工作站名:'+@HOST_NAME+''+CHAR(10) SET@DESCRIPTION=@DESCRIPTION+'| 联系方式:Spark.Zou@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(4)+'DECLARE @SQL VARCHAR(200)' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'DECLARE @KEYCOLUMNS VARCHAR(200)' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'DECLARE @DBNAME VARCHAR(20)' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'SELECT @SQL='''',@KEYCOLUMNS='''',@DBNAME=DB_NAME() ' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'IF ISNULL(@SearchCondition,'''')='''' AND ISNULL(@OrderList,'''')<>''''' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'BEGIN' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'SET @SQL=''SELECT * FROM ( ' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(4)+' SELECT ROW_NUMBER()OVER(ORDER BY ''+@OrderList+'' ) AS ROWID,* FROM '+@TABLENAME+'' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(4)+' ) AS MYTABLE ' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(4)+' WHERE ROWID BETWEEN ''+LTRIM(@PageSize)+''*(''+LTRIM(@PageIndex)+''-1)+1 and ''+LTRIM(@PageSize)+''*''+LTRIM(@PageIndex)+''''' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'END' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'IF ISNULL(@SearchCondition,'''')='''' AND ISNULL(@OrderList,'''')=''''' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'BEGIN' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' SELECT @KEYCOLUMNS=@KEYCOLUMNS+'',''+A.NAME' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' FROM SYSCOLUMNS A INNER JOIN SYSTYPES B' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' ON A.XUSERTYPE=B.XUSERTYPE AND ID=OBJECT_ID('''+@TABLENAME+''')' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' AND EXISTS(SELECT 1 FROM SYSOBJECTS ' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' WHERE XTYPE=''PK'' AND PARENT_OBJ=A.ID AND NAME IN (' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' SELECT NAME FROM SYSINDEXES ' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' WHERE INDID IN(' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' SELECT INDID FROM SYSINDEXKEYS ' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' WHERE ID = A.ID AND COLID=A.COLID' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' )' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' )' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'SET@KEYCOLUMNS=STUFF(@KEYCOLUMNS,1,1,'''')' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'IFISNULL(@KEYCOLUMNS,'''')=''''' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'BEGIN' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'raiserror(''SQL Server 2005 中需要提供排序字段,默认为表的主键字段'',16,1,@DBNAME)' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'RETURN' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'END' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'SET@SQL=''SELECT*FROM ( ' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'SELECT ROW_NUMBER()OVER(ORDERBY''+@KEYCOLUMNS+'' ) AS ROWID,*FROM'+@TABLENAME+'' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' ) AS MYTABLE ' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'WHERE ROWID BETWEEN''+LTRIM(@PageSize)+''*(''+LTRIM(@PageIndex)+''-1)+1and''+LTRIM(@PageSize)+''*''+LTRIM(@PageIndex)+''''' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'END' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'IFISNULL(@SearchCondition,'''')<>''''ANDISNULL(@OrderList,'''')=''''' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'BEGIN' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'SELECT@KEYCOLUMNS=@KEYCOLUMNS+'',''+A.NAME' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'FROM SYSCOLUMNS A INNERJOIN SYSTYPES B' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'ON A.XUSERTYPE=B.XUSERTYPE AND ID=OBJECT_ID('''+@TABLENAME+''')' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'ANDEXISTS(SELECT1FROM SYSOBJECTS ' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'WHERE XTYPE=''PK''AND PARENT_OBJ=A.ID AND NAME IN (' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'SELECT NAME FROM SYSINDEXES ' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'WHERE INDID IN(' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'SELECT INDID FROM SYSINDEXKEYS ' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'WHERE ID = A.ID AND COLID=A.COLID' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' )' SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' )' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' SET @KEYCOLUMNS=STUFF(@KEYCOLUMNS,1,1,'''')' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' IF ISNULL(@KEYCOLUMNS,'''')=''''' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' BEGIN' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' raiserror(''SQL Server 2005 中需要提供排序字段,默认为表的主键字段'',16,1,@DBNAME)' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' RETURN ' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' END' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' SET @SQL=''SELECT * FROM ( ' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' SELECT ROW_NUMBER()OVER(ORDER BY ''+@KEYCOLUMNS+'' ) AS ROWID,* FROM '+@TABLENAME+'' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' ) AS MYTABLE ' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' WHERE ROWID BETWEEN ''+LTRIM(@PageSize)+''*(''+LTRIM(@PageIndex)+''-1)+1 and ''+LTRIM(@PageSize)+''*''+LTRIM(@PageIndex)+''' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' AND @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(4)+' SET @SQL=''SELECT * FROM ( ' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(4)+' SELECT ROW_NUMBER()OVER(ORDER BY ''+@OrderList+'' ) AS ROWID,* FROM '+@TABLENAME+'' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(4)+' ) AS MYTABLE ' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(4)+' WHERE ROWID BETWEEN ''+LTRIM(@PageSize)+''*(''+LTRIM(@PageIndex)+''-1)+1 and ''+LTRIM(@PageSize)+''*''+LTRIM(@PageIndex)+''' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(4)+' AND @SearchCondition ''' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'END' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'EXEC(@SQL)' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'SET NOCOUNT OFF' SET@SQLROC=@SQLROC+CHAR(10)+'END' PRINT@SQLROC+CHAR(10)+'GO ' END GO CREATEPROC #SP_Generation_Query @TABLENAMESVARCHAR(8000) AS BEGIN DECLARE@IINT DECLARE@TABLENAMEVARCHAR(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 IFLEN(@TABLENAMES)>0 BEGIN EXEC #AutoGeneration_Query_P @TABLENAMES END END GO #SP_Generation_Query 'tb_human_plan2' dropproc #SP_Generation_Query dropproc #AutoGeneration_Query_P