SQL Return default values if no rows returned

Question

I've got a SQL script which returns a result set, the requirement is to return a default result set with default values if the below script does not yield any results. It should have the same column names as the below script

 -- Return final results
    SELECT  
        p.worked                                                                           [AccountsWorked],
        p.rcmade                                                                       [RPC's],
        p.obtained                                                                             [PTPCount],
        p.amount                                                                          [PTPValue], 
                                                                                                [PreviousDayPTPValue]

    FROM
        @tab_performance p JOIN
        dbo.user usr ON
            (p.usr_code = usr.usr_code) JOIN
        dbo.team tme ON 
            (tme.tme_id = usr.tme_id)   
            AND p.usr_code = @usr_code

I need to return a default result set if no rows are returned. So all the columns should be returned with NULL's or any default value.

I have tried conditional select statements without any luck, I have also tried the @@ROWCOUNT

rohitvats · Accepted Answer · 2020-02-07 06:44:06Z

3

You can add a union all select to your existing query with default values like this:

<your existing query>

union all

select null accounts_worked, null right_contacts_made, null ppts_obtained .....
where not exists(
select *
from @tab_performance p JOIN
        dbo.TR_USR_User usr ON
            (p.usr_code = usr.usr_code) JOIN
        dbo.TR_TME_Team tme ON 
            (tme.tme_id = usr.tme_id)   
            AND p.usr_code = @usr_code
)

The where clause could be further simplified, if your inner joins don't filter out any rows from @tab_performance:

<your existing query>

union all 

select null accounts_worked, null right_contacts_made, null ppts_obtained .....
where not exists(
select *
from @tab_performance
where usr_code = @usr_code
)

edited Feb 7, 2020 at 6:44

answered Feb 7, 2020 at 6:29

rohitvats

1,85113 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Robin · Accepted Answer · 2020-02-07 06:39:02Z

2

I would do it with WITH and UNION ALL

Drop  table if exists #test
create table #test (
    Column1 int null,
    Column2 varchar(50) null
);

--INSERT INTO [#test] (Column1,Column2)
--VALUES        
--        (1, 'test'),
--        (2,'test2'),
--        (3,'test3');

WITH qry AS (
   select Column1, Column2 from #test
)
select * from qry
UNION ALL
select NULL as Colum1, null as Column2 where (select COUNT(*) from qry) = 0

answered Feb 7, 2020 at 6:39

Robin

1543 bronze badges

Collectives™ on Stack Overflow

SQL Return default values if no rows returned

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related