August 14, 2013 at 10:18 pm
Comments posted to this topic are about the item Object binding
August 15, 2013 at 12:36 am
Maybe a clear definition of succesful execution would be helpful. In my opinion the statement execute runs fine if the procedure exists. The procedure throws an error not the execute statement.
August 15, 2013 at 2:42 am
Good question,
Just a quick BOL reference to clarify things
A procedure can reference tables that do not yet exist. At creation time, only syntax checking is performed. The procedure is not compiled until it is executed for the first time. Only during compilation are all objects referenced in the procedure resolved. Therefore, a syntactically correct procedure that references tables that do not exist can be created successfully; however, the procedure will fail at execution time if the referenced tables do not exist.
August 15, 2013 at 2:53 am
Good question, though even if you didn't know the answer the "choose 4" hints should give it away - I guess the 50% (!) getting it wrong didn't notice this.
The explanation is a bit suspect though.
August 15, 2013 at 3:49 am
This was removed by the editor as SPAM
August 15, 2013 at 3:53 am
Interesting. The explanation ought to cite deferred name resolution as the reason it's possible to create stored procedures referencing non-existent tables. The paragraph from BOL quoted is not entirely accurate since not all objects are resolved - only those that are touched in the flow logic.
John
August 15, 2013 at 7:00 am
Thanks for the question... glad I managed to get through that one without morning caffeine. 🙂
August 15, 2013 at 7:21 am
Thomas Abraham (8/15/2013)
Thanks for the question. Relearned something I had forgotten, as I do many things at work besides programming - and my brain is getting older! 😉
+ 1
August 15, 2013 at 7:24 am
I was one of the people who got it wrong... <facepalm/>
Even without reading the explanation, I realized my error... maybe I should drink my first cup of coffee in the morning before I attempt to engage brain.
Thanks for the good Q!
August 15, 2013 at 7:45 am
John Mitchell-245523 (8/15/2013)
Interesting. The explanation ought to cite deferred name resolution as the reason it's possible to create stored procedures referencing non-existent tables. The paragraph from BOL quoted is not entirely accurate since not all objects are resolved - only those that are touched in the flow logic.John
EDIT: The following seems to be incorrect - see Toreador's post below.
SQL Server will attempt to resolve all objects except those that it can determine will NEVER be necessary to return results when it parses the query. Otherwise it will attempt to resolve and bind all object references in all logical branches of a query because it can't know which branch will actually execute at runtime. I just wanted to clarify that because we occasionally see questions along the lines of "My stored procedure has an IF statement where one branch has a table that may not exist - why does it throw a non-existent table error when I run it with parameter values that should direct it to the other branch?"
Here, SQL Server recognizes that "1<>1" will NEVER evaluate to TRUE, so it doesn't bother to parse and bind the IF branch that depends on that condition. If you change the stored procedure to use some condition for which SQL Server can't conclusively rule out a TRUE result (such as "1<>@int" - at runtime, the value of int may or may not be 1), the attempt to execute it will fail.
Jason Wolfkill
August 15, 2013 at 7:47 am
Toreador (8/15/2013)
Good question, though even if you didn't know the answer the "choose 4" hints should give it away - I guess the 50% (!) getting it wrong didn't notice this.The explanation is a bit suspect though.
I thought the 4th batch would throw an error because of the non-existing table in a "reachable" point of the flow, and then the 5th batch would execute successfully the original version of the procedure.
August 15, 2013 at 7:55 am
wolfkillj (8/15/2013)
If you change the stored procedure to use some condition for which SQL Server can't conclusively rule out a TRUE result (such as "1<>@int" - at runtime, the value of int may or may not be 1), the attempt to execute it will fail.
I assume you didn't try this, as it's not true.
The procedure will only fail at runtime if that particular select is reached. If the logic means that it is not reached then it will not fail, regardless of whether it is reachable or not.
So the following with both compile and execute
create procedure dbo.test_existing
as
declare @i int
set @i=1
if (@i<>1)
begin
select c from dbo.non_existing;
end
else
begin
select c from dbo.existing;
end
go
--Third batch
execute dbo.test_existing;
go
August 15, 2013 at 8:09 am
SQL Server will attempt to resolve all objects except those that it can determine will NEVER be necessary to return results when it parses the query. Otherwise it will attempt to resolve and bind all object references in all logical branches of a query because it can't know which branch will actually execute at runtime. I just wanted to clarify that because we occasionally see questions along the lines of "My stored procedure has an IF statement where one branch has a table that may not exist - why does it throw a non-existent table error when I run it with parameter values that should direct it to the other branch?"
Here, SQL Server recognizes that "1<>1" will NEVER evaluate to TRUE, so it doesn't bother to parse and bind the IF branch that depends on that condition. If you change the stored procedure to use some condition for which SQL Server can't conclusively rule out a TRUE result (such as "1<>@int" - at runtime, the value of int may or may not be 1), the attempt to execute it will fail.
Good explanation - thanks! Have you considered becoming an author for BOL?
I assume you didn't try this, as it's not true.
The procedure will only fail at runtime if that particular select is reached. If the logic means that it is not reached then it will not fail, regardless of whether it is reachable or not.
So the following with both compile and execute
But that's the whole point. The non-existent table isn't reachable, and so it doesn't fail.
John
August 15, 2013 at 8:22 am
John Mitchell-245523 (8/15/2013)But that's the whole point. The non-existent table isn't reachable, and so it doesn't fail.
No. It's not reached, so it doesn't fail. It can be reachable and will work so long as it doesn't actually get there.
create procedure dbo.test_existing(@i int)
as
if (@i<>1)
begin
select c from dbo.non_existing;
end
else
begin
select c from dbo.existing;
end
go
--Third batch
execute dbo.test_existing 1 ;
go
or
create procedure dbo.test_existing
as
declare @i int
set @i=case when RAND() < .5 then 1 else 0 end
if (@i<>1)
begin
select c from dbo.non_existing;
end
else
begin
select c from dbo.existing;
end
go
--Third batch
execute dbo.test_existing ;
go
the procedure will compile OK. When you execute it, it will sometimes give an error, sometimes not.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply