Issue (seemingly) with the SET PARSEONLY option
This was actually a question raised in a discussion forum. Thought I will write about it anyways. In a database, lets say we don't have a table called orders.
Then the following script works fine, since syntactically its right and the select statement is just parsed.
set parseonly on
select * from orders
But the following doesn't and throws an error saying "Invalid object name 'orders'"
set parseonly on
select * from orders
set parseonly off
Might seem intriguing at first glance. Well it isn't.
Well, thats because the option "set parseonly on" is set at parse time and not at run time and parsing obviously happens before the execution :)
So what actually happens is the option is set to on and then the statement is parsed and then it doesn't throw the parsing error and then the option is set to off. Now SQL Server has completed parsing the script. Now it will check for the PARSEONLY option whether to execute it or not. and since the option is off it executes the query and throw an error.
Well, just to prove my point, try and execute this
set parseonly on
select top 1 * from sysobjects
set parseonly off
You will actually get the result.
And then I know there will be some smart guy out there who will try to bring in a paradox here.
I would rather bring it myself ;)
Lets take this script
set parseonly on
set parseonly off
Now, shouldn't the second statement be parsed and not executed since parseonly is on. Well that will just mean that "parseonly" option once its set ON can never be switched off :)
Well as I said in the beginning "parseonly" is set at parse time and for clarity sake take it this way that the "set parseonly" statements will be removed during the execution time.
0 comments:
Post a Comment