通用存储过程之五: 根据查询条件,获取表记录数的存储过程。
2013年04月16日
⁄ 综合
⁄ 共 3880字 ⁄ 字号
小 中 大
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
*/