ISNULL & CAST in SP 1

[WordTechinc]

If [History count} is zero, [Final Count} should have [All count]. But [Final Count] value returns as zero. How to move [History count] into [Final Count]? This is part of SELECT within Store Procedure.





ISNULL(cast (([ALL count]-[History count]) as integer(18)),0) AS [Final Count]

 

[gmmastros]

I suspect HistoryCount is not 0, but null. When you perform math with NULL, the result is always NULL. I think you need to do ISNULL first, like this:

ISNULL([ALL count], 0)-IsNull([History count], 0) AS [Final Count]

You don't need an ISNULL around the whole thing because and non-null subtract a non-null results in a non-null. If HistoryCount is NULL or 0, the output will be the same as All Count.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[WordTechinc]

Thank you gmmastros. I spent 2 days to resolve this problem. Thank you very much.