sample:某一个表分页的存储过程生成器.你可以学习下. 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--测试 #SP_Generation_Query '表名'drop proc #SP_Generation_Query drop proc #AutoGeneration_Query_P
就多了两个头个尾巴
Create proc 存储过程名称
参数 类型
as
begin
set nocount on
一段处理SQL的业务逻辑.
set nocount off
end
就多加一两行create procedure 就要找软件帮助?
@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--测试
#SP_Generation_Query '表名'drop proc #SP_Generation_Query
drop proc #AutoGeneration_Query_P