May 16, 2011 at 5:40 am
Hello All
I have a database named 'MyDatabase' in Sqlserver 2008R2.
In 'MyDatabase' i have one table named 'tbl_test'.
I write this script to create a stored procedure :
"
CREATE PROCEDURE sp_Test
AS
SELECT * FROM tbl_tests
"
tbl_tests does not exist in 'MyDatabase' but when i execute this script I dont see any error
.
why this happen?
May 16, 2011 at 5:43 am
Probably your post got truncated, but I see no question here.
Can you clarify please?
-- Gianluca Sartori
May 16, 2011 at 5:57 am
May 16, 2011 at 5:59 am
OK, it's much clearer now.
Dependency checks are not performed when you create a stored procedure.
See BOL here:
Deferred Name Resolution and Compilation
-- Gianluca Sartori
May 16, 2011 at 7:53 pm
By which Gianluca is saying that the script you executed completes successfully because it's not RUNNING the code in the stored procedure, it just created a procedure (with bad code in it) successfully.
If you actually EXECUTE your stored procedure, then you'll get the error.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
May 16, 2011 at 8:41 pm
When you create any procedure,trigger or function or even a view , it only parses that object . Parsing is the first phase which only checks the syntax of the code.It just ensure that all the sql keywords and other releated sql statements are syntatically correct.
But other things like normalisation,compilation and optimisation happens when you actually execute that object.
During normalisation phase, the underlying objects (tables, views or any other procedure) which you might be reffering in your procedure\function will be checked and in case it does not exist on the mentioned database \schema you will get that error.
I have just tried to summarise that it is important for you to also understand the different phases which the database engines uses.
Regards,
Sachin
May 16, 2011 at 10:38 pm
But other things like normalisation,compilation and optimisation happens when you actually execute that object.
Query engine does normalization? Could you please post the link which explains the same. That would be great of you...
May 16, 2011 at 10:47 pm
Pls. read out the attached PDF file. When i say normalisation in the context of query execution phases it is different from the normal normalistion which you must have learnt in the context of database design like Normalisation Forms.
Pls. let me know if it clears out your doubt..
Sachin
May 16, 2011 at 11:02 pm
Thanks for sharing the document.
Again, it says the "Translation from parse tree to internal query representation requires some Normalization". Any idea what are the steps taken in those calculus representaion : "Normalization".
Am trying to google out the same.
May 16, 2011 at 11:07 pm
check out this link also.
May 17, 2011 at 7:01 am
That first pdf is specifically about xQuery, and the second link is about Sybase?
In any case, the parsing/normalization is not the issue here, the CREATE statement successfully completes, because it is not executing the script within the stored procedure.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply