CODEWITHSUNDEEP
sqlsql-servert-sql
GeoVIP
GeoVIP

Reputation: 1564

How to bring some value when result is empty

I have a stored procedure like this:

declare @myVal int

select 
    t2.short_descr , count(*) as count 
FROM 
    t1 
INNER JOIN 
    t2 ON t1.id = t2.id                                                                         
where 
    [date] BETWEEN DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) and GETDATE())
group by 
    t2.short_descr

It works good, now I want the procedure to return a result even if it is empty; in that case, I want to return 

select @myVal

I tried like this :

  declare @myVal int

  select 
      t2.short_descr , count(*) as count 
  FROM 
      t1 
  INNER JOIN 
      t2 ON t1.id = t2.id
  where 
      [date] BETWEEN DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) and GETDATE())
  group by 
      t2.short_descr 

  IF @@ROWCOUNT = 0 
     select @myVal

but when result is empty stored procedure selects 2 results: empty and @myVal. I want if is empty select just @myVal . How do it ?

Upvotes: 0

Views: 56

Answers (1)

gotqn
gotqn

Reputation: 43666

You can add EXISTS check like this:

IF EXISTS ( SELECT 1 FROM t1 INNER JOIN t2 ON t1.id = t2.ID
           where [date] BETWEEN DATEADD(day,DATEDIFF(day,0,GETDATE()),0) and GETDATE()) )
BEGIN
    select t2.short_descr , count(*) as count 
    FROM t1 
    INNER JOIN t2 ON t1.id = t2.id                                                                         
    where [date] BETWEEN DATEADD(day,DATEDIFF(day,0,GETDATE()),0) and GETDATE())
    group by t2.short_descr
END
ELSE
BEGIN
     select @myVal
END

Upvotes: 2

Related Questions