根据表名生成有关Update的存储过程

 Create PROCEDURE [dbo].[P_CreateProcedure_Update]
      @TableName as Varchar(50)
AS
BEGIN

      SET LOCK_TIMEOUT 2000;
      SET XACT_ABORT ON;
      SET NOCOUNT ON;
     
      Declare @Date As Varchar(200)
      Set @Date=CONVERT(Varchar, GetDate(),121)

      Declare @PKName As Varchar(50)

      SELECT @PKName=syscolumns.name
      FROM syscolumns,sysobjects,sysindexes,sysindexkeys
      WHERE syscolumns.id = OBJECT_ID(@TableName)
      AND sysobjects.xtype = 'PK'
      AND sysobjects.parent_obj = syscolumns.id
      AND sysindexes.id = syscolumns.id
      AND sysobjects.name = sysindexes.name
      AND sysindexkeys.id = syscolumns.id
      AND sysindexkeys.indid = sysindexes.indid
      AND syscolumns.colid = sysindexkeys.colid

      --获取更新时间字段
      Declare @UpdateTimeName as Varchar(50)
      Set @UpdateTimeName=''
      Select @UpdateTimeName=syscolumns.name From syscolumns Where syscolumns.id = OBJECT_ID(@TableName) And Upper(syscolumns.name) Like '%UPDATETIME%'
      --获取生成时间字段
      Declare @CreateTimeName as Varchar(50)
      Set @CreateTimeName=''
      Select @CreateTimeName=syscolumns.name From syscolumns Where syscolumns.id = OBJECT_ID(@TableName) And Upper(syscolumns.name) Like '%CREATETIME%'
     
      IF (Len(@UpdateTimeName)=0 OR Len(@CreateTimeName)=0)
      Begin
            Return -1
      End
      --更新字段
      Declare @FieldsUpdate As Varchar(4000)
      Set @FieldsUpdate=dbo.fn_FieldsExceptPKAboutUpdat e(@TableName)
      --去掉UpdateTime和CreateTime字段
      Set @FieldsUpdate=Replace(@FieldsUpdate,@UpdateTimeName+'=@'+@UpdateTimeName,'')
      Set @FieldsUpdate=Replace(@FieldsUpdate,',,',',')
      Set @FieldsUpdate=Replace(@FieldsUpdate,@CreateTimeName+'=@'+@CreateTimeName,'')
      Set @FieldsUpdate=Replace(@FieldsUpdate,',,',',')
      --参数
      Declare @FieldsType  As Varchar(4000)
      Set @FieldsType=dbo.fn_FieldsTypeExceptPK(@TableName)
      --去掉UpdateTime和CreateTime参数
      Set @FieldsType=Left(@FieldsType,CharIndex(@UpdateTimeName,@FieldsType,0)-3)
      --格式优化
      Set @FieldsUpdate=Char(9)+Replace(@FieldsUpdate,',',','+Char(13)+Char(10)+Char(9))
      Set @FieldsType=Char(9)+Replace(@FieldsType,',',','+Char(13)+Char(10)+Char(9))


      --得到存储过程名称
      Declare @ObjectName AS Varchar(100)
      Set @ObjectName='P_'+@TableName+'_Update'
      --如果存储过程已经存在则删除;
      IF OBJECT_ID(@ObjectName)Is Not Null
      Begin
            Exec('Drop Procedure '+@ObjectName)
      END
      Declare @SQL nvarchar(max)
Set @SQL = N'
------------------------------------------------------
--Author:<Meng>
--Create Date:<'+@Date+'>
--Description:<Update ' + @TableName + 'Set... Where '+@PKName+' = @'+@PKName+'>
--Return Value:<0:执行成功>;
------------------------------------------------------
CREATE PROCEDURE [dbo].[P_'+@TableName+'_Update]
      @'+convert(char(29),@PKName)+'INT,
'+@FieldsType+'
AS
     
Update [dbo].['+@TableName+']
Set
'+@FieldsUpdate+@UpdateTimeName+'=GetDate()
WHERE      ['+@PKName+'] = @'+@PKName+'

RETURN 0
'
      EXEC (@SQL)
      Return 0
END