CREATEPROC #AutoGeneration_Exists_P @TABLENAMEVARCHAR(50) AS BEGIN DECLARE@HOST_NAMEVARCHAR(200) DECLARE@GET_DATEDATETIME DECLARE@SQLROCVARCHAR(4000) DECLARE@REMARKVARCHAR(2000) DECLARE@SQLVARCHAR(2000) DECLARE@PARAMETERVARCHAR(2000) DECLARE@DESCRIPTIONVARCHAR(8000) DECLARE@WHERE_SQLVARCHAR(2000) DECLARE@ROWCOUNTINT SELECT@SQLROC='',@DESCRIPTION='',@PARAMETER='',@REMARK='',@WHERE_SQL='',@SQL='', @HOST_NAME=HOST_NAME(),@GET_DATE=GETDATE() SET@SQLROC=@SQLROC+'IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID(''SP_'+@TABLENAME+'_Exists'') AND XTYPE IN (N''P''))'+CHAR(10) SET@SQLROC=@SQLROC+SPACE(5)+'DROP PROC SP_'+@TABLENAME+'_Exists'+CHAR(10) SET@SQLROC=@SQLROC+'GO ' SET NOCOUNT ON CREATETABLE #(TABLE_QUALIFIER VARCHAR(100), TABLE_OWNER VARCHAR(100), TABLE_NAME VARCHAR(100), COLUMN_NAME VARCHAR(100), KEY_SEQ VARCHAR(50), PK_NAME VARCHAR(100)) INSERTINTO # EXEC SP_PKEYS @TABLENAME SELECT@REMARK=@REMARK+',@'+COLUMN_NAME, @WHERE_SQL=@WHERE_SQL+' AND '+COLUMN_NAME+'=@'+COLUMN_NAME+'' FROM # SELECT@PARAMETER=@PARAMETER+SPACE(4)+'@'+LTRIM(NAME)+SPACE(15-LEN(NAME))+ CASEWHEN xtype=34THEN'image' WHEN xtype=35THEN'text' WHEN xtype=36THEN'uniqueidentifier' WHEN xtype=48THEN'tinyint' WHEN xtype=52THEN'smallint' WHEN xtype=56THEN'int' WHEN xtype=58THEN'smalldatetime' WHEN xtype=59THEN'real' WHEN xtype=60THEN'money' WHEN xtype=61THEN'datetime' WHEN xtype=62THEN'float' WHEN xtype=98THEN'sql_variant' WHEN xtype=99THEN'ntext' WHEN xtype=104THEN'bit' WHEN xtype=106THEN'decimal' WHEN xtype=108THEN'numeric' WHEN xtype=122THEN'smallmoney' WHEN xtype=127THEN'bigint' WHEN xtype=165THEN'varbinary' WHEN xtype=167THEN'varchar'+'('+LTRIM(length)+')' WHEN xtype=173THEN'binary' WHEN xtype=175THEN'char'+'('+LTRIM(length)+')' WHEN xtype=189THEN'timestamp' WHEN xtype=231THEN'nvarchar'+'('+LTRIM(length)+')' WHEN xtype=239THEN'nchar'+'('+LTRIM(length)+')' WHEN xtype=241THEN'xml' WHEN xtype=231THEN'sysname'END+','+CHAR(10) FROM SYSCOLUMNS A WHERE ID=OBJECT_ID(''+@TABLENAME+'') AND NAME IN (SELECT COLUMN_NAME FROM #) DROPTABLE # SET NOCOUNT OFF SET@DESCRIPTION=@DESCRIPTION+'/*+--------------------------------------+'+CHAR(10) SET@DESCRIPTION=@DESCRIPTION+'| 过程名称:SP_'+@TABLENAME+'_Exists'+CHAR(10) SET@DESCRIPTION=@DESCRIPTION+'| 功能说明:判断表'+@TABLENAME+'是否存在该记录的存储过程'+CHAR(10) SET@DESCRIPTION=@DESCRIPTION+'| 入口参数:'+STUFF(@REMARK,1,1,'')+''+CHAR(10) SET@DESCRIPTION=@DESCRIPTION+'| 过程返回:无返回记录'+CHAR(10) SET@DESCRIPTION=@DESCRIPTION+'| 维护记录:Y/A'+CHAR(10) SET@DESCRIPTION=@DESCRIPTION+'| 使用案例:SP_'+@TABLENAME+'_Exists'+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+'_Exists' SET@SQLROC=@SQLROC+CHAR(13)+CHAR(10)+LEFT(@PARAMETER,LEN(@PARAMETER)-2)+CHAR(10) SET@SQLROC=@SQLROC+'AS'+CHAR(10)+'BEGIN' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'SET NOCOUNT ON' SET@SQLROC=@SQLROC+CHAR(32)+@SQL SET@SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'IF EXISTS (SELECT 1 FROM '+@TABLENAME+' WHERE '+STUFF(@WHERE_SQL,1,4,'')+')'+CHAR(10) SET@SQLROC=@SQLROC+' BEGIN '+CHAR(10) SET@SQLROC=@SQLROC+' SELECT _RETURN=1'+CHAR(10) SET@SQLROC=@SQLROC+' END'+CHAR(10) SET@SQLROC=@SQLROC+' ELSE'+CHAR(10) SET@SQLROC=@SQLROC+' BEGIN '+CHAR(10) SET@SQLROC=@SQLROC+' SELECT _RETURN=0'+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 CREATEPROC #SP_Generation_EXISTS @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_Exists_P @TABLENAME SET@TABLENAMES=RIGHT(@TABLENAMES,LEN(@TABLENAMES)-@I) SET@I=CHARINDEX(',',@TABLENAMES) END IFLEN(@TABLENAMES)>0 BEGIN EXEC #AutoGeneration_Exists_P @TABLENAMES END END GO --测试 #SP_Generation_EXISTS 't' dropproc #AutoGeneration_Exists_P dropproc #SP_Generation_EXISTS --测试结果 /**//* IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID('SP_t_Exists') AND XTYPE IN (N'P')) DROP PROC SP_t_Exists GO /*+--------------------------------------+ | 过程名称:SP_t_Exists | 功能说明:判断表t是否存在该记录的存储过程 | 入口参数:@type | 过程返回:无返回记录 | 维护记录:Y/A | 使用案例:SP_t_Exists | 工作站名:RICHWAY-ZJ | 联系方式:zlp321001@hotmail.com | 创建日期:2006-08-31 12:20:09 +--------------------------------------+*/ CREATEPROC SP_t_Exists @typevarchar(10) AS BEGIN SET NOCOUNT ON IFEXISTS (SELECT1FROM t WHERE type=@type) BEGIN SELECT _RETURN=1 END ELSE BEGIN SELECT _RETURN=0 END SET NOCOUNT OFF END GO */