SQL - No return value, but I need one 1

[zemp]

SQL Server 2000 SP3

I have the following T-SQL that does not return anything. Which is correct because no records match the criteria (a possible case that must be handled). However I need to be able to return a zero (0) in this case so that the return value can be used in a math function as part of a larger stored procedure. I am at a loss. Any suggestions?

SELECT  SUM(CDHours) as hours
FROM         TCDetail
WHERE     (CDDate <= '07/29/2005') 
GROUP BY ECode
HAVING      (ECode = 'STA')

zemp

 

[gmmastros]

will this work?

If Exists(Select 1 From TCDetail Where ECode='STA'
  SELECT  SUM(CDHours) as hours
  FROM         TCDetail
  WHERE     (CDDate <= '07/29/2005') 
  GROUP BY ECode
  HAVING      (ECode = 'STA')
Else
  Select 0 As Hours

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Ooops, obvious errors.

If Exists(Select 1 From TCDetail Where ECode='STA' And CDDate <= '07/29/2005')
  SELECT  SUM(CDHours) as hours
  FROM         TCDetail
  WHERE     (CDDate <= '07/29/2005') 
  GROUP BY ECode
  HAVING      (ECode = 'STA')
Else
  Select 0 As Hours

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[zemp]

Thanks George, That will work fine!

zemp