Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL default schema resolution in Stored Procedure

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]
like image 671
SimonJ Avatar asked Oct 22 '25 06:10

SimonJ


2 Answers

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

  • Look in 'sys' schema
  • Look in the schema of your procedure
  • Look in dbo schema

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

  • Look in 'sys' schema
  • Look in users default schema
  • look in dbo schema
like image 125
Steve Ford Avatar answered Oct 23 '25 22:10

Steve Ford


After you dropped s1.TestTable table, it will default to dbo, and because you didn't specify in the schema in the proc. If you change the proc to select from s1.TestTable in s1.gettesttable, you will get an error

like image 41
Jaques Avatar answered Oct 23 '25 20:10

Jaques