The UK Home Automation Archive

Archive Home
Group Home
Search Archive


Advanced Search

The UKHA-ARCHIVE IS CEASING OPERATIONS 31 DEC 2024


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Re: UKHA Classified update - SQL Help!


  • To: ukha_d@xxxxxxx
  • Subject: Re: Re: UKHA Classified update - SQL Help!
  • From: steve.cooper@xxxxxxx
  • Date: Wed, 27 Mar 2002 14:23:12 +0000
  • Delivered-to: mailing list ukha_d@xxxxxxx
  • Mailing-list: list ukha_d@xxxxxxx; contact ukha_d-owner@xxxxxxx
  • Reply-to: ukha_d@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.

___________________________________________________________________________



Home | Main Index | Thread Index

Comments to the Webmaster are always welcomed, please use this contact form . Note that as this site is a mailing list archive, the Webmaster has no control over the contents of the messages. Comments about message content should be directed to the relevant mailing list.