Returning a default value when SQL Server doesn't return any rows.

Sometimes you have the need to run a query but if it returns no rows you still want a response.  I've done this with some monitoring queries and even some structural ones.  Here's an example of how you can accomplish it.

Step one create a table or table variable.  In this case I'm just creating a quick table to hold some demo values.

DECLARE @testtable1 TABLE (
testid int identity (1,1),
testvalue int)

Now populate that table variable.  

DECLARE @insertcounter int
SET @insertcounter = 0  

-- inserts 101 rows into the table from 0 - 100

WHILE @insertcounter < 101      
BEGIN         
INSERT INTO @testtable1(testvalue)   
VALUES (@insertcounter)   
SET @insertcounter = @insertcounter +1  

END;  

Now select the table and make sure it populated as expected

SELECT * FROM @testtable1;

This part shows why the simple ISNULL or COALESCE doesn't work in this case- no rows no values.

SELECT

ISNULL(testvalue,0) as testvaluenull,
COALESCE(testvalue,0) as testvaluecoalesce

FROM @testtable1
WHERE testid >101

This is what I have found to work.  Create the subquery where the column would generally go and then when it returns nothing you get the outside value which in this case is zero.

SELECT
ISNULL((SELECT testvalue from @testtable1 where testvalue > 101),0) as testvaluethatworks

Change the default value to get what you need.

SELECT
ISNULL((SELECT testvalue from @testtable1 where testvalue > 101), 9999) as testvaluethatworks

Or if you're ready to take it to the ╰[ ⁰﹏⁰ ]╯level then change the column to NChar and use the hex of your favorite emoji like:

DECLARE @testtable1 TABLE (
testid int identity (1,1),
testvalue  nchar (255))

--use the below for the final query

SELECT
ISNULL((SELECT testvalue from @testtable1 where testvalue > 101),  NCHAR(0xD83D)+ NCHAR(0xDE20) ) as testvaluethatworks

 

This is the non permanent code to this example using a table variable

-- Declare the table variable, this has an identity for ease of tracking

DECLARE @testtable1 TABLE (
testid int identity (1,1),
testvalue int)

--declare the counter variable so you can insert values into the table from a while loop

DECLARE @insertcounter int
SET @insertcounter = 0

-- inserts 101 rows into the table from 0 - 100

WHILE @insertcounter < 101
BEGIN
INSERT INTO @testtable1(testvalue)
VALUES (@insertcounter)
SET @insertcounter = @insertcounter +1
END;

--Selects the entire table variable so you can see what happened SELECT * FROM @testtable1;

--this part shows why the simple isnull or coalesce doesn't work in this case- no rows no values. SELECT

ISNULL(testvalue,0) as testvaluenull,
COALESCE(testvalue,0) as testvaluecoalesce

FROM @testtable1
WHERE testid >101

-- HOW TO MAKE IT HAPPEN -- NESTED SUBQUERY so if the subquery is zero it supplants the value SELECT ISNULL((SELECT testvalue from @testtable1 where testvalue > 101),0) as testvaluethatworks'''