Select dummy values if where clause returns zero rows

I am using SQL Server 2016. I have a SQL and it has where clause which returns zero or non-zero rows depending on data present in the table at a specific time. For example the SQL is:

select step_id,
       step_name
  from msdb.dbo.sysjobhistory
 where step_id = 9999

What I want is when the SQL returns zero rows, then it should return one row with dummy values that I can specify.

What I tried: I can do this by using this SQL:

DECLARE @Condition int 
SET @Condition = 0;
IF (
    SELECT count(*) FROM
    (
     select step_id,
            step_name
       from msdb.dbo.sysjobhistory
       where step_id = 9999
    ) t1
   ) > 0
BEGIN
    SET @Condition = 1;
END;

IF @Condition = 1
BEGIN
select step_id,
       step_name
  from msdb.dbo.sysjobhistory
 where step_id = 9999
END
ELSE
BEGIN
    SELECT 123456 AS [step_id], 'There are no step_id matching the WHERE clause' AS [step_name]
END

Question: But here I am running the same SQL twice. I want to know if there is any better way to do this.

Answer

The problem is, that if the WHERE clause eliminates all rows, you end up with an empty set.

One way around it, is to use an aggregate function to force one row in the result.

Then, with COALESCE (or ISNULL), you can assign your default values.

This solution is only feasible if:

• your basic query always returns exactly 0 or 1 row
• your columns do not contain NULL
• the selected columns can be aggregated (e.g. it will not work for text columns)
• your default values have the same data type as the columns

For example:

select COALESCE(MAX(step_id),123456) AS step_id,
       COALESCE(MAX(step_name),'There are no step_id matching the WHERE clause') AS step_name
  from msdb.dbo.sysjobhistory
 where step_id = 9999