Returning a Default Row When No Data Would Be Returned

Recently, I had to tune a stored procedure that had an odd behavior. If no data was found matching the parameters passed in, it still returned a row with empty string values. The way the sproc was doing this was to first check for the existence of data for the parameters passed in. If data was there it issues the SELECT statement, otherwise a SELECT of empty string values.

IF NOT EXISTS (SELECT * FROM dbo.TestData WHERE Col1 = @Col1 AND Col2 = @Col2)

       SELECT
               '' AS Col1
              ,'' AS Col2

              ,'' AS Col3

              ,'' AS Col4
ELSE
       SELECT
               Col1
              ,Col2
              ,Col3
              ,Col4
       FROM
              dbo.TestData
       WHERE
              Col1 = @Col1
       AND Col2 = @Col2

Naturally, this sort of logic should not be in the database tier. But the contract with the application was established, and the application call to the database was not sufficiently encapsulated – and so I had to work with this and change only the implementation of the sproc while preserving this contract.

So the obvious problem with the solution above is that when there is a match, there are two requests for the data as the execution plan below shows.

So the challenge was to preserve the default row contract with the application while eliminating the extra requests to check for the existence of the data. I will present three solutions, the third being the best I know of. But naturally, let me know if you have a better solution.

 (1)  UNION ALL the default row.

SELECT

        Col1
       ,Col2
       ,Col3
       ,Col4
FROM
       dbo.TestData
WHERE
       Col1 = @Col1
AND Col2 = @Col2
UNION ALL
SELECT
        '' AS Col1
       ,'' AS Col2
       ,'' AS Col3
       ,'' AS Col4

The problem here is that the default row is returned even when the data exists.

So for this solution, additional logic is needed to return either the records matching the parameters or the default row, but not both – something like this.

 WITH DataCTE

AS
(
SELECT
        Col1
       ,Col2
       ,Col3
       ,Col4
       ,0 AS DefaultRowInd
FROM
       dbo.TestData
WHERE
       Col1 = @Col1
AND Col2 = @Col2
UNION ALL
SELECT
        '' AS Col1
       ,'' AS Col2
       ,'' AS Col3
       ,'' AS Col4
       ,1 AS DefaultRowInd
)

SELECT

        Col1
       ,Col2
       ,Col3
       ,Col4
FROM
       DataCTE
WHERE
       DefaultRowInd = (SELECT MIN(DefaultRowInd) FROM DataCTE)

But the CTE does not shield us from two requests for the data. The WHERE clause at the end referencing DataCTE ultimately is a separate request for the data against the base table, and so - in this regard - no different from the original sproc implementation.

(2)  Table variable solution. This solution reads the data once into a table variable and then checks there for the existence of the data.

DECLARE @Data TABLE (Col1 VARCHAR(50),Col2 VARCHAR(50),Col3 VARCHAR(50),Col4 VARCHAR(50))

INSERT
INTO
       @Data
(
        Col1
       ,Col2
       ,Col3
       ,Col4
)
SELECT
        Col1
       ,Col2
       ,Col3
       ,Col4
FROM
       dbo.TestData
WHERE
       Col1 = @Col1
AND Col2 = @Col2

IF (SELECT COUNT(*) FROM @Data) = 0

       SELECT
               '' AS Col1
              ,'' AS Col2
              ,'' AS Col3
              ,'' AS Col4
ELSE
       SELECT
               Col1
              ,Col2
              ,Col3
              ,Col4
       FROM
              @Data

Although in a heavy-handed way this avoids a separate existence-check hit to the base table, the sproc still has to do the same sort of processing against the table variable and actually it is more expensive. The execution plan comparison is roughly 25% (original) vs. 75% (table variable solution).

(3)  SELECT NULL and LEFT OUTER JOIN. This is the best solution I was able to work out.

SELECT

        ISNULL(Col1,'') AS Col1
       ,ISNULL(Col2,'') AS Col2
       ,ISNULL(Col3,'') AS Col3
       ,ISNULL(Col4,'') AS Col4
FROM
       (SELECT NULL AS DefaultVal) DefaultRow
       LEFT OUTER JOIN dbo.TestData
              ON  Col1 = @Col1
              AND Col2 = @Col2

This solution guarantees a record is returned with the SELECT NULL and will display data from the base table only if it exists – otherwise the ISNULL functions returns empty strings. NOTE: In order for this to work, it is important to move the WHERE clause criteria to the LEFT OUTER JOIN criteria – so it is applied only to the base table, and not to the entire query. If it is not moved from the WHERE clause, the T-SQL will not return the default row.

Below are the result sets for both conditions confirming the required results.

Comparing the execution plans, we see the tuned version has a execution plan cost equal to half of the original sproc – which makes sense because we have eliminated one database hit without introducing costly processing.

Hope this is of some benefit.

Later

 -Regan