THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Yet another use of OUTER APPLY in defensive programming

When a SELECT is used to populate variables from a subquery, it fails to change them if the subquery returns nothing - and that can lead to subtle bugs. We shall use OUTER APPLY to eliminate this problem.

Prerequisites

All we need is the following mock function that imitates a subquery:

CREATE FUNCTION dbo.BoxById ( @BoxId INT )
RETURNS TABLE
AS RETURN
  
( SELECT  CAST(1 AS INT) AS [Length] ,
            
CAST(2 AS INT) AS [Width] ,
            
CAST(3 AS INT) AS [Height]
    
WHERE   @BoxId = 1
  
) ;

Let us assume that this subquery is slow and we do not want to run it more than absolutely necessary.

Short recap on SET vs. SELECT

The following approach uses SET to populate variables:

DECLARE @Length INT ,
  
@Width INT ,
  
@BoxId INT ;

SELECT  @Length = 0 ,
        
@Width = 0  ;

SET @BoxId = 1 ;

SET @Length = ( SELECT  [Length]
                
FROM    dbo.BoxById(@BoxId)
              ) ;
SET @Width = ( SELECT [Width]
              
FROM   dbo.BoxById(@BoxId)
             ) ;

This script will always change the values of variables, even when the subquery returns nothing, which is good. However, it runs a possibly slow/expensive subquery twice. Also it duplicates code. We want to avoid both these disadvantages.

The following script uses SELECT, it has no duplication, and it runs the subquery only once:

DECLARE @Length INT ,
  
@Width INT ,
  
@BoxId INT ;
  
SELECT  @Length = 0 ,
        
@Width = 0  ;

SET @BoxId = 1 ;

SELECT  @Length = [Length] ,
        
@Width = [Width]
FROM    dbo.BoxById(@BoxId) ;

SELECT  @Length AS [Length] ,
        
@Width AS [Width] ;
 

This code looks cleaner and performs better, but it has the following problem: if the subquery returns nothing, the variables are not changed. You can change @BoxId from 1 to any other number, rerun the script, and see for yourself.

When the variables are not changed by an assignment, that can lead to subtle bugs, 

like this one.

To avoid this vulnerability, we can use OUTER APPLY, as follows:

DECLARE @Length INT ,
  
@Width INT ,
  
@BoxId INT ;
  
SELECT  @Length = 0 ,
        
@Width = 0  ;

SET @BoxId = 2 ;

SELECT  @Length = [Length] ,
        
@Width = [Width]
FROM    (SELECT 1 AS PlaceHolder) AS AlwaysOneRow
OUTER APPLY dbo.BoxById(@BoxId) ;

SELECT  @Length AS [Length] ,
        
@Width AS [Width] ;

As a result, we still have no duplication, run an expensive subquery only once, and also always change the variables, making the code less vulnerable to bugs.
Published Wednesday, June 06, 2012 5:58 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam Machanic said:

Hi Alex,

In case you're interested, here's a related blog post on this topic -- make sure to look at the comments as well:

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/t-sql-variables-multiple-value-assignment.aspx

June 6, 2012 7:42 PM
 

AlexK said:

Hi Adam,

I recall reading your post a while ago, and I liked it. But that is a different, although related, scenario. In fact, in all those infrequent cases I care about possible ambiguities in multiple assignments, I can assume that currently there are no ambiguities, and document the assumption as a unit test.

For example, I can use a unit test to query system views and make sure that BoxId=@BoxId returns at most one row. If the database structure has changed, a broken unit test will indicate it. This check will occur at build time. As such, it will not slow down my runtime. What do you think?

June 6, 2012 10:10 PM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement