mysql - 无法创建将列添加到我指定的任何表的存储过程

Question

我正在尝试制作一个允许我仅指定一个表的过程，该过程将自动向该表添加两列。

create proc addAuditFeild (@table_name nvarchar(50))
as
    if OBJECT_ID (@table_name, ''U'') is not null
    begin
        alter table @table_name
        add moduser varchar(50),
            moddate datetime
    end

我做错了什么？我尝试使用 exec 但它仍然没有用。我得到 scalr 变量未在我的 exec 中定义。

exec('alter proc addAuditFeild (@table_name nvarchar(50))
as
    if OBJECT_ID (@table_name, ''U'') is not null
    begin
        alter table ' + @table_name + '
        add moduser varchar(50),
            moddate datetime
    end')

您将如何创建一个可以将列添加到您在参数中指定的任何表的过程？

Answer 1

试试这样的......

ALTER PROCEDURE addAuditFeild 
@table_name SYSNAME
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @Sql NVARCHAR(MAX);

  -- check first if the table exists
  IF (OBJECT_ID (@table_name, 'U') IS NOT NULL)
   BEGIN

       -- then check if column "moduser" already exist if not then add one
       IF NOT EXISTS(SELECT 1 FROM sys.tables t 
                      INNER JOIN sys.columns c on t.object_id = c.object_id
                      WHERE t.name = @table_name
                      AND c.name = 'moduser')
        BEGIN
         SET @Sql = N' ALTER TABLE ' + QUOTENAME(@table_name)
                  + N' ADD moduser varchar(50)'

           exec sp_executesql @Sql
        END

       -- then check if column "moddate" already exist if not then add one
       IF NOT EXISTS(SELECT 1 FROM sys.tables t 
                      INNER JOIN sys.columns c on t.object_id = c.object_id
                      WHERE t.name = @table_name
                      AND c.name = 'moddate')
        BEGIN
         SET @Sql = N' ALTER TABLE ' + QUOTENAME(@table_name)
                  + N' ADD moddate datetime'

           exec sp_executesql @Sql
        END
   END
END

编辑

要处理架构问题，请执行以下操作......

ALTER PROCEDURE addAuditFeild 
  @table_name SYSNAME
 ,@Schem_Name SYSNAME
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @Sql NVARCHAR(MAX);

  IF (OBJECT_ID (QUOTENAME(@Schem_Name)+ '.' + QUOTENAME(@table_name), 'U') IS NOT NULL)
   BEGIN
       IF NOT EXISTS(SELECT 1 FROM sys.tables t 
                   INNER JOIN sys.columns c on t.object_id = c.object_id
                   INNER JOIN sys.schemas s on t.schema_id = s.schema_id
                   WHERE t.name = @table_name
                    AND  s.name = @Schem_Name
                    AND c.name = 'moduser')
        BEGIN
         SET @Sql = N' ALTER TABLE ' + QUOTENAME(@Schem_Name)+ '.' + QUOTENAME(@table_name)
                     + N' add moduser varchar(50)'

           exec sp_executesql @Sql
        END

       IF NOT EXISTS(SELECT 1 FROM sys.tables t 
                   INNER JOIN sys.columns c on t.object_id = c.object_id
                   INNER JOIN sys.schemas s on t.schema_id = s.schema_id
                   WHERE t.name = @table_name
                    AND  s.name = @Schem_Name
                    AND c.name = 'moddate')
        BEGIN
         SET @Sql = N' ALTER TABLE ' + QUOTENAME(@Schem_Name)+ '.' + QUOTENAME(@table_name)
                     + N' add moddate datetime'

           exec sp_executesql @Sql
        END
   END
END