Function in WHERE clause

Question

Function in WHERE clause

Default port

Points: 1435

March 13, 2005 at 1:09 pm

Can someone tell me if function "IntListCount" get processed for each row or
does MSSQL calculate it once before using it?

select 
*
from
PersonCategoryAssign
inner join
dbo.IntListToTable( @categoryIDs ) 
on number = PersonCategoryID 
where
ACID = Person.ACID 
group by 
Person.ACID
having
count(*) = dbo.IntListCount( @categoryIDs )

Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 1

I am pretty sure it is processed for each row. You could do something like this:

declare @CatCount int
select @CatCount = dbo.IntListCount(@categoryIDs)
select * from PersonCategoryAssign inner join dbo.IntListToTable( @categoryIDs ) on number = PersonCategoryID where ACID = Person.ACID group by Person.ACID having count(*) = @CatCount

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

lenardd Default port Points: 1435 More actions · Answer 2

Thanks Kathi.

That is actually what I did after posting the message. Also, because that snippet actually lives in a WHERE clause (I do an EXISTS on it), I also process IntListToTable() into a variable @table before I join it.

--Lenard

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 3

Also do not forget the order of precedence.

The having clause is done after the join and the where clause are "executed", the dataset is available and just after the filter in the having clause is executed.

Bye
Gabor