创建列后，在sql server上更新的列名无效

26 人关注

有谁发现SQL Server的这段代码有什么问题吗？

IF NOT EXISTS(SELECT *
              FROM   sys.columns
              WHERE  Name = 'OPT_LOCK'
                     AND object_ID = Object_id('REP_DSGN_SEC_GRP_LNK'))
  BEGIN
      ALTER TABLE REP_DSGN_SEC_GRP_LNK
        ADD OPT_LOCK NUMERIC(10, 0)
      UPDATE REP_DSGN_SEC_GRP_LNK
      SET    OPT_LOCK = 0
      ALTER TABLE REP_DSGN_SEC_GRP_LNK
        ALTER COLUMN OPT_LOCK NUMERIC(10, 0) NOT NULL
  END; 
当我运行这个时，我得到
  Msg 207, Level 16, State 1, Line 3

  无效的列名'OPT_LOCK'。
在更新命令中。

sql


         sql-server


         tsql

ddl


        3
        
        个回答


          
           
           
            Martin Smith
           
          
          
           发布于
           
           2012-10-25


          已采纳


         0
         
         人赞同


          
           在这种情况下，你可以通过添加列作为
           
            NOT NULL
           
           ，并在一条语句中设置现有行的值来避免这个问题，
           
            正如我在这里的回答
           
           。
          
          
           一般来说，这个问题是一个解析/编译的问题。在执行任何语句之前，SQL Server会尝试编译批处理中的所有语句。
          
          
           当一个语句引用一个根本不存在的表时，该语句会被推迟编译。当表已经存在时，如果你引用一个不存在的列，它就会抛出一个错误。解决这个问题的最好方法是在不同的批次中进行DDL和DML。
          
          
           如果一个语句同时引用了一个现有表中的非现有列和一个不存在的表，在推迟编译之前，可能会或不会抛出错误。
          
          
           你可以在不同的批次中提交（例如通过使用客户端工具中的批次分离器
           
            GO
           
           ），或者在一个子范围中执行，通过使用
           
            EXEC
           
           或
           
            EXEC sp_executesql
           
           ，单独进行编译。
          
          
           第一种方法需要你重构你的代码，因为一个
           
            IF ...
           
           不能跨越批次。
          
          IF NOT EXISTS(SELECT *
              FROM   sys.columns
              WHERE  Name = 'OPT_LOCK'
                     AND object_ID = Object_id('REP_DSGN_SEC_GRP_LNK'))
  BEGIN
      ALTER TABLE REP_DSGN_SEC_GRP_LNK
        ADD OPT_LOCK NUMERIC(10, 0)
      EXEC('UPDATE REP_DSGN_SEC_GRP_LNK SET OPT_LOCK = 0');
      ALTER TABLE REP_DSGN_SEC_GRP_LNK
        ALTER COLUMN OPT_LOCK NUMERIC(10, 0) NOT NULL
  END;


           
            
             Ola Berntsson
            
            ：


           
            
             这是我多年来一直使用的方法，而我们每次想增加一个新的列并为其填充数据时，都不得不采取这种笨拙的动态sql路线，这让我很惊讶。


           
            
             我同意Ola的观点。"当一个语句引用一个根本不存在的表时，该语句要进行延迟编译。当表已经存在时，如果你引用了一个不存在的列，它会抛出一个错误。"这种行为没有意义。对平行结构原则的简单应用表明，
             
              这两种
             
             情况都应该用延迟编译来处理。


          
           
            错误的根本原因是新添加的列名没有反映在sys.syscolumns和sys.columns表中，直到你重新启动SQL Server Management Studio。


           
            
             Martin Smith
            
            ：


           
            
             这不是真的。重启management studio为你做的只是刷新对象资源管理器和智能感应。这与查询执行错误或系统表和视图的内容没有关系。


          
           
            供你参考，你可以用
            
             COL_LENGTH
            
            函数代替
            
             IF NOT EXISTS
            
            。它需要两个参数。
           
           
            
             你要搜索的列
            
           
           
            如果找到该列，它将返回该列的数据类型范围，例如：Int（4字节），如果没有找到，它将返回一个
            
             NULL
            
            。
           
           
            所以，你可以按以下方式使用，也可以将3条语句合并为一条。
           
           IF (SELECT COL_LENGTH('REP_DSGN_SEC_GRP_LNK','OPT_LOCK')) IS NULL
BEGIN
    ALTER TABLE REP_DSGN_SEC_GRP_LNK
    ADD OPT_LOCK NUMERIC(10, 0) NOT NULL DEFAULT 0