SQL Server 如果引用不存在的表/列,则导致创建存储过程失败

pbwdgjma  于 2022-12-10  发布在  其他
关注(0)|答案(2)|浏览(134)

给定以下存储过程定义

create procedure myproc
as

select * 
from sometablethatdoesnotexist

select somecolumnthatdoesnotexist 
from sometablethatdoesexist

如果在创建存储过程时这些对象(列或表)不存在,是否可以将sql-server(或localdb)配置为使存储过程的创建失败?

z0qdvdin

z0qdvdin1#

No.
This is SQL Server's deferred name resolution.
There is no way of turning it off . It will fail if you reference a non existent column on an existing table though.

bxfogqkk

bxfogqkk2#

对于任何人谁绊倒在这个问题上,有一个黑客概述https://dba.stackexchange.com/users/11635/geoff-patterson这里https://dba.stackexchange.com/questions/13522/is-there-a-way-to-force-deferred-name-resolution-even-if-the-table-exists-when-c
下面是他的代码示例-临时表是关键。

--Say this table already exists.
CREATE TABLE myTable
(
    a NVARCHAR(MAX)
)
GO

--My C# code creates something like this
BEGIN TRAN 
GO

--the sproc gets generated first.
CREATE PROCEDURE mySproc
AS
BEGIN
    CREATE TABLE #deferResolution (dummy INT NOT NULL)
    SELECT a,b FROM myTable WHERE NOT EXISTS (SELECT * FROM #deferResolution WHERE 0=1)
END

--then the table update
ALTER TABLE myTable
    ADD b nvarchar(MAX)

COMMIT TRAN

相关问题