SQL Server Performance

return null in stored procedure

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by LindaLi, Sep 19, 2005.

  1. LindaLi New Member

    I wrote a stored procedure. In this stored procedure, when no record is selected return 0. But when I execute the stored procedure, it returns null whatever the input date is.

    Thanks

    CREATE Procedure GetRolloutTotal
    @ToDatedatetime,
    @Hitsint output
    /* Param List */
    AS
    SET NOCOUNT ON

    SELECT @Hits = sum(eligibility)+sum(claim)+sum(provider)
    FROM dailytransactioncount
    WHERE (logdate < DateAdd(dd,1,@ToDate))
    if @Hits is Null
    set @Hits = 0
    return @Hits

    SET NOCOUNT OFF
  2. indshri Member

    You can use @@rowcount check for this, also you should use isnull check.
    Below should work I think --

    ( you may want to use variable to store @@rowcount as this is good practice,
    multpile OR conditions are used as precautionary, you may not need it,
    you may want to handle null condition for @ToDAte as well )
    )

    CREATE Procedure GetRolloutTotal
    @ToDate datetime,
    @Hits int output
    /* Param List */
    AS
    SET NOCOUNT ON

    SELECT @Hits = sum(isnull(eligibility,0))+sum(isnull(claim,0))+sum(isnull(provider,0))
    FROM dailytransactioncount
    WHERE (logdate < DateAdd(dd,1,@ToDate))

    if ( @@rowcount = 0 or @Hits is null or @Hits = 0 )
    set @Hits = 0

    return @Hits

    SET NOCOUNT OFF
  3. merrillaldrich New Member

    Or check out the coalesce() function

    coalesce( @hits, 0 ) will return 0 whenever @hits is null.
  4. LindaLi New Member

  5. Madhivanan Moderator

    and also Isnull
    IsNull( @hits, 0 )

    Madhivanan

    Failing to plan is Planning to fail

Share This Page