Catch That Bug! - 20 Apr 2006

March's Bug Solution: The following query returns the maximum OrderID from the Orders table in the Northwind database:

SELECT MAX(OrderID) FROM   dbo.Orders 

And SQL Server's system stored procedure sp_who returns current users and processes. You use the following code to try to run both statements in the same batch:

SELECT MAX(OrderID)FROM   dbo.Orders sp_who 

However, you get only the max OrderID from Orders.You don't receive any output from sp_who—not even an error. It seems that only the first line of code ran and that sp_who wasn't invoked at all. Where's the bug in the code?

The problem is that sp_who—instead of being treated as a stored procedure name that you want to invoke—is actually treated as an alias of the Orders table.You can see the problem more clearly if you write the code in one line, as follows:

SELECT MAX(OrderID) FROM   dbo.Orders sp_who 

It's just like writing the following query and providing the alias O to the Orders table:

SELECT MAX(OrderID) FROM   dbo.Orders O 

To fix the bug, you need to make sure that you use the EXEC stored_procedure syntax, in which it's clear that you're invoking a stored procedure as follows:

SELECT MAX(OrderID) FROM   dbo.Orders EXEC sp_who 

If you invoke the stored procedure first, and the query second, both would run:

sp_who SELECT MAX(OrderID) FROM   dbo.Orders 

The stored procedure is the first statement in the batch, and therefore it's clear to SQL Server that you're invoking a stored procedure.

But it's a bad practice to not specify EXEC in front of the procedure name.You never know whether additional lines of code will be later added in front of the stored procedure invocation.

May's Bug: In SQL Server 2005, you run the following PIVOT query in the Northwind database:

SELECT CustomerID,   [1],[2],[3],[4],[5],[6],[7],[   8],[9] FROM dbo.Orders  PIVOT(COUNT(OrderID)  FOR EmployeeID   IN([1],[2],[3],[4],[5],[6],[7 ],[8],[9])). AS P; 

You expect to get a single row for each customer, along with the number of orders placed by each employee for that customer. Because there are 89 customers in the Orders table that have orders,you expect to get 89 rows in the result. However,you don't get the expected result. Rather, you end up getting 830 rows. Can you identify the bug in the code and suggest a solution?