Stored Procedure - Error not found when temp table is used in join
-
Dear all,
what I learned is that Stored Procedures (SP) are not compiled before they are executed the first time, but a syntax check takes place when creating it.
What I don't understand is, that e.g. a wrong table column in a join is not leading to an error message as soon as a temp table is involved.
Pls check the example below:
The first procedure is successfully created and the error occours when it is executed.
When I replace the temp table with a table variable, the SP cannot be created.
That is a difference between SAP ASE (error in first example) and SQL Server that I stumbled upon while migrating, and this hurts me a lot:)
1. can so. explain the difference between the two examples?
2. Is there a way how to can error check the procedure in example 1 before executing it?
create table a (a int)
go
create or alter proc test
as
begin
create table #c (c int)
select * from #c C join a A on A.ZZZZ = C.c
end -- successful
exec test -- Invalid column name 'ZZZZ'
go
create or alter proc test
as
begin
declare @c table (c int)
select * from @c C join a A on A.ZZZZ = C.c
end -- Invalid column name 'ZZZZ'- This topic was modified 3 months, 1 week ago by Arthur Kirchner.
-
There is no column ZZZZ in table a. The only column in table a is a
create table a (a int) would need to change to create table a (ZZZZ int)
- Ross McMicken wrote:
There is no column ZZZZ in table a. The only column in table a is a
create table a (a int) would need to change to create table a (ZZZZ int)
That's not the point. The poster is asking why SQL Server allows the creation of a proc containing such an obvious error, not how to fix it.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Because SQL, deliberately, has delayed verification. This allows you to create a proc before other objects exist. For example, this should run fine:
USE tempdb;
GO
CREATE PROC proc1 AS SELECT * FROM dbo.nosuchtable;
GO
DROP PROC proc1
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
- Arthur Kirchner wrote:
1. can so. explain the difference between the two examples?
<Deleted incorrect information suggesting columns can be added to existing tables within a stored procedure.>
Variables can not be conditionally declared so any reference to a variable must be accurate, which is why the code with the table variable doesn't compile. One might expect the code below to fail because the declare is inside a conditional block that won't be executed, however, the table variable is declared and the query completes. The table variable is empty because the insert did follow the conditional logic.
if 1 = 2
begin
declare @this table (that int)
insert @this values (1)
end
select *
from @this -
- At a guess I would say the difference stems from the fact that because the temp table isn't actually created and thus has no metadata the compiler can check against. In contrast the table variable becomes part of the sp and therefore has metadata available for checking. And because one side of the join is not present the compiler drops checking the other side.
- I would execute the sp from SSMS with the "Display Estimated Execution Plan" (Ctrl L) option, which will show the error without actually doing anything.
-
EDIT - see my corrected conclusion below
- Arthur Kirchner wrote:
It seems that there is no other way than to check the temp table joins in the procedures by hand.
Not exactly by hand. Intellisense identifies them.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
It's deferred name resolution.
You can create a procedure if the table does not exist but not if the table exists and the columns do not exist.
-
This was removed by the editor as SPAM
-
Dear all,
as Phil and Jonathan gave me valuable input I'll correct my conclusion here:
1. I understand that due to deferred name resolution the temp table is not evaluated when it is created inside the procedure. Any joins to existing tables are then also "only" syntax- , not metadata-checked. (you get a lot of interesting hits when searching "deferred name resolution")
2. In SSMS you can identify erroneous joins in the procedure code when Instellisense is enabled. You can set Options|Text editor|Intellisense|Maximum Script size to unlimited, if you want to try sth. funny like paste > 100K of code in one file as I'll try to do.
- Arthur Kirchner wrote:
That is a difference between SAP ASE (error in first example) and SQL Server that I stumbled upon while migrating, and this hurts me a lot:)
I shall address the elephant in the room:
If the code you are migrating contains errors, presumably it was never executed – so why are you migrating it and how did it get there in the first place?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
ASE code was fine, the procedures had to be migrated and the code changed:
They were way too complex to accomplish this with the Migration Assistant, and so the way was clear for human errors.
-
Makes sense, thanks for clarifying.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply