How to handle missing data 1

[wbodger]

I have a function with the following query that may or may not have any records, but I need to return a value regardless

with optoutcte AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY OptOutDate DESC) AS Ordered
FROM tblSiteTaskOptOuts
WHERE SiteID=siteid
and TypeID=1
)
select ISNULL(OptOut,0) AS Status 
from optoutcte where Ordered=1

I was hoping that the ISNULL would work when there were no records in the CTE, but no luck. Any thoughts? I will be passing in a SiteID and there may not be any records. OptOut is a bit type field, so it can be 0 or 1. If there is no record, I want it to return a 0. Help?

wb

 

[bborissov]

IF NOT EXISTS(SELECT * FROM tblSiteTaskOptOuts WHERE SiteID=siteid and TypeID=1)
   SELECT CAST(0 AS bit) AS Status
ELSE
   SELECT OptOut
          FROM (SELECT OptOut, ROW_NUMBER() OVER (ORDER BY OptOutDate DESC) AS Ordered
                      FROM tblSiteTaskOptOuts
                WHERE SiteID=siteid
                  and TypeID=1) Tbl1
   WHERE Ordered = 1

Borislav Borissov
VFP9 SP2, SQL Server