June 11, 2024 at 9:39 am
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'
June 11, 2024 at 4:24 pm
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)
June 11, 2024 at 4:36 pm
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.
June 11, 2024 at 5:18 pm
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".
June 11, 2024 at 6:13 pm
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
June 11, 2024 at 6:19 pm
June 12, 2024 at 12:38 pm
EDIT - see my corrected conclusion below
June 12, 2024 at 3:03 pm
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.
June 13, 2024 at 5:47 am
This was removed by the editor as SPAM
June 13, 2024 at 7:43 am
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.
June 13, 2024 at 7:54 am
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?
June 13, 2024 at 8:03 am
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.
June 13, 2024 at 8:13 am
Makes sense, thanks for clarifying.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply