Object binding

Question

Object binding

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 1

Toreador (8/15/2013)
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

Toreador, you have indeed identified a mistake I made. I deleted the rest of this post because I was just compounding that error.

Jason Wolfkill

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 2

Stewart "Arturius" Campbell (8/15/2013)
Good back-to-stored proc basics question.

+1 🙂

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

kapil_kk SSC-Insane Points: 21316 More actions · Answer 3

Very good question....

There is a lil confusion to me....

When the second batch gets executed the Stored procedure created successfuly, as according to deferred name resolution process when an entry is made in a sys.sql_modules in which an object is used in a SP that is not existed, SP will get create successfully...

Now, while 3 batch gets executed, it does not the throw error and gets executed successfully as condition (1<>1) never gets executed. So, Does at the time of compilation only valid condition gets compiled and rest remained untouched?

While executing 4 batch, I read somewhere that after getting an entry into a sys.sql_module if we create an non existing object in a stored procedure it will give you an error as now it will check that all objects using in a SP exists or not....

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Nick Doyle SSC Eights! Points: 926 More actions · Answer 4

Good question, but do agree the explanation could be improved.

james.of.rivendell SSC-Addicted Points: 425 More actions · Answer 5

Great question. 2) and 4) should give a warning about the nonexistent table, but will run nevertheless.

I don't understand why 5% of the answers don't include the first option. I mean why would the create table statement not succeed :blink:?

Abrar Ahmad_ SSCarpal Tunnel Points: 4239 More actions · Answer 6

Abrar Ahmad_

SSCarpal Tunnel

Points: 4239

August 20, 2013 at 5:18 am

#1643015

Tough time!!!!! 😉

sqlnaive SSCoach Points: 17437 More actions · Answer 7

Easy question for me. Experience paid for me. 🙂