存储过程中的 SQL 默认架构解析 [英] SQL default schema resolution in Stored Procedure

问题描述

在 MS SQL (2008 R2) 中，我凭经验发现，在以下 SQL 中，存储过程从定义该过程的同一架构中的表中返回数据，但将默认返回到用户的默认架构如果没有找到这样的.
虽然这看起来很合乎逻辑，但有没有人有关于 MSSQL 在访问存储过程中的非完全限定表时使用的架构解析的优先顺序的章节?

假设当前用户的默认架构是 dbo.

In MS SQL (2008 R2), I have discovered, empirically, that in the following SQL, a stored procedure returns data from the table in the same schema in which the procedure is defined but will default back to the user's default schema if none such is found.
Whilst this seems logical, does anyone have chapter and verse on what order of precedence for schema resolution MSSQL uses when accessing non fully qualified tables in stored procedures?

Assume that the current user has dbo as their default schema.

CREATE SCHEMA [s1]
GO
CREATE TABLE [dbo].[TestTable] ([Id] INT)
GO
CREATE TABLE [s1].[TestTable] ([AnotherId] INT)
GO

CREATE PROCEDURE [dbo].[GetTestTable]
AS BEGIN
    SELECT * FROM [TestTable]
END
GO
CREATE PROCEDURE [s1].[GetTestTable]
AS BEGIN
    SELECT * FROM [TestTable]
END
GO

EXEC [dbo].[GetTestTable]
-- Returns [Id]

EXEC [s1].[GetTestTable]
-- Returns [AnotherId]

DROP TABLE [s1].[TestTable]
GO

EXEC [s1].[GetTestTable]
-- Returns [Id]

推荐答案

根据 Bob Beauchemin 这里 存储过程的优先顺序是:

According to Bob Beauchemin here the order of precedence for stored procedures is:

• 查看sys"架构
• 查看程序的架构
• 查看 dbo 架构

这与批处理或动态 sql 的顺序不同:

this is different to batch or dynamic sql when the order is:

• 查看sys"架构
• 查看用户默认架构
• 查看 dbo 架构

这篇关于存储过程中的 SQL 默认架构解析的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！