通用存储过程之一：插入、更新、删除存储过程。

 

CREATE   PROC  #AutoGeneration_IUD_P
@TABLENAME   VARCHAR ( 50 ),
@FLAT    TINYINT
AS
BEGIN
  DECLARE   @HOST_NAME   VARCHAR ( 200 )
  DECLARE   @GET_DATE   DATETIME
  SELECT   @HOST_NAME = HOST_NAME (), @GET_DATE = GETDATE ()
  IF   @FLAT = 1   -- 插入
  BEGIN
  DECLARE   @INSERT_SQLROC   NVARCHAR ( 4000 )
  DECLARE   @INSERT_SQL   VARCHAR ( 8000 )
  DECLARE   @INSERT_PARAMETER   VARCHAR ( 8000 )
  DECLARE   @INSERT_DESCRIPTION   VARCHAR ( 8000 )
  DECLARE   @INSERT_REMARK   VARCHAR ( 2000 )
  DECLARE   @INSERT_COLUMN   VARCHAR ( 8000 )
  SELECT   @INSERT_SQLROC = '' , @INSERT_SQL = '' , @INSERT_PARAMETER = '' , @INSERT_DESCRIPTION = '' ,
    @INSERT_REMARK = '' , @INSERT_COLUMN = ''
  SET   @INSERT_SQLROC = @INSERT_SQLROC + ' IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID( '' SP_ ' + @TABLENAME + ' _AddNew '' ) AND XTYPE IN (N '' P '' )) ' + CHAR ( 10 )
  SET   @INSERT_SQLROC = @INSERT_SQLROC + SPACE ( 5 ) + ' DROP PROC SP_ ' + @TABLENAME + ' _AddNew ' + CHAR ( 10 )
  SET   @INSERT_SQLROC = @INSERT_SQLROC + ' GO  '
  SELECT   @INSERT_PARAMETER = @INSERT_PARAMETER + SPACE ( 4 ) + ' @ ' + LTRIM (NAME) + SPACE ( 30 - LEN (NAME)) +
      CASE   WHEN  xtype = 34   THEN   ' image '
        WHEN  xtype = 35   THEN   ' text '
        WHEN  xtype = 36   THEN   ' uniqueidentifier '
        WHEN  xtype = 48   THEN   ' tinyint '
        WHEN  xtype = 52   THEN   ' smallint '
        WHEN  xtype = 56   THEN   ' int '
        WHEN  xtype = 58   THEN   ' smalldatetime '
        WHEN  xtype = 59   THEN   ' real '
        WHEN  xtype = 60   THEN   ' money '
        WHEN  xtype = 61   THEN   ' datetime '
        WHEN  xtype = 62   THEN   ' float '
        WHEN  xtype = 98   THEN   ' sql_variant '
        WHEN  xtype = 99   THEN   ' ntext '
        WHEN  xtype = 104   THEN   ' bit '
        WHEN  xtype = 106   THEN   ' decimal '
        WHEN  xtype = 108   THEN   ' numeric '
        WHEN  xtype = 122   THEN   ' smallmoney '
        WHEN  xtype = 127   THEN   ' bigint '
        WHEN  xtype = 165   THEN   ' varbinary '
           WHEN  xtype = 167   THEN   ' varchar ' + ' ( ' + LTRIM (length) + ' ) '
        WHEN  xtype = 173   THEN   ' binary '
        WHEN  xtype = 175   THEN   ' char ' + ' ( ' + LTRIM (length) + ' ) '
        WHEN  xtype = 189   THEN   ' timestamp '
        WHEN  xtype = 231   THEN   ' nvarchar ' + ' ( ' + LTRIM (length) + ' ) '
        WHEN  xtype = 239   THEN   ' nchar ' + ' ( ' + LTRIM (length) + ' ) '
        WHEN  xtype = 241   THEN   ' xml '
        WHEN  xtype = 231   THEN   ' sysname '   END + ' , ' + CHAR ( 10 ),
    @INSERT_REMARK = @INSERT_REMARK + ' ,@ ' + NAME,
    @INSERT_COLUMN = @INSERT_COLUMN + ' , ' + NAME
  FROM
  ( 
   SELECT  NAME,CDEFAULT,A.ID,XTYPE,LENGTH,COLORDER
   FROM  SYSCOLUMNS A 
   WHERE  A.ID = OBJECT_ID ( '' + @TABLENAME + '' )
   AND   COLUMNPROPERTY (A.ID,A.NAME, ' IsIdentity ' ) <> 1  
  )TB
  ORDER   BY  COLORDER
  SET   @INSERT_DESCRIPTION = @INSERT_DESCRIPTION + ' /*+--------------------------------------+ ' + CHAR ( 10 )
  SET   @INSERT_DESCRIPTION = @INSERT_DESCRIPTION + ' | 过程名称：SP_ ' + @TABLENAME + ' _AddNew ' + CHAR ( 10 )
  SET   @INSERT_DESCRIPTION = @INSERT_DESCRIPTION + ' | 功能说明：插入表 ' + @TABLENAME + ' 的存储过程 ' + CHAR ( 10 )
  SET   @INSERT_DESCRIPTION = @INSERT_DESCRIPTION + ' | 入口参数： ' + STUFF ( @INSERT_REMARK , 1 , 1 , '' ) + '' + CHAR ( 10 )
  SET   @INSERT_DESCRIPTION = @INSERT_DESCRIPTION + ' | 过程返回：无返回记录 ' + CHAR ( 10 )
  SET   @INSERT_DESCRIPTION = @INSERT_DESCRIPTION + ' | 维护记录：Y/A ' + CHAR ( 10 )
  SET   @INSERT_DESCRIPTION = @INSERT_DESCRIPTION + ' | 使用案例：SP_ ' + @TABLENAME + ' _AddNew ' + CHAR ( 10 )
  SET   @INSERT_DESCRIPTION = @INSERT_DESCRIPTION + ' | 工作站名： ' + @HOST_NAME + '' + CHAR ( 10 )
  SET   @INSERT_DESCRIPTION = @INSERT_DESCRIPTION + ' | 联系方式：ZLP321001@HOTMAIL.COM ' + CHAR ( 10 )
  SET   @INSERT_DESCRIPTION = @INSERT_DESCRIPTION + ' | 创建日期： ' + CONVERT ( VARCHAR ( 20 ), @GET_DATE , 120 ) + '' + CHAR ( 10 ) 
  SET   @INSERT_DESCRIPTION = @INSERT_DESCRIPTION + ' +--------------------------------------+*/ ' + CHAR ( 10 )
  SELECT   @INSERT_SQLROC = @INSERT_SQLROC + CHAR ( 10 ) + @INSERT_DESCRIPTION + ' CREATE PROC SP_ ' + @TABLENAME + ' _AddNew '
  SET   @INSERT_SQLROC = @INSERT_SQLROC + CHAR ( 13 ) + CHAR ( 10 ) + LEFT ( @INSERT_PARAMETER , LEN ( @INSERT_PARAMETER ) - 2 ) + CHAR ( 10 )
  SET   @INSERT_SQLROC = @INSERT_SQLROC + ' AS ' + CHAR ( 10 ) + ' BEGIN '
  SET   @INSERT_SQLROC = @INSERT_SQLROC + CHAR ( 10 ) + SPACE ( 4 ) + ' SET NOCOUNT ON '
  SET   @INSERT_SQL = SPACE ( 8 ) + ' INSERT INTO  ' + @TABLENAME + ' ( ' + STUFF ( @INSERT_COLUMN , 1 , 1 , '' ) + ' ) ' + CHAR ( 13 ) + CHAR ( 10 ) + SPACE ( 8 ) + '  SELECT  ' + STUFF ( @INSERT_REMARK , 1 , 1 , '' )
  SET   @INSERT_SQLROC = @INSERT_SQLROC + CHAR ( 10 ) + CHAR ( 32 ) + @INSERT_SQL
  SET   @INSERT_SQLROC = @INSERT_SQLROC + CHAR ( 10 ) + SPACE ( 8 ) + '  SELECT _ROWCOUNT=@@ROWCOUNT '
  SET   @INSERT_SQLROC = @INSERT_SQLROC + CHAR ( 10 ) + SPACE ( 4 ) + ' SET NOCOUNT OFF '  
  SET   @INSERT_SQLROC = @INSERT_SQLROC + CHAR ( 10 ) + ' END '
  PRINT   @INSERT_SQLROC + CHAR ( 10 ) + ' GO  '
  -- EXEC(@INSERT_SQLROC)
  -- EXEC SP_EXECUTESQL @INSERT_SQLROC
  END
  IF   @FLAT = 2   -- 修改
  BEGIN
  DECLARE   @UPDATE_SQLROC   VARCHAR ( 8000 )
  DECLARE   @UPDATE_SQL   VARCHAR ( 8000 ) 
  DECLARE   @UPDATE_PARAMETER   VARCHAR ( 1000 )
  DECLARE   @UPDATE_DESCRIPTION   VARCHAR ( 1000 )
  DECLARE   @UPDATE_REMARK   VARCHAR ( 1000 )
  DECLARE   @UPDATE_KEY_COLUMN   VARCHAR ( 1000 )
  DECLARE   @UPDATE_COLUMN   VARCHAR ( 5000 )
  SELECT    @UPDATE_SQLROC = '' ,
    @UPDATE_SQL = '' , @UPDATE_PARAMETER = '' , @UPDATE_DESCRIPTION = '' ,
    @UPDATE_REMARK = '' , @UPDATE_KEY_COLUMN = '' , @UPDATE_COLUMN = ''
  SET   @UPDATE_SQLROC = @UPDATE_SQLROC + ' IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID( '' SP_ ' + @TABLENAME + ' _Update '' ) AND XTYPE IN (N '' P '' )) ' + CHAR ( 10 )
  SET   @UPDATE_SQLROC = @UPDATE_SQLROC + SPACE ( 5 ) + ' DROP PROC SP_ ' + @TABLENAME + ' _Update ' + CHAR ( 10 )
  SET   @UPDATE_SQLROC = @UPDATE_SQLROC + '  GO  '
  SELECT   @UPDATE_PARAMETER = @UPDATE_PARAMETER + SPACE ( 4 ) + ' @ ' + LTRIM (NAME) + SPACE ( 30 - LEN (NAME)) +
      CASE   WHEN  xtype = 34   THEN   ' image '
        WHEN  xtype = 35   THEN   ' text '
        WHEN  xtype = 36   THEN   ' uniqueidentifier '
        WHEN  xtype = 48   THEN   ' tinyint '
        WHEN  xtype = 52   THEN   ' smallint '
        WHEN  xtype = 56   THEN   ' int '
        WHEN  xtype = 58   THEN   ' smalldatetime '
        WHEN  xtype = 59   THEN   ' real '
        WHEN  xtype = 60   THEN   ' money '
        WHEN  xtype = 61   THEN   ' datetime '
        WHEN  xtype = 62   THEN   ' float '
        WHEN  xtype = 98   THEN   ' sql_variant '
        WHEN  xtype = 99   THEN   ' ntext '
        WHEN  xtype = 104   THEN   ' bit '
        WHEN  xtype = 106   THEN   ' decimal '
        WHEN  xtype = 108   THEN   ' numeric '
        WHEN  xtype = 122   THEN   ' smallmoney '
        WHEN  xtype = 127   THEN   ' bigint '
        WHEN  xtype = 165   THEN   ' varbinary '
           WHEN  xtype = 167   THEN   ' varchar ' + ' ( ' + LTRIM (length) + ' ) '
        WHEN  xtype = 173   THEN   ' binary '
        WHEN  xtype = 175   THEN   ' char ' + ' ( ' + LTRIM (length) + ' ) '
        WHEN  xtype = 189   THEN   ' timestamp '
        WHEN  xtype = 231   THEN   ' nvarchar ' + ' ( ' + LTRIM (length) + ' ) '
        WHEN  xtype = 239   THEN   ' nchar ' + ' ( ' + LTRIM (length) + ' ) '
        WHEN  xtype = 241   THEN   ' xml '
        WHEN  xtype = 231   THEN   ' sysname '   END + ' , ' + CHAR ( 10 )
  FROM  SYSCOLUMNS A
     WHERE  ID = OBJECT_ID ( '' + @TABLENAME + '' )
  SET  NOCOUNT  ON
  CREATE   TABLE  #(TABLE_QUALIFIER  VARCHAR ( 100 ),
 TABLE_OWNER   VARCHAR ( 100 ),
 TABLE_NAME    VARCHAR ( 100 ),
 COLUMN_NAME   VARCHAR ( 100 ),
 KEY_SEQ       VARCHAR ( 50 ),
 PK_NAME    VARCHAR ( 100 ))
  INSERT   INTO  #  EXEC  SP_PKEYS  @TABLENAME
  SELECT   @UPDATE_REMARK = @UPDATE_REMARK + ' , ' + COLUMN_NAME + ' =@ ' + COLUMN_NAME
  FROM  #
  SELECT   @UPDATE_KEY_COLUMN = @UPDATE_KEY_COLUMN + ' , ' + NAME + ' =@ ' + NAME
  FROM  SYSCOLUMNS A
     WHERE  ID = OBJECT_ID ( '' + @TABLENAME + '' )
  AND  NAME  NOT   IN  ( SELECT  COLUMN_NAME  FROM  #)
  DROP   TABLE  #
  SET  NOCOUNT  OFF
   IF   DATALENGTH ( @UPDATE_PARAMETER ) > 0
   BEGIN
    SET   @UPDATE_PARAMETER = LEFT ( @UPDATE_PARAMETER , LEN ( @UPDATE_PARAMETER ) - 2 )
    SET   @UPDATE_DESCRIPTION = @UPDATE_DESCRIPTION + ' /*+--------------------------------------+ ' + CHAR ( 10 )
    SET   @UPDATE_DESCRIPTION = @UPDATE_DESCRIPTION + ' | 过程名称：SP_ ' + @TABLENAME + ' _Update ' + CHAR ( 10 )
    SET   @UPDATE_DESCRIPTION = @UPDATE_DESCRIPTION + ' | 功能说明：更新表 ' + @TABLENAME + ' 的存储过程 ' + CHAR ( 10 )
    SET   @UPDATE_DESCRIPTION = @UPDATE_DESCRIPTION + ' | 入口参数： ' + STUFF ( @UPDATE_REMARK , 1 , 1 , '' ) + '' + CHAR ( 10 )
    SET   @UPDATE_DESCRIPTION = @UPDATE_DESCRIPTION + ' | 过程返回：无返回记录 ' + CHAR ( 10 )
    SET   @UPDATE_DESCRIPTION = @UPDATE_DESCRIPTION + ' | 维护记录：Y/A ' + CHAR ( 10 )
    SET   @UPDATE_DESCRIPTION = @UPDATE_DESCRIPTION + ' | 使用案例：SP_ ' + @TABLENAME + ' _Update ' + CHAR ( 10 )
    SET   @UPDATE_DESCRIPTION = @UPDATE_DESCRIPTION + ' | 工作站名： ' + @HOST_NAME + '' + CHAR ( 10 )
    SET   @UPDATE_DESCRIPTION = @UPDATE_DESCRIPTION + ' | 联系方式：ZLP321001@HOTMAIL.COM ' + CHAR ( 10 )
    SET   @UPDATE_DESCRIPTION = @UPDATE_DESCRIPTION + ' | 创建日期： ' + CONVERT ( VARCHAR ( 20 ), @GET_DATE , 120 ) + '' + CHAR ( 10 ) 
    SET   @UPDATE_DESCRIPTION = @UPDATE_DESCRIPTION + ' +--------------------------------------+*/ ' + CHAR ( 10 )
    SELECT   @UPDATE_SQLROC = @UPDATE_SQLROC + CHAR ( 10 ) + @UPDATE_DESCRIPTION + ' CREATE PROC SP_ ' + @TABLENAME + ' _Update '
    SET   @UPDATE_SQLROC = @UPDATE_SQLROC + CHAR ( 13 ) + CHAR ( 10 ) + @UPDATE_PARAMETER + CHAR ( 10 )
    SET   @UPDATE_SQLROC = @UPDATE_SQLROC + ' AS ' + CHAR ( 10 ) + ' BEGIN '
    SET   @UPDATE_SQLROC = @UPDATE_SQLROC + CHAR ( 10 ) + SPACE ( 4 ) + ' SET NOCOUNT ON ' + CHAR ( 10 )
    SET   @UPDATE_SQL = SPACE ( 8 ) + ' UPDATE  ' + @TABLENAME + CHAR ( 10 ) + SPACE ( 8 ) + ' SET  '
    SET   @UPDATE_SQLROC = @UPDATE_SQLROC + @UPDATE_SQL + STUFF ( @UPDATE_KEY_COLUMN , 1 , 1 , '' )
    SET   @UPDATE_SQLROC = @UPDATE_SQLROC + CHAR ( 10 ) + SPACE ( 8 ) + ' FROM  ' + @TABLENAME + CHAR ( 10 ) + SPACE ( 8 ) + ' WHERE  ' + STUFF ( @UPDATE_REMARK , 1 , 1 , '' ) + ''
    SET   @UPDATE_SQLROC = @UPDATE_SQLROC + CHAR ( 10 ) + SPACE ( 8 ) + ' SELECT _ROWCOUNT=@@ROWCOUNT '
    SET   @UPDATE_SQLROC = @UPDATE_SQLROC + CHAR ( 10 ) + SPACE ( 4 ) + ' SET NOCOUNT OFF '
    SET   @UPDATE_SQLROC = @UPDATE_SQLROC + CHAR ( 10 ) + ' END '  
    PRINT   @UPDATE_SQLROC + CHAR ( 13 ) + CHAR ( 10 ) + ' GO '
    -- EXECUTE(@UPDATE_SQLROC+' GO ')
   END
  END
  IF   @FLAT = 3   -- 删除
  BEGIN
  DECLARE   @DELETE_SQLROC   VARCHAR ( 8000 )
  DECLARE   @DELETE_SQL   VARCHAR ( 8000 ) 
  DECLARE   @DELETE_PARAMETER   VARCHAR ( 1000 )
  DECLARE   @DELETE_DESCRIPTION   VARCHAR ( 1000 )
  DECLARE   @DELETE_REMARK   VARCHAR ( 1000 )
  DECLARE   @DELETE_KEY_COLUMN   VARCHAR ( 1000 )
  SELECT    @DELETE_SQLROC = '' , @DELETE_SQL = '' , @DELETE_PARAMETER = '' , @DELETE_DESCRIPTION = '' ,
    @DELETE_REMARK = '' , @DELETE_KEY_COLUMN = ''
  SET    @DELETE_SQLROC = @DELETE_SQLROC + ' IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID( '' SP_ ' + @TABLENAME + ' _Remove '' ) AND XTYPE IN (N '' P '' )) ' + CHAR ( 10 )
  SET    @DELETE_SQLROC = @DELETE_SQLROC + SPACE ( 5 ) + ' DROP PROC SP_ ' + @TABLENAME + ' _Remove ' + CHAR ( 10 )
  SET    @DELETE_SQLROC = @DELETE_SQLROC + '  GO  '
  SET  NOCOUNT  ON
  CREATE   TABLE  #_#(TABLE_QUALIFIER  VARCHAR ( 100 ),
 TABLE_OWNER   VARCHAR ( 100 ),
 TABLE_NAME    VARCHAR ( 100 ),
 COLUMN_NAME   VARCHAR ( 100 ),
 KEY_SEQ       VARCHAR ( 50 ),
 PK_NAME    VARCHAR ( 100 ))
  INSERT   INTO  #_#  EXEC  SP_PKEYS  @TABLENAME
  SELECT   @DELETE_KEY_COLUMN = @DELETE_KEY_COLUMN + ' , ' + COLUMN_NAME + ' =@ ' + COLUMN_NAME,
    @DELETE_REMARK = @DELETE_REMARK + ' ,@ ' + COLUMN_NAME
  FROM  #_#
  SELECT   @DELETE_PARAMETER = @DELETE_PARAMETER + SPACE ( 4 ) + ' @ ' + LTRIM (NAME) + SPACE ( 30 - LEN (NAME)) +
      CASE   WHEN  xtype = 34   THEN   ' image '
        WHEN  xtype = 35   THEN   ' text '
        WHEN  xtype = 36   THEN   ' uniqueidentifier '
        WHEN  xtype = 48   THEN   ' tinyint '
        WHEN  xtype = 52   THEN   ' smallint '
        WHEN  xtype = 56   THEN   ' int '
        WHEN  xtype = 58   THEN   ' smalldatetime '
        WHEN  xtype = 59   THEN   ' real '
        WHEN  xtype = 60   THEN   ' money '
        WHEN  xtype = 61   THEN   ' datetime '
        WHEN  xtype = 62   THEN   ' float '
        WHEN  xtype = 98   THEN   ' sql_variant '
        WHEN  xtype = 99   THEN   ' ntext '
        WHEN  xtype = 104   THEN   ' bit '
        WHEN  xtype = 106   THEN   ' decimal '
        WHEN  xtype = 108   THEN   ' numeric '
        WHEN  xtype = 122   THEN   ' smallmoney '
        WHEN  xtype = 127   THEN   ' bigint '
        WHEN  xtype = 165   THEN   ' varbinary '
           WHEN  xtype = 167   THEN   ' varchar ' + ' ( ' + LTRIM (length) + ' ) '
        WHEN  xtype = 173   THEN   ' binary '
        WHEN  xtype = 175   THEN   ' char ' + ' ( ' + LTRIM (length) + ' ) '
        WHEN  xtype = 189   THEN   ' timestamp '
        WHEN  xtype = 231   THEN   ' nvarchar ' + ' ( ' + LTRIM (length) + ' ) '
        WHEN  xtype = 239   THEN   ' nchar ' + ' ( ' + LTRIM (length) + ' ) '
        WHEN  xtype = 241   THEN   ' xml '
        WHEN  xtype = 231   THEN   ' sysname '   END + ' , ' + CHAR ( 10 )
  FROM  SYSCOLUMNS A
     WHERE  ID = OBJECT_ID ( '' + @TABLENAME + '' ) 
  AND  NAME  IN  ( SELECT  COLUMN_NAME  FROM  #_#)
  DROP   TABLE  #_#
  SET  NOCOUNT  OFF
   IF   DATALENGTH ( @DELETE_PARAMETER ) > 0
   BEGIN
    SET   @DELETE_PARAMETER = LEFT ( @DELETE_PARAMETER , LEN ( @DELETE_PARAMETER ) - 2 )
    SET   @DELETE_DESCRIPTION = @DELETE_DESCRIPTION + ' /*+--------------------------------------+ ' + CHAR ( 10 )
    SET   @DELETE_DESCRIPTION = @DELETE_DESCRIPTION + ' | 过程名称：SP_ ' + @TABLENAME + ' _Remove ' + CHAR ( 10 )
    SET   @DELETE_DESCRIPTION = @DELETE_DESCRIPTION + ' | 功能说明：删除表 ' + @TABLENAME + ' 的存储过程 ' + CHAR ( 10 )
    SET   @DELETE_DESCRIPTION = @DELETE_DESCRIPTION + ' | 入口参数： ' + STUFF ( @DELETE_REMARK , 1 , 1 , '' ) + '' + CHAR ( 10 )
    SET   @DELETE_DESCRIPTION = @DELETE_DESCRIPTION + ' | 过程返回：无返回记录 ' + CHAR ( 10 )
    SET   @DELETE_DESCRIPTION = @DELETE_DESCRIPTION + ' | 维护记录：Y/A ' + CHAR ( 10 )
    SET   @DELETE_DESCRIPTION = @DELETE_DESCRIPTION + ' | 使用案例：SP_ ' + @TABLENAME + ' _Remove ' + CHAR ( 10 )
    SET   @DELETE_DESCRIPTION = @DELETE_DESCRIPTION + ' | 工作站名： ' + @HOST_NAME + '' + CHAR ( 10 )
    SET   @DELETE_DESCRIPTION = @DELETE_DESCRIPTION + ' | 联系方式：ZLP321001@HOTMAIL.COM ' + CHAR ( 10 )
    SET   @DELETE_DESCRIPTION = @DELETE_DESCRIPTION + ' | 创建日期： ' + CONVERT ( VARCHAR ( 20 ), @GET_DATE , 120 ) + '' + CHAR ( 10 ) 
    SET   @DELETE_DESCRIPTION = @DELETE_DESCRIPTION + ' +--------------------------------------+*/ ' + CHAR ( 10 )
    SELECT   @DELETE_SQLROC = @DELETE_SQLROC + CHAR ( 10 ) + @DELETE_DESCRIPTION + ' CREATE PROC SP_ ' + @TABLENAME + ' _Remove '
    SET   @DELETE_SQLROC = @DELETE_SQLROC + CHAR ( 13 ) + CHAR ( 10 ) + @DELETE_PARAMETER + CHAR ( 10 )
    SET   @DELETE_SQLROC = @DELETE_SQLROC + ' AS ' + CHAR ( 10 ) + ' BEGIN '
    SET   @DELETE_SQLROC = @DELETE_SQLROC + CHAR ( 10 ) + SPACE ( 4 ) + ' SET NOCOUNT ON '
    SET   @DELETE_SQL = ' DELETE  ' + @TABLENAME + CHAR ( 10 ) + SPACE ( 8 ) + ' WHERE  '
    SET   @DELETE_SQLROC = @DELETE_SQLROC + CHAR ( 10 ) + SPACE ( 8 ) + @DELETE_SQL + STUFF ( @DELETE_KEY_COLUMN , 1 , 1 , '' )
    SET   @DELETE_SQLROC = @DELETE_SQLROC + CHAR ( 10 ) + SPACE ( 8 ) + ' SELECT _ROWCOUNT=@@ROWCOUNT '
    SET   @DELETE_SQLROC = @DELETE_SQLROC + CHAR ( 10 ) + SPACE ( 4 ) + ' SET NOCOUNT OFF '
    SET   @DELETE_SQLROC = @DELETE_SQLROC + CHAR ( 10 ) + ' END '  
    PRINT   @DELETE_SQLROC + CHAR ( 13 ) + CHAR ( 10 ) + ' GO '   
    -- EXECUTE(@DELETE_SQLROC+' GO ') 
   END
  END
END

GO

CREATE   PROC  #SP_Generation_IUD
@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_IUD_P  @TABLENAME , 1
  EXEC  #AutoGeneration_IUD_P  @TABLENAME , 2
  EXEC  #AutoGeneration_IUD_P  @TABLENAME , 3
  SET   @TABLENAMES = RIGHT ( @TABLENAMES , LEN ( @TABLENAMES ) - @I )
  SET   @I = CHARINDEX ( ' , ' , @TABLENAMES )
END
IF   LEN ( @TABLENAMES ) > 0
BEGIN
  EXEC  #AutoGeneration_IUD_P  @TABLENAMES , 1
  EXEC  #AutoGeneration_IUD_P  @TABLENAMES , 2
  EXEC  #AutoGeneration_IUD_P  @TABLENAMES , 3
END
END

GO

#SP_Generation_IUD  ' EnterpriseNameTranslation '

drop   proc  #SP_Generation_IUD
drop   proc  #AutoGeneration_IUD_P

测试结果：

IF   EXISTS  ( SELECT   1   FROM  SYSOBJECTS  WHERE  ID = OBJECT_ID ( ' SP_EnterpriseNameTranslation_AddNew ' )  AND  XTYPE  IN  (N ' P ' ))
      DROP   PROC  SP_EnterpriseNameTranslation_AddNew
GO  
/**/ /*+--------------------------------------+
| 过程名称：SP_EnterpriseNameTranslation_AddNew
| 功能说明：插入表EnterpriseNameTranslation的存储过程
| 入口参数：@EnterpriseGuid,@EnterpriseName,@EnterpriseErrorName,@IsCheck,@InputData
| 过程返回：无返回记录
| 维护记录：Y/A
| 使用案例：SP_EnterpriseNameTranslation_AddNew
| 工作站名：CBD-YDPC
| 联系方式：ZLP321001@HOTMAIL.COM
| 创建日期：2007-09-14 00:54:49
+--------------------------------------+*/
CREATE   PROC  SP_EnterpriseNameTranslation_AddNew
     @EnterpriseGuid                  uniqueidentifier ,
     @EnterpriseName                  varchar ( 60 ),
     @EnterpriseErrorName             varchar ( 60 ),
     @IsCheck                         bit ,
     @InputData                       datetime
AS
BEGIN
     SET  NOCOUNT  ON
          INSERT   INTO  EnterpriseNameTranslation(EnterpriseGuid,EnterpriseName,EnterpriseErrorName,IsCheck,InputData)
          SELECT   @EnterpriseGuid , @EnterpriseName , @EnterpriseErrorName , @IsCheck , @InputData
          SELECT  _ ROWCOUNT = @@ROWCOUNT
     SET  NOCOUNT  OFF
END
GO  
IF   EXISTS  ( SELECT   1   FROM  SYSOBJECTS  WHERE  ID = OBJECT_ID ( ' SP_EnterpriseNameTranslation_Update ' )  AND  XTYPE  IN  (N ' P ' ))
      DROP   PROC  SP_EnterpriseNameTranslation_Update
  GO  
/**/ /*+--------------------------------------+
| 过程名称：SP_EnterpriseNameTranslation_Update
| 功能说明：更新表EnterpriseNameTranslation的存储过程
| 入口参数：EnterpriseGuid=@EnterpriseGuid
| 过程返回：无返回记录
| 维护记录：Y/A
| 使用案例：SP_EnterpriseNameTranslation_Update
| 工作站名：CBD-YDPC
| 联系方式：ZLP321001@HOTMAIL.COM
| 创建日期：2007-09-14 00:54:49
+--------------------------------------+*/
CREATE   PROC  SP_EnterpriseNameTranslation_Update
     @EnterpriseGuid                  uniqueidentifier ,
     @EnterpriseName                  varchar ( 60 ),
     @EnterpriseErrorName             varchar ( 60 ),
     @IsCheck                         bit ,
     @InputData                       datetime
AS
BEGIN
     SET  NOCOUNT  ON
         UPDATE  EnterpriseNameTranslation
         SET  EnterpriseErrorName = @EnterpriseErrorName ,EnterpriseName = @EnterpriseName ,InputData = @InputData ,IsCheck = @IsCheck
         FROM  EnterpriseNameTranslation
         WHERE  EnterpriseGuid = @EnterpriseGuid
         SELECT  _ ROWCOUNT = @@ROWCOUNT
     SET  NOCOUNT  OFF
END
GO
IF   EXISTS  ( SELECT   1   FROM  SYSOBJECTS  WHERE  ID = OBJECT_ID ( ' SP_EnterpriseNameTranslation_Remove ' )  AND  XTYPE  IN  (N ' P ' ))
      DROP   PROC  SP_EnterpriseNameTranslation_Remove
  GO  
/**/ /*+--------------------------------------+
| 过程名称：SP_EnterpriseNameTranslation_Remove
| 功能说明：删除表EnterpriseNameTranslation的存储过程
| 入口参数：@EnterpriseGuid
| 过程返回：无返回记录
| 维护记录：Y/A
| 使用案例：SP_EnterpriseNameTranslation_Remove
| 工作站名：CBD-YDPC
| 联系方式：ZLP321001@HOTMAIL.COM
| 创建日期：2007-09-14 00:54:49
+--------------------------------------+*/
CREATE   PROC  SP_EnterpriseNameTranslation_Remove
     @EnterpriseGuid                  uniqueidentifier
AS
BEGIN
     SET  NOCOUNT  ON
         DELETE  EnterpriseNameTranslation
         WHERE  EnterpriseGuid = @EnterpriseGuid
         SELECT  _ ROWCOUNT = @@ROWCOUNT
     SET  NOCOUNT  OFF
END
GO