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
In my installation (Denali CTP3) it has been executed fine,
and even the following one yield no errors:
SELECT MAX(OBJECT_ID) FROM sys.objects As ______________________________________
SELECT MIN(OBJECT_ID) FROM sys.objects As ______________________________________
SELECT COUNT(OBJECT_ID) FROM sys.objects
Hi
here ______________________________________ is treated as table alias so that you can use a query like this also.
select * FROM sys.objects
______________________________________
where ______________________________________.name like ‘%test%’
No clue …
Surprised to see the question, which is not at all question but just a discovery of free mind :)
One side you saying “Now when you execute individual lines only it will give you error as” and the same time you selecting 2 line, one with query and one with ‘——————‘. Why you selecting it? If I replace the line with some other character, it will still report the same.
Underscore is a Result Separator.
if you select Query —-> Results —–> Result to Text
menu you will get this ans as below.
———–
1419152101
(1 row(s) affected)
———–
4
(1 row(s) affected)
———–
77
(1 row(s) affected)
so if there is any query underscore is ignored.
But without any query you will get the error.
Nirav in your result (result in Text) underscore (——) denotes the column name space. place column alias name in any query and then you find the exact result. i.e.
SELECT MAX(OBJECT_ID) As [Max_ID] FROM sys.objects
Hi Dave,
If I execute following queries, they will return same results.
SELECT MAX(______________________________________.OBJECT_ID) FROM sys.objects
______________________________________
SELECT MIN(______________________________________.OBJECT_ID) FROM sys.objects
______________________________________
SELECT COUNT(___.OBJECT_ID) FROM sys.objects
___
So, they demonstrate string ‘______________________________________’ is table alias.
Regards,
Kaiyuan Liang
I have SQL SERVER 2008R2 Developer Edition.
when I run those statement , then i did not get any error.
Its execute perfect.
And if I run all this together, then Its run fine as well.
Any entry, which is not an SQL Command is treated as an SP name and SQL server trying to execute the same. If you enter “ssssssss” and try to execute, it will throw the same error Could not find stored procedure ‘ssssssss’.
As “______________________________________” is not a valid SQL Command, SQL server try to identify a stored procedure with the same name. That is why it throws the error message.
When we execute the entire commands together, then SQL Server will consider “______________________________________” as an alias for the previous SQL Command.
If you add one more line like
SELECT MAX(OBJECT_ID) FROM sys.objects
______________________________________
______________________________________
SELECT MIN(OBJECT_ID) FROM sys.objects
Then it fails with incorrect syntax in the second line.
if you write underscore in your select statment you will get error.
try this & get the idea.
SELECT MAX(OBJECT_ID) FROM ____ sys.objects.
I have sql server 2008r2 and I am able to run this even individually as well as all together.
It seems error occurred when sql try to execute
______________________________________
then
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure ‘______________________________________’.
Hi Pinal,
Nice Question.
When we execute this line ______________________________________, we get the error,
because when you write any Word or simple any symbol like “______” in SQL Prompt then sql considers it as stored procedure.
That means for ex. if you write “employee” word in prompt then it searches for employee stored procedure in current database.Because of this when you write ______________________________________ line then server considers it as stored procedure name & we gets the error as
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure ‘______________________________________’.
If we write SQL statement first followed by ______________________________________ then it takes this as a table alias name and executes this without any error.
But if we write the ______________________________________ followed by sql statement then it will give an error. It gives an error because it consider it as a procedure and tries to execute the same. Generally to execute a procedure we simply write the procedure name and press F5. Thus in the above case it tries to execute two separate statements and therefore it gives an error.
It’s a simple table alias. The following statement will execute fine as well…
SELECT MAX(OBJECT_ID) FROM sys.objects
hahaha_this_is_a_table_alias_hahaha
Exec is automatically gets call when you write
______________________________________ this statement or even single word also like abc or xyz
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
______________________________________
But when I run
SELECT MAX(OBJECT_ID) FROM sys.objects
______________________________________
SELECT MIN(OBJECT_ID) FROM sys.objects
______________________________________
SELECT COUNT(OBJECT_ID) FROM sys.objects
______________________________________
______________________________________
It will throw an error
Incorrect syntax near ‘______________________________________’.
because you can create only one alias for a table but here in this case we are trying to give more than one alias.
Check the below statement :
This statement will work fine.
SELECT COUNT(OBJECT_ID) FROM sys.objects TableAlias
This statement will give the same error.
SELECT COUNT(OBJECT_ID) FROM sys.objects TableAlias TableAlias
Thanks,
Geetanjali Agarwal
This one is quite simple, actually. The series of underscores are the table alias. So, if I run the following, it works:
SELECT MAX(______________________________________.OBJECT_ID) FROM sys.objects
______________________________________
Table alias name
Hi all,
When we execute all together, the “______________________________________” is used as table alias.
Demostration: following run without error
SELECT MAX(hi.OBJECT_ID) FROM sys.objects
hi
SELECT MIN(______________________________________.OBJECT_ID) FROM sys.objects
______________________________________
Really interesting :-) Thanks for make us thinking.
Guiomar
SELECT MAX(OBJECT_ID) FROM sys.objects—2137058649
SELECT MIN(OBJECT_ID) FROM sys.objects—3
SELECT COUNT(OBJECT_ID) FROM sys.objects—166