SP return null as ZERO?

Question

Using a stored procedure i have a fairly complex SQL statement which returns a COUNT value as a pseudo column. In many cases the result will be 'null'. This causes problems in my application, so i am wondering if it is possible to return a 'null' as '0' by default from the stored procedure? Thanks. UPDATE I need to apply the ISNULL to the following statement; ``` select recip_Chosen, recip_CampaignId) AS ChosenCount from TBL_CAMPAIGNRECIPIENTS WHERE recip_CampaignId = @campaign group by recip_Chosen ``` Which should be something like; ``` select recip_Chosen, ISNULL(count(recip_CampaignId),0) AS ChosenCount from TBL_CAMPAIGNRECIPIENTS WHERE recip_CampaignId = @campaign group by recip_Chosen ``` However, this still returns null(s) in the ChosenCount column. Any ideas? UPDATE Ok so the above statement is part of a much larger one as shown below. Here is the full Stored Procedure, but not the table structure since it involves several tables. I only need 'ChosenCount' to return '0' instea…

Accepted Answer

Yes:

```
RETURN ISNULL(yourvalue, 0)
```

What this basically means:

if "yourvalue" (either a computed value, or the value of a @variable) is NOT NULL, then return that value

if "yourvalue" IS NULL, then return the second parameter (here "0") instead

I don't know exactly how you compute your count - I assume you're assigning it to some local variable in your stored procedure:

```
CREATE PROCEDURE dbo.DoMyCount()
RETURNS INT
AS BEGIN
   DECLARE @MyCountVariable INT

SET @MyCountVariable = SELECT COUNT(*)......... -- whatever you do here

RETURN ISNULL(@MyCountVariable, 0)
END
```

In this case, your stored procedure `dbo.DoMyCount` will return a count (however you computed that), or "0" (zero) is that count comes out to NULL.

That way, you can be sure to always get back a valid NON-NULL INT value from the stored procedure.

Marc

UPDATE:

The stored proc makes it clear why the ISNULL doesn't work - it's being used inside a LEFT OUTER JOIN. If no data matches, then the whole select inside the LEFT OUTER JOIN will never be called --> the ISNULL never gets a chance to do its work.

You need to restructure your stored proc a bit:

```
CREATE PROCEDURE web.getPublicCampaignData(@campaign BIGINT)
AS BEGIN
  SELECT
     (list of fields), ISNULL(CRC.ChosenCount, 0), .....
  FROM TBL_CAMPAIGNS C
  INNER JOIN TBL_MEMBERS M 
     ON C.campaign_MemberId = M.members_Id
  INNER JOIN TBL_CAMPAIGNS_CHARITIES CC 
     ON C.campaign_Key = CC.camchar_CampaignID
  INNER JOIN TBL_CHARITIES CH 
    ON CC.camchar_CharityID = CH.cha_Key
  LEFT OUTER JOIN (
    select recip_Chosen, count(recip_CampaignId) as ChosenCount
    from TBL_CAMPAIGNRECIPIENTS
    WHERE recip_CampaignId =  @campaign
    group by recip_Chosen
  ) CRC on CH.cha_Key = CRC.recip_Chosen
  WHERE 
     C.campaign_Key = @campaign;
END
```

That way, if the `CH.cha_Key = CRC.recip_Chosen` doesn't match and the LEFT OUTER JOIN returns NULLs, it will be caught and the ChosenCount = NULL will be turned into a "0".

SP return null as ZERO?

Solution

Problem