how do I return a default record if result set is empty

[axasyn36]

Hello all,
I am working on a sitemap provider for a web site and want to return a default value for a visitors 'zone' if their detected country is not defined as belonging to any particular zone. I am completely new to SQL and have been trying for days so I would really apreciate any help you can offer.

this is where I have got to and although it executes (small cause for celabration)
when I try and use it in a aspx I get and error. I'm not sure if this is the right aproach, it's just what I am trying. Thanks in advance.

DECLARE 
@nodetreeByCountry int,
@nodetreeByDefault int
 
SELECT @nodetreeByCountry =
    'SELECT    siteZone.nodeTreeID
FROM         zoneCountry INNER JOIN
                      siteZone ON zoneCountry.zoneID = siteZone.zoneID INNER JOIN
                      siteDNS INNER JOIN
                      DNS ON siteDNS.DNSID = DNS.DNSID ON siteZone.siteID = siteDNS.siteID
WHERE     (DNS.DomainName = @domainName) AND (zoneCountry.countryISOCode = @countryISOCode)'
SELECT @nodetreeByDefault =
    'SELECT    siteZone.nodeTreeID
FROM         zoneCountry INNER JOIN
                      siteZone ON zoneCountry.zoneID = siteZone.zoneID INNER JOIN
                      siteDNS INNER JOIN
                      DNS ON siteDNS.DNSID = DNS.DNSID ON siteZone.siteID = siteDNS.siteID
WHERE     siteZone.isDefaultZone'
SELECT ISNULL(@nodetreeByCountry,@nodetreeByDefault)
 

[Vidhura]

Hello all,
I am working on a sitemap provider for a web site and want to return a default value for a visitors 'zone' if their detected country is not defined as belonging to any particular zone. I am completely new to SQL and have been trying for days so I would really apreciate any help you can offer.

this is where I have got to and although it executes (small cause for celabration)
when I try and use it in a aspx I get and error. I'm not sure if this is the right aproach, it's just what I am trying. Thanks in advance.

DECLARE 
@nodetreeByCountry int,
@nodetreeByDefault int
 
SELECT @nodetreeByCountry =
    'SELECT    siteZone.nodeTreeID
FROM         zoneCountry INNER JOIN
                      siteZone ON zoneCountry.zoneID = siteZone.zoneID INNER JOIN
                      siteDNS INNER JOIN
                      DNS ON siteDNS.DNSID = DNS.DNSID ON siteZone.siteID = siteDNS.siteID
WHERE     (DNS.DomainName = @domainName) AND (zoneCountry.countryISOCode = @countryISOCode)'
SELECT @nodetreeByDefault =
    'SELECT    siteZone.nodeTreeID
FROM         zoneCountry INNER JOIN
                      siteZone ON zoneCountry.zoneID = siteZone.zoneID INNER JOIN
                      siteDNS INNER JOIN
                      DNS ON siteDNS.DNSID = DNS.DNSID ON siteZone.siteID = siteDNS.siteID
WHERE     siteZone.isDefaultZone'
SELECT ISNULL(@nodetreeByCountry,@nodetreeByDefault)
 

Hi
Try this

DECLARE 
@nodetreeByCountry int,
@nodetreeByDefault int
 
SET @nodetreeByCountry =
    (SELECT    siteZone.nodeTreeID
FROM         zoneCountry INNER JOIN
                      siteZone ON zoneCountry.zoneID = siteZone.zoneID INNER JOIN
                      siteDNS INNER JOIN
                      DNS ON siteDNS.DNSID = DNS.DNSID ON siteZone.siteID = siteDNS.siteID
WHERE     (DNS.DomainName = @domainName) AND (zoneCountry.countryISOCode = @countryISOCode))
SET @nodetreeByDefault =
    (SELECT    siteZone.nodeTreeID
FROM         zoneCountry INNER JOIN
                      siteZone ON zoneCountry.zoneID = siteZone.zoneID INNER JOIN
                      siteDNS INNER JOIN
                      DNS ON siteDNS.DNSID = DNS.DNSID ON siteZone.siteID = siteDNS.siteID
WHERE     siteZone.isDefaultZone)
SELECT ISNULL(@nodetreeByCountry,@nodetreeByDefault)