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

 

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
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值