November 15, 2015 at 7:05 pm
I have a SP which queries a table in another database. There is an abnormal behavior when creating the SP.
1. When I get the column name of a local table wrong
AND
if table in the other database is NOT present - there is no error about the incorrect column name.
2. When I get the column name of a local table wrong
AND
if table in the other database IS present - there is an error about the incorrect column name.
This is not a critical issue as such, but I'm intrigued to know why this happens. Any input please.
SELECT @@VERSION
/*Microsoft SQL Server 2012 - 11.0.2218.0 (X64)
Jun 12 2012 13:05:25
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)*/
CREATE DATABASE Third_Party ON PRIMARY
(
NAME = N'Third_Party', FILENAME=N'C:\MSSQL_DATA\DATA\Third_Party.mdf',
SIZE = 2GB, MAXSIZE = 8GB, FILEGROWTH = 1GB
)
LOG ON
(
NAME = N'Third_Party_Log', FILENAME=N'C:\MSSQL_DATA\DATA\Third_Party.ldf',
SIZE = 2GB, MAXSIZE = 8GB, FILEGROWTH = 1GB
)
GO
CREATE DATABASE Dealer ON PRIMARY
(
NAME = N'Dealer', FILENAME=N'C:\MSSQL_DATA\DATA\Dealer.mdf',
SIZE = 2GB, MAXSIZE = 8GB, FILEGROWTH = 1GB
)
LOG ON
(
NAME = N'Dealer_Log', FILENAME=N'C:\MSSQL_DATA\DATA\Dealer.ldf',
SIZE = 2GB, MAXSIZE = 8GB, FILEGROWTH = 1GB
)
GO
----------------------------------------------------
USE Third_Party
GO
IF OBJECT_ID('dbo.Location_Dummy') IS NOT NULL DROP TABLE dbo.Location_Dummy
CREATE TABLE dbo.Location_Dummy
(
Location_ID INT,
Location NVARCHAR(50)
)
go
USE Dealer
GO
IF OBJECT_ID('dbo.Store') IS NOT NULL DROP TABLE dbo.Store
CREATE TABLE dbo.Store
(
Store_Name NVARCHAR(100),
Store_Location NVARCHAR(50)
)
GO
USE Dealer
GO
--This Create SP script does not throw an error, even though Store.Store_Code is not present
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'Test_Column_Name_When_Table_Not_Present' AND ROUTINE_TYPE = 'procedure') DROP PROCEDURE Test_Column_Name_When_Table_Not_Present
GO
CREATE PROCEDURE dbo.Test_Column_Name_When_Table_Not_Present
AS
BEGIN
--Column Store_Code is not present
SELECT s.Store_Name, s.Store_Location, s.Store_Code
FROM dbo.Store s
WHERE s.Store_Location IN
(
--Table Third_Party.dbo.Location is not present
SELECT l.Location FROM Third_Party.dbo.Location l
)
END
GO
--This Create SP script throws an error, as expected
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'Test_Column_Name_When_Table_Present' AND ROUTINE_TYPE = 'procedure') DROP PROCEDURE Test_Column_Name_When_Table_Present
GO
CREATE PROCEDURE dbo.Test_Column_Name_When_Table_Present
AS
BEGIN
--Column Store_Code is not present
SELECT s.Store_Name, s.Store_Location, s.Store_Code
FROM dbo.Store s
WHERE s.Store_Location IN
(
--Table Third_Party.dbo.Location_Dummy is present
SELECT ld.Location FROM Third_Party.dbo.Location_Dummy ld
)
END
GO
https://sqlroadie.com/
November 16, 2015 at 12:29 pm
If the table does not exist at all, SQL defers name resolution ("deferred name resolution") for that table. That is, it waits until run time, then checks to see if the table exists and checks the column names. This is intentional, to allow you to create code before the object exists.
But, if the table does exist, SQL can verify all the column names from the start, and thus won't allow an incorrect name.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 16, 2015 at 4:04 pm
ScottPletcher (11/16/2015)
If the table does not exist at all, SQL defers name resolution ("deferred name resolution") for that table.<snip/>
Thanks for the reply Scott. If a table does not exist, is name resolution for all tables of that query deferred? It looks so.
In my example, table Store is present and table in the other DB is not present. I'm selecting a non-existent column in an existing table (--no error)
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'Test_Column_Name_When_Table_Not_Present' AND ROUTINE_TYPE = 'procedure') DROP PROCEDURE Test_Column_Name_When_Table_Not_Present
GO
CREATE PROCEDURE dbo.Test_Column_Name_When_Table_Not_Present
AS
BEGIN
--Column Store_Code is not present
SELECT s.Store_Name, s.Store_Location, s.Store_Code
FROM dbo.Store s
WHERE s.Store_Location IN
(
--Table Third_Party.dbo.Location is not present
SELECT l.Location FROM Third_Party.dbo.Location l
)
END
GO
https://sqlroadie.com/
November 16, 2015 at 4:19 pm
Arjun Sivadasan (11/16/2015)
ScottPletcher (11/16/2015)
If the table does not exist at all, SQL defers name resolution ("deferred name resolution") for that table.<snip/>
Thanks for the reply Scott. If a table does not exist, is name resolution for all tables of that query deferred? It looks so.
In my example, table Store is present and table in the other DB is not present. I'm selecting a non-existent column in an existing table (--no error)
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'Test_Column_Name_When_Table_Not_Present' AND ROUTINE_TYPE = 'procedure') DROP PROCEDURE Test_Column_Name_When_Table_Not_PresentGO
CREATE PROCEDURE dbo.Test_Column_Name_When_Table_Not_Present
AS
BEGIN
--Column Store_Code is not present
SELECT s.Store_Name, s.Store_Location, s.Store_Code
FROM dbo.Store s
WHERE s.Store_Location IN
(
--Table Third_Party.dbo.Location is not present
SELECT l.Location FROM Third_Party.dbo.Location l
)
END
GO
Hmm, interesting. I guess the entire statement is deferred if a table within it has to be deferred. That makes sense when you think about it: SQL can't validate data types for comparisons between tables if one of the table's schema's isn't even known!
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 16, 2015 at 4:47 pm
Yup, I agree. Thanks for your time.
https://sqlroadie.com/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply