Returning A Row When Your Query Has No Results

Canary

---

Empty results are often dissatisfying. Especially when stored procedures return multiple result sets, it can be easy to get lost in which result is empty.

Of course, there’s many a good argument to be made against actual production stored procedures returning multiple results — usually these should be multiple procedures — I see it happen quite a lot.

Rather selfishly, I do this for my stored procedures, for all the reasons in the first sentence. Especially when debugging stored procedures, you’ll want to know where things potentially went wrong.

In this post, I’m going to walk through a couple different ways that I use to do this. One when you’re storing intermediate results in a temporary object, and one when you’re just using a single query.

Intermediate

---

This is fairly obvious and straightforward. You do the insert, check the row count, and return a message if it’s zero:

DECLARE
    @t table
(
    database_id integer,
    database_name sysname
);

INSERT
    @t
(
    database_id,
    database_name
)
SELECT
    d.database_id,
    d.name
FROM sys.databases AS d
WHERE d.database_id > 32767;

IF @@ROWCOUNT > 0
BEGIN
    SELECT
        t.*
    FROM @t AS t;
END;
ELSE
BEGIN
    SELECT
        msg = 'table @t is empty!'
END;

Nothing new under the sun in this one at all.

All In One

---

Let’s say you don’t want or need a temporary object. Your query is good enough, smart enough, and gosh darn it etc.

This is a particularly tricky one, because there’s no way to check the row count from within the query. In this case, you can use a common table expression in a rather handy way.

WITH
    d AS
(
    SELECT
        d.database_id,
        d.name
    FROM sys.databases AS d
    WHERE d.database_id > 32766
)
SELECT
    d.*
FROM d

UNION ALL

SELECT
    0,
    'table @t is empty!'
WHERE NOT EXISTS
(
    SELECT
        1/0
    FROM d AS d2
);

Of course, the usual caveats about common table expressions bear repeating here: The query within the common table expression will run twice:

1. Once when we select from it outside the CTE
2. Once when we check for the existence of rows in the CTE

I don’t recommend this approach for long running queries within common table expressions, since this is essentially double your displeasure, double your misery.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.