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