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
*/
@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
*/