none
Returning 0 when no records exist in a sql query

    Question

  • How can I return a 0 when no records exists in an ms sql query?  I'm unable to use ISNULL or COALEASE functions since no rows are being returned at all.

    AS


    • Edited by ADS97_ Tuesday, July 02, 2013 6:48 PM
    Tuesday, July 02, 2013 6:47 PM

Answers

All replies

  • Can we see the query?

    You can use SET NOCOUNT OFF to return the number of rows affected by your query. This will be another recordset at the client side.


    AMB

    Some guidelines for posting questions...

    Tuesday, July 02, 2013 6:57 PM
    Moderator
  • If you expect your result to be just a few rows, you can

    if object_id('tempdb..#Results', N'U') is not null drop table #Results;

        select ... into #Results from ... - your query into #Results table

    IF @@ROWCOUNT= 0 

        select 0 as FinalResult;

    ELSE 

       select * from #Results;


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, July 02, 2013 7:04 PM
    Moderator
  • IF NOT EXISTS (SELECT ... FROM..)
       RETURN 0;


    Bob - www.crowcoder.com

    Tuesday, July 02, 2013 8:15 PM
  • Thanks.  Well this is a really rough example of what I'm trying to do if it can be done@ all. 

    DECLARE

    @callcproj Int

    IF

    Count(@callproj) < 0

    SELECT

    @callproj as Sol, list_subset as List,

    COUNT

    (@callproj) AS Cnt

    FROM

    ProjList

    Where

    Status in ('o', 'r', 's', 't')

    and

    CallCProj not in (select CallProj from ProjList where Status IN ('o', 'r', 's', 't')

    )

    GROUP BY callproj, list_subset

    ELSE

    SELECT

    COUNT(@callproj)I need to return zeros where there are no records where no records exists for those statuses listed in the criteria.  Is this possible to do?  Any assistance is greatly appreciated.


    AS

    Tuesday, July 02, 2013 8:25 PM
  • Received the following error:  Msg 178, Level 15, State 1, Line 13

    A RETURN statement with a return value cannot be used in this context.

    I'm returning more than 1 record, so I don't think I can use Return 0


    AS

    Wednesday, July 03, 2013 3:35 PM
  • Did you see my suggestion?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, July 03, 2013 3:40 PM
    Moderator
  • Yes, thank you.  However, I'm returning many rows.

    AS

    Wednesday, July 03, 2013 4:20 PM
  • You can do it with UNION:

    SELECT ProductID, ProductName=Name, ListPrice FROM Production.Product WHERE Color is not null
    UNION
    SELECT 0, '0', 0.0  WHERE NOT EXISTS ( SELECT 1 FROM Production.Product WHERE Color is not null);
    -- (256 row(s) affected)
    
    SELECT ProductID, ProductName=Name, ListPrice FROM Production.Product WHERE Color = 'LightGreen'
    UNION
    SELECT 0, '0', 0.0  WHERE NOT EXISTS ( SELECT 1 FROM Production.Product WHERE Color = 'LightGreen');
    /*
    ProductID	ProductName	ListPrice
    0	0	0.0000
    */

    You can also return NULLs:

    SELECT ProductID, ProductName=Name, ListPrice FROM Production.Product WHERE Color = 'LightGreen'
    UNION
    SELECT NULL, NULL, NULL  WHERE NOT EXISTS ( SELECT 1 FROM Production.Product WHERE Color = 'LightGreen');
    /*
    ProductID	ProductName	ListPrice
    NULL	NULL	NULL
    */
     

    In SSRS you can set the NoRowsMessage parameter for empty result set:  http://stackoverflow.com/questions/10146944/how-do-i-display-no-data-available-when-there-are-no-rows-to-show-on-the-repo


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Wednesday, July 03, 2013 4:48 PM
    Moderator
  • You don't need to use UNION, you can use UNION ALL.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, July 03, 2013 4:58 PM
    Moderator
  • When no records exist, the query returns no records@ all.  This would be much easier if Nulls were returned instead of nothing.


    AS

    Wednesday, July 03, 2013 6:52 PM
  • Did you try Kalman's suggestion but with UNION ALL?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, July 03, 2013 8:58 PM
    Moderator
  • Couldn't get this to work.  No values returned as 0.


    AS

    Friday, July 05, 2013 7:21 PM
  • Yes.  No luck.

    AS

    Friday, July 05, 2013 7:22 PM
  • Can you post the query you tried?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, July 05, 2013 7:30 PM
    Moderator