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' instead of Null. The SP does return 1 row for each record in TBL_CAMPAIGNS_CHARITIES where it corresponds to TBL_CAMPAIGNS.

CREATE PROCEDURE web.getPublicCampaignData
   (
   @campaign BIGINT
   )
AS
BEGIN

SELECT * 
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

Answer 1

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".

Answer 2

select isnull(sum(size),0) as total_size from my_table where is_valid = 1

Answer 3

Aside from isnull on SQL Server, I'm a big fan of coalesce, since that takes any number of arguments. For two arguments, it's functionally equivalent to isnull:

return coalesce(yourvalue, 0)

Is the same as:

return isnull(yourvalue, 0)

However,

coalesce(yourvalue, backupval, 0)

is equivalent to

isnull(isnull(yourvalue, backupval), 0)

Obviously, coalesce is much more readable in this case, so that's why I'm a fan.

Apologies for a long-winded answer on a very simple question, but hey, why not learn something else, eh?

Answer 4

ISNULL is maybe faster bur it's specific to SQLServer.

COALESCE is ANSI standard

Here's some differences and other explanations about ISNULL and COALESCE