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?