How to turn null result to zero?
-
Hi,
I have a select count(*) statement in a stored procedure. How to make the count(*)=0 when it's null?
here's my sp:
-------------------------------------------
CREATE Procedure sp_CountCommunityStore
(
@ZipCode Numeric(5, 0) = Null
)
As
set nocount on
Declare @Latitude Float(10)
Declare @Longitude Float(10)
-- Lookup longitude, latitude for zip codes
Select @Latitude = Latitude, @Longitude = Longitude From Zips_Range_Table Where Zip_Code = @ZipCode
Select case when count(*) is null then 0 when count(*)>0 then 1 end as CountStore
From
Zips_Range_Table z, InSightAmKgODS.micros.Store_Table s
Where
Longitude Is Not Null
And Latitude Is Not Null
And (
3958.75 * ACos(Sin(@Latitude/57.2958) *
Sin(Latitude/57.2958) +
Cos(@Latitude/57.2958) *
Cos(Latitude/57.2958) *
Cos(Longitude/57.2958 - @Longitude/57.2958)) ) <=25
And z.zip_code=s.zip_code and s.co_code=1 and s.store_mgr is not null
Group by s.zip_Code, s.store_num, s.store_address,s.city,s.state
return
GO
-
I think what you really want to be doing is selecting @@rowcount into a variable after your assignment select
select @var = col from table
select @rows = @@ROWCOUNT
(edit: Or put your count on the same assignment select and use a group by)
-
I wanna see the query which actually returns you count(*) = null.
I was always sure it's not possible.
_____________
Code for TallyGenerator -
Agreed.
Only way I know of is if you use count(*) as a value in some expression, then the result of the expression may be null...
select count(*) - null
However, that is the expression, not the count(*) function that evaluates to null.
count(*) itself always returns an int, afaik never null.
Are you positive that your problem is with the count(*) itself?
Could you expand on it? Perhaps some sample data along with the example that demonstrates the behaviour you're seeing? (and of course a description of the wanted behaviour as well)
/Kenneth
-
As the others have mentioned, COUNT(*) will not return null.
It appears you want to return any value > 0 as 1.
So your SELECT statement can be simplified to:
Select CASE Count(*) WHEN 0 THEN 0 ELSE 1 END AS CountStore
From Zips_Range_Table z, InSightAmKgODS.micros.Store_Table s
...
-
...CASE Count(*) WHEN 0 THEN 0 ELSE 1 END AS CountStore... Or
SIGN(COUNT(*)) AS [CountStore]
Far away is close at hand in the images of elsewhere.
Anon. -
Yup, much better.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply