Re: Re: UKHA Classified update - SQL Help!

[steve.cooper@xxxxxxx]

Graham

Here is a procedure that will do the work you need:

CREATE PROCEDURE sp_CountItems

@ProductTypeID int
as
set nocount on

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = object_id
('tempdb.dbo.##tablist') and sysstat & 0xf = 3)
DROP TABLE dbo.##tablist

declare @current int
declare @count int
select @count = 0

Select ProductTypeID into ##tablist from tblProductsType where
ParentProductTypeID = @ProductTypeID

while (select count(*) from ##tablist) > 0
begin
Select @current = min(ProductTypeID) from ##tablist
Select @count = @count + Count(*) from tblProducts where productTypeID
= @current
Insert into ##tablist Select ProductTypeID from tblProductsType where
ParentProductTypeID = @current
delete from ##tablist where ProductTypeID = @current
end
return @count

-----------------------------------------------

You can run it with the following SQL:

declare @tot int
execute @tot = sp_Countitems 4
select @tot

-----------------------------------------------

There is a way to do it without a temporary table but it involves
recursively calling the same procedure and gets very messy trying to
dynamically allocate different cursor names on each entry to the stored
proc.

S.



___________________________________________________________________________

The information contained in this message is confidential and may be
legally privileged. If you are not the intended recipient, please do not
read, copy or otherwise use it and do not disclose it to anyone else.
Please notify the sender of the delivery error and then delete the
message from your system.

Any views or opinions expressed in this email are those of the author only.

Thank you for your assistance.

___________________________________________________________________________