January 19, 2008 at 9:45 am
I have a stored proc that accepts 3 input parms to take the count of rows
with a where clause
select count(*) from dbo.tab1 M
where M.region=@region and M.hub_name=@hub_name and M.account_name=@account_name)
Unfortunately, my table data is bad (region or hub_name or account_name can be null) and when that happens, the count is incorrect when in actuality there are rows.
Anyway, I can get the correct counts given the fact that null is allowed in all columns of that table?
Thanks
January 19, 2008 at 10:00 am
Count (*) will give you the total number of rows in the result set. Count(column name) give you the number of non-null values in the column.
If there are nulls in the columns you're filtering on, then you have to allow for nulls so that the rows can appear in the result set.
where (M.region=@region OR M.region IS NULL) and (M.hub_name=@hub_name OR M.hub_name IS NULL) and (M.account_name=@account_name OR M.account_name IS NULL)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 21, 2008 at 7:30 am
GilaMonster (1/19/2008)
If there are nulls in the columns you're filtering on, then you have to allow for nulls so that the rows can appear in the result set.where (M.region=@region OR M.region IS NULL) and (M.hub_name=@hub_name OR M.hub_name IS NULL) and (M.account_name=@account_name OR M.account_name IS NULL)
eliminate the OR logic by wrapping the column with isnull():
where isnull(M.region,@region)=@region
and isnull(M.hub_name,@hub_name)=@hub_name
and isnull(M.account_name,@account_name)=@account_name
January 21, 2008 at 8:31 am
Thank you both, Gail and Antonio. Works fine.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply