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] ;