Recently I was asked to write a T-SQL code to count the rows of any table, given its name. My first attempt was to embed an EXECUTE(…) statement in the body of a stored procedure, and get the table name as an input parameter:

CREATE PROC dbo.RecordCount(@tablename nvarchar(128))

AS

EXECUTE('SELECT COUNT(*) FROM '+@tablename)

The solution was straightforward, but what if you wanted the record count as an output parameter? You cannot use EXECUTE since it doesn’t support parameters. In order to both execute a dynamic statement and support parameters, you have to use the system stored procedure sys.sp_executesql, which has a similar behavior to EXECUTE.

After reading the MSDN documentation I wrote the following code:

CREATE PROC dbo.RecordCount   
  @TableName nvarchar(128),
  @Count int OUTPUT
AS 
BEGIN
  DECLARE @SQLString nvarchar(500); 
  DECLARE @ParamDefinition nvarchar(500); 
  
  SET @SQLString = 'SELECT @Count = Count(*) FROM '+@TableName 
  SET @ParamDefinition = '@Count int OUTPUT' 
  
EXECUTE sys.sp_executesql @SQLString, @ParamDefinition, @Count output 
END

As you can see, the dbo.RecordCount stored procedure declares an input parameter called @TableName and an output parameter called @Count; the interesting part comes with the body of the stored procedure.  At first I build the dynamic SQL statement and store it in the @SQLString variable. Then I build a second SQL statement, declaring the parameter used inside @SQLString; I store it in the @ParamDefinition variable. Finally I invoke EXECUTE sys.sp_executesql passing it three parameters: my two string variables (the dynamic statement and the definition of @Count OUTPUT) and my actual procedure OUTPUT parameter, which was described inside @ParamDefinition in order to be recognized by sp_executesql.

Using this function is easy as declaring a variable and reading an output parameter. For instance, you can define it inside AdventureWorks2008R2 database and test it with the Person.Person table:

DECLARE @c int
EXEC RecordCount 'Person.Person',@c OUT
PRINT @c

What’s worth remembering is that one of the advantages of sp_executesql compared to EXECUTE, is that SQL Server is able to cache the execution plan of the dynamic query so that subsequent invocations of the procedure are likely not recompiled (you can read more on MSDN.)

Happy programming!

Recently I meant to demo how to use two T-SQL logical operators: ANY (a.k.a. SOME) and ALL. Tipically these two operators are used in the right-side of a comparison expression, combined with a sub-query. For instance, let’s consider a sample query against AdventureWorksLT database:

SELECT * FROM SalesLT.Product WHERE ListPrice < [ALL|ANY] (
SELECT UnitPrice FROM SalesLT.SalesOrderDetails WHERE ProductID = 123 )

As Microsoft TechNet says, ANY…

…returns TRUE when the comparison specified is TRUE for any pair (scalar_expression, x) where x is a value in the single-column set; otherwise, returns FALSE.

while ALL…

…returns TRUE when the comparison specified is TRUE for all pairs (scalar_expression, x), when x is a value in the single-column set; otherwise returns FALSE.

Referring to the the previous code, you can use “ALL” to select all the products that cost less than every unit price stored in the SalesOrderDetails table. On the other hand, you can use “ANY” to select all the products having a list price inferior than any unit price.

What You See is *not* What You Get

After this simple sample, I decided to entangle things a little bit, and I wrote the following code:

SELECT p.ProductId, p.ListPrice
FROM SalesLT.Product p
WHERE p.ListPrice > ANY (
  SELECT d.UnitPrice
  FROM SalesLT.SalesOrderDetail d
  WHERE d.ProductID = p.ProductID
)

The inner query is now a correlated sub-query and it gives different result-sets for every record evaluated by the outer query. With this composed query I meant to select all the products with a list price greater than – at least – any related unit price. Note that in this case, the sub-query returns only the list of unit prices related to every single product; for instance, when evaluating the outer query for the ProductID 780, the sub-query is equivalent to the following one:

SELECT d.UnitPrice 
FROM SalesLT.SalesOrderDetail d 
WHERE d.ProductID = 780

When I executed the query I found 142 records [⁰]. Then I executed a similar query, using “ALL” in place of “ANY”. I expected much less records. The result was astonishing: 295 records [⁰]!!! How could be possible that the number of products that cost more than EVERY sold product is greater than the number of products that cost more than ANY sold product?!

This couldn’t be possible…

Yet another devil hidden in the details…

At first I thought I found a bug in ANY and ALL implementations …

Of course, I was wrong

The point is that not every product listed in the Product table has been sold. For such products, the correlated sub-query returns an empty result-set.

So, how does SQL Server evaluates ANY/ALL when doing comparisons against an empty set? It turned out that SQL Server handles such cases in a simplistic way [¹]:

• when evaluating an “ALL-ized” comparison operator, it returns TRUE if it doesn’t find any case for which the comparison is FALSE
• when evaluating an “ANY-ized” comparison operator, it returns FALSE if it doesn’t find any case for which the comparison is TRUE

As a consequence, the ALL operator evaluates TRUE against empty sets and the ANY operator evaluates FALSE against empty sets. Good to know!

You can try this yourself:

-- Selecting all products:
SELECT * FROM SalesLT.Product 
WHERE ListPrice > ALL ( 
  SELECT ‘who cares?’ WHERE 1=0 
) 

-- Selecting no product:
SELECT * FROM SalesLT.Product 
WHERE ListPrice > ANY ( 
  SELECT ‘who cares?’ WHERE 1=0 
)

Since my Product table contains 153 records which are not referenced by any record in SalesOrderDetail, all these unreferenced products where selected in the “ALL-ized” version of the query and where discarded in the “ANY-ized” version (142+153=295, math cannot lie.)

By the way, this verbose query works as expected:

SELECT p.ProductId, p.ListPrice FROM SalesLT.Product p 
WHERE p.ListPrice > ANY ( 
SELECT d.UnitPrice FROM SalesLT.SalesOrderDetail d 
WHERE d.ProductID = p.ProductID )
AND EXISTS (
SELECT * FROM SalesLT.SalesOrderDetail d
WHERE d.ProductID = p.ProductID )

Happy programming!

[⁰] Numbers may vary in your database (I can’t remember if I used a clean version of AW)

[¹] This is how every sane programmer would solve such a problem: check for a counterexample and exit the loop as soon as you find one.