根据表名生成有关Update的存储过程_根据表结构生成up-CSDN博客

本文介绍了一种使用T-SQL动态生成更新记录的存储过程的方法。该方法通过查找表的主键、更新时间和创建时间字段，并排除这些字段，来构建更新语句。此过程包括获取表结构信息、构造存储过程及参数等步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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_FieldsExceptPKAboutUpdate(@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