为什么 Microsoft SQL Server 检查存储过程中的列而不检查表? [英] Why does Microsoft SQL Server check columns but not tables in stored procs?

问题描述

Microsoft SQL Server 似乎在定义存储过程时检查列名有效性，但不检查表名有效性.如果它检测到当前存在引用的表名，它会根据该表中的列验证语句中的列名.因此，例如，这将运行正常:

Microsoft SQL Server seems to check column name validity, but not table name validity when defining stored procedures. If it detects that a referenced table name exists currently, it validates the column names in a statement against the columns in that table. So, for example, this will run OK:

CREATE PROCEDURE [dbo].[MyProcedure]
AS
BEGIN
    SELECT
        Col1, Col2, Col3
    FROM
        NonExistentTable
END
GO

...就像这样:

CREATE PROCEDURE [dbo].[MyProcedure]
AS
BEGIN
    SELECT
        ExistentCol1, ExistentCol2, ExistentCol3
    FROM
        ExistentTable
END
GO

...但这失败了，'无效的列名':

... but this fails, with 'Invalid column name':

CREATE PROCEDURE [dbo].[MyProcedure]
AS
BEGIN
    SELECT
        NonExistentCol1, NonExistentCol2, NonExistentCol3
    FROM
        ExistentTable
END
GO

为什么 SQL Server 会检查列而不是表的存在?肯定是不一致的；它应该两者都做，或者两者都不做.能够定义可能引用模式中尚不存在的表 AND/OR 列的 SP 对我们很有用，那么有没有办法关闭 SQL Server 对当前表中列存在的检查存在?

Why does SQL Server check columns, but not tables, for existence? Surely it's inconsistent; it should do both, or neither. It's useful for us to be able to define SPs which may refer to tables AND/OR columns which don't exist in the schema yet, so is there a way to turn off SQL Server's checking of column existence in tables which currently exist?

推荐答案

这称为延迟名称解析.

没有办法关闭它.您可以使用动态 SQL 或(一个讨厌的黑客！)添加对不存在的表的引用，以便推迟该语句的编译.

There is no way of turning it off. You can use dynamic SQL or (a nasty hack!) add a reference to a non existent table so that compilation of that statement is deferred.

CREATE PROCEDURE [dbo].[MyProcedure]
AS
BEGIN

CREATE TABLE #Dummy (c int)

    SELECT
        NonExistantCol1, NonExistantCol2, NonExistantCol3
    FROM
        ExistantTable 
    WHERE NOT EXISTS(SELECT * FROM #Dummy)    


DROP TABLE #Dummy

END
GO

这篇关于为什么 Microsoft SQL Server 检查存储过程中的列而不检查表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！