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]
According to Bob Beauchemin here the order of precedence for stored procedures is:
this is different to batch or dynamic sql when the order is:
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With