通用存储过程之五: 根据查询条件,获取表记录数的存储过程。

 

CREATE   PROC  #AutoGeneration_Count_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 + ' _Count '' ) AND XTYPE IN (N '' P '' )) ' + CHAR ( 10 )
  SET   @SQLROC = @SQLROC + SPACE ( 5 ) + ' DROP PROC SP_ ' + @TABLENAME + ' _Count ' + CHAR ( 10 )
  SET   @SQLROC = @SQLROC + ' GO  '
  SET   @DESCRIPTION = @DESCRIPTION + ' /*+--------------------------------------+ ' + CHAR ( 10 )
  SET   @DESCRIPTION = @DESCRIPTION + ' | 过程名称：SP_ ' + @TABLENAME + ' _Count ' + CHAR ( 10 )
  SET   @DESCRIPTION = @DESCRIPTION + ' | 功能说明：根据条件获取表 ' + @TABLENAME + ' 的记录数的存储过程 ' + CHAR ( 10 )
  SET   @DESCRIPTION = @DESCRIPTION + ' | 入口参数：@SearchCondition ' + CHAR ( 10 )
  SET   @DESCRIPTION = @DESCRIPTION + ' | 过程返回：返回记录数 ' + CHAR ( 10 )
  SET   @DESCRIPTION = @DESCRIPTION + ' | 维护记录：Y/A ' + CHAR ( 10 )
  SET   @DESCRIPTION = @DESCRIPTION + ' | 使用案例：SP_ ' + @TABLENAME + ' _Count ' + 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 + ' _Count '
  SET   @SQLROC = @SQLROC + CHAR ( 13 ) + CHAR ( 10 ) + SPACE ( 4 ) + ' @SearchCondition ' + SPACE ( 20 - LEN ( ' @SearchCondition ' )) + ' VARCHAR(8000) ' + CHAR ( 10 )
  SET   @SQLROC = @SQLROC + ' AS ' + CHAR ( 10 ) + ' BEGIN '
  SET   @SQLROC = @SQLROC + CHAR ( 10 ) + SPACE ( 4 ) + ' SET NOCOUNT ON '
  SET   @SQLROC = @SQLROC + CHAR ( 10 ) + SPACE ( 8 ) + ' DECLARE @SQL NVARCHAR(4000) '
  SET   @SQLROC = @SQLROC + CHAR ( 10 ) + SPACE ( 8 ) + ' DECLARE @ROW INT '
  SET   @SQLROC = @SQLROC + CHAR ( 10 ) + SPACE ( 12 ) + ' IF ISNULL(@SearchCondition, '''' )<> ''''   ' + CHAR ( 10 )
  SET   @SQLROC = @SQLROC + '   BEGIN  ' + CHAR ( 10 )
  SET   @SQLROC = @SQLROC + '    SET @SQL= '' SELECT @ROW=COUNT(*) FROM  ' + @TABLENAME + '  WHERE  '' +@SearchCondition+ ''   ''' + CHAR ( 10 )
  SET   @SQLROC = @SQLROC + '    EXECUTE SP_EXECUTESQL @SQL,N ''  @ROW INT OUTPUT '' ,@ROW OUTPUT ' + CHAR ( 10 )
  SET   @SQLROC = @SQLROC + '    SELECT _ROWCOUNT=@ROW ' + CHAR ( 10 )
  SET   @SQLROC = @SQLROC + '   END ' + CHAR ( 10 )
  SET   @SQLROC = @SQLROC + '   ELSE ' + CHAR ( 10 )
  SET   @SQLROC = @SQLROC + '   BEGIN  ' + CHAR ( 10 )
  SET   @SQLROC = @SQLROC + '    SELECT @ROW=COUNT(*) FROM  ' + @TABLENAME + '' + CHAR ( 10 )
  SET   @SQLROC = @SQLROC + '    SELECT _ROWCOUNT=@ROW ' + CHAR ( 10 )
  SET   @SQLROC = @SQLROC + '   END ' + 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_COUNT
@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_Count_P  @TABLENAME
  SET   @TABLENAMES = RIGHT ( @TABLENAMES , LEN ( @TABLENAMES ) - @I )
  SET   @I = CHARINDEX ( ' , ' , @TABLENAMES )
END
IF   LEN ( @TABLENAMES ) > 0
BEGIN
  EXEC  #AutoGeneration_Count_P  @TABLENAMES
END
END
GO

-- 测试
#SP_Generation_COUNT  ' t '

drop   proc  #SP_Generation_COUNT
drop   proc  #AutoGeneration_Count_P 

-- 结果
/**/ /*
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID('SP_t_Count') AND XTYPE IN (N'P'))
     DROP PROC SP_t_Count
GO 
/*+--------------------------------------+
| 过程名称：SP_t_Count
| 功能说明：根据条件获取表t的记录数的存储过程
| 入口参数：@SearchCondition
| 过程返回：返回记录数
| 维护记录：Y/A
| 使用案例：SP_t_Count
| 工作站名：RICHWAY-ZJ
| 联系方式：zlp321001@hotmail.com
| 创建日期：2006-08-31 12:37:02
+--------------------------------------+*/
CREATE   PROC  SP_t_Count
     @SearchCondition      VARCHAR ( 8000 )
AS
BEGIN
     SET  NOCOUNT  ON
         DECLARE   @SQL   NVARCHAR ( 4000 )
         DECLARE   @ROW   INT
             IF   ISNULL ( @SearchCondition , '' ) <> ''  
   BEGIN  
    SET   @SQL = ' SELECT @ROW=COUNT(*) FROM t WHERE  ' + @SearchCondition + '   '
    EXECUTE  SP_EXECUTESQL  @SQL ,N '  @ROW INT OUTPUT ' , @ROW  OUTPUT
    SELECT  _ ROWCOUNT = @ROW
   END
   ELSE
   BEGIN  
    SELECT   @ROW = COUNT ( * )  FROM  t
    SELECT  _ ROWCOUNT = @ROW
   END
     SET  NOCOUNT  OFF
END
GO  


*/