SQL Server Performance Forum – Threads Archive

return null in stored procedure

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

---

Or check out the coalesce() function coalesce( @hits, 0 ) will return 0 whenever @hits is null.

---

thanks

---

and also Isnull
IsNull( @hits, 0 ) Madhivanan Failing to plan is Planning to fail

---

]]>