Today we are going to have very simple and interesting question.
Run following T-SQL Code in SSMS. There are total of five lines. Three T-SQL statements separated by two horizontal lines.
SELECT MAX(OBJECT_ID) FROM sys.objects
______________________________________
SELECT MIN(OBJECT_ID) FROM sys.objects
______________________________________
SELECT COUNT(OBJECT_ID) FROM sys.objects
Now when you execute individual lines only it will give you error as
Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure '______________________________________'.
However, when you executed all the five statement together it will give you following resultset.
What is the reason of the same?
Please leave your comment as answer. I will discuss the answer of this question on tomorrow’s office hours.
One random correct answer will win my SQL Wait Stats [Amazon] | [Flipkart] | [Kindle] copy – anywhere in the world.
Reference: Pinal Dave (https://blog.sqlauthority.com)
82 Comments. Leave new
I am getting output for individual query
T-SQL Optical illusion……. :)
______________________________________ is considered table alias……
Good one……
when i executed all the five statement together it works ok
and in the result panel it seprates ,the answer
and individuly it goes to find table or stored procedure with same name
so it gives u an error……
naren
I believe that the ‘___________ ….’ or any other (as long as it’s not a reserved keyword) is treated as a table alias and hence the behaviour.
this is treated as alias name that is why erro not coming.
“______________________________________” is being used as table alias
when you executed all the five statement together ___________ is treated as a table alias. you can check it by using as like
from sys.objects as ___________
Mitesh Modi
Hello Friends,
When it is executed as whole it creates the alias so it will not give any error.
If line is executed independently it looks into the system database as well as in the current selected database for the SP name like that because SP is the the only object in the sqlserver which can be executed independently so, sql considers it as an SP and it will not find the SP for the same name and gives an error.
If you create the SP with the name as ______________________________________ it will return correct result.
Thanks,
Ramesh
you can also use this query also like that (alias tablename.columnname)
SELECT MAX(______________________________________.OBJECT_ID) FROM sys.objects
______________________________________
It takes the ____ as the Alias Name
SQL server is unable to recognize the line ______________________________________, if this line is before the SQL Statement SQL Server is treating this line as a Stored Procedure and searching for this object but its does not exists, that is the reason it is throwing the error, it is also treating the line and select query are different parts. when we are executing all the statements at a time, the line is treating as an alias of the Table. That is the reason it is not throwing any error.
Hi Pinal!
Well, I think ___ is just an alias, and to be sure I tested it with the following query:
select Firstname, Lastname, LocationID __ from Employee
(the table Employee and fields I’m using come from the book “Beginning SQL Joes 2 Pros”)
That is the same as:
select Firstname, Lastname, LocationID AS __ from Employee
So, that line is acting as an alias for the table sys.objects
Rene Alberto Castro Velasquez
El Salvador
The line is the alias for the table name.
Are the underscores the name given to the alias?
If not, then I’m not sure, thought i’d give it a shot though! :P
Hi,
When I am running below statement in SQL Server 2008 R2 it’s working fine.
SELECT MAX(OBJECT_ID) FROM sys.objects
______________________________________
SELECT MIN(OBJECT_ID) FROM sys.objects
______________________________________
SELECT COUNT(OBJECT_ID) FROM sys.objects
______________________________________
Output are :
(No column name)
1195151303
(No column name)
3
(No column name)
74
Regards
Dharmendra
When Executing the
SELECT MAX(OBJECT_ID) FROM sys.objects
______________________________________
The compiler treats it as a single statement and hence takes line as a table alias
The underscore “_” is a valid character in the name of a Store Procedure so the entire underscore line is considered to be the name of a Stored Proc. Open a new query window and just type _ and run it. You get the error.
Simple really
I have no clear idea why this is happening but i am 995 sure that –To specify that a command continues on the next line, we use a hyphen (continuation character) after all the text on the line to be continued.
So in this case when we are executing all together, its working fine because its treating all the lines it continuation but executing single line giving error because its not getting the next executable statement — is correct posted by sharing360
Hi Pinal,
Regarding the question below is the answer
Anything in the batch first line in query window it will treat as a stored procedure
1.)
For example:
in query window just run ABCDEFGH it returns the same
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure ‘ABCDEFGH’.
2.)
And also we can create a stored procedure with line(nothing but underscore)
create procedure _____
as
begin
select getdate()
end
3.) It will execute in batch because first line is not there and 2nd it will omit
4.) We can execute stored procedure with just name and f5
it will exeute and no key words required.
after little bit R & D, it table alias only……