sqlsql-serverqsqlquery

Default row values if returned row is blank

I have a sample query below:

Select column1, column2, column3
from table1
where column1 = 3

and the result is blank

enter image description here

Question: How to update the query that it will return this default result instead of blank rows?

column1 column2 column3
no rows found

I tried a case expression but its still returning a blank row

Solution

  • You have to return a record when your search does not match anything

    You can achieve this result using UNION operator, to add to your result set the default row you desire when main search give no results:

    declare @src varchar(50) = '3'

Select column1, column2, column3
from table1
where column1 = @src

UNION ALL

Select 'no rows found', null column2, null column3
where not exists (select null from table1 where column1 = @src)

    you can write it using cte for readability

    declare @src varchar(50) = '3'

;with
src as (
    Select column1, column2, column3
    from table1
    where column1 = @src
)
    select * from src

    UNION ALL

    Select 'no rows found', null column2, null column3
    where not exists (select null from src)