This is a multi-part message in MIME format.
--=_NextPart_000_0019_01C6AC39.4D136D70
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hello,
I wrote the query below, but I was wondering whether there was a better = way of doing so. I am basically trying to build a recursive process.
Any suggestion would be greatly appreciated.
Thanks.
Mike
*************************************************************************= ********************
*************************************************************************= ********************
CREATE PROCEDURE dbo.CustomersFiltered
(
@.filter varchar(50)
)
AS
create table #t
(
dummyId bigint IDENTITY(1,1) PRIMARY KEY,
customerId bigint,
parentId bigint,
levelId int
)
INSERT #t (customerId, parentId, levelId)
SELECT customerAutoId, parentCustomerAutoId, 0
FROM customers
WHERE [name] like @.filter
declare @.dummyId bigint
select @.dummyId =3D 1
declare @.customerId bigint
declare @.parentId bigint
declare @.levelId bigint
WHILE (SELECT COUNT(*) FROM #t WHERE dummyId =3D @.dummyId) > 0
BEGIN
select @.customerId =3D customerId,
@.parentId =3D parentId,
@.levelId =3D levelId
FROM #t WHERE dummyId =3D @.dummyId
insert into #t (customerId, parentId, levelId)
select CustomerAutoId,
parentCustomerAutoId,
@.levelId - 1
from customers
where parentCustomerAutoId =3D @.customerId
group by CustomerAutoId,
parentCustomerAutoId
if (@.parentId is not null)
insert into #t (customerId, parentId, levelId)
select c.CustomerAutoId,
c.parentCustomerAutoId,
@.levelId + 1
from customers c
where c.customerAutoId =3D @.parentId
and not exists (select * from #t where c.customerAutoId =3D = customerId)
group by CustomerAutoId,
parentCustomerAutoId
select @.dummyId =3D @.dummyId + 1
END
select t.levelId as LevelId,
t.customerId as CustomerAutoId,
c.[name] as [Name],
c.sic as SIC,
c.ParentCustomerAutoId as ParentCustomerAutoId
from #t t
inner join customers c on c.customerAutoId =3D t.customerId
group by t.levelId,
t.customerId,
c.[name],
c.sic,
c.ParentCustomerAutoId
order by levelId desc,
c.ParentCustomerAutoId,
c.[name]
drop table #t
GO
--=_NextPart_000_0019_01C6AC39.4D136D70
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hello,
I wrote the query below, but I was = wondering whether there was a better way of doing so. I am basically trying to = build a recursive process.
Any suggestion would be greatly appreciated.
Thanks.
Mike
****************************************************************= *****************************
****************************************************************= *****************************
CREATE PROCEDURE dbo.CustomersFiltered( @.filter varchar(50))AS
create table #t( dummyId bigint IDENTITY(1,1) = PRIMARY KEY, customerId = bigint, parentId bigint, levelId int)
INSERT #t (customerId, parentId, levelId)SELECT = customerAutoId, parentCustomerAutoId, 0FROM customersWHERE [name] like = @.filter
declare @.dummyId bigintselect @.dummyId =3D 1
declare @.customerId bigintdeclare @.parentId bigintdeclare @.levelId bigint
WHILE (SELECT COUNT(*) FROM #t WHERE dummyId =3D @.dummyId) > 0BEGIN
select @.customerId =3D customerId, @.parentId =3D parentId, @.levelId =3D levelId FROM #t WHERE dummyId =3D @.dummyId
insert into #t (customerId, parentId, = levelId) select = CustomerAutoId, = parentCustomerAutoId, = @.levelId - 1 from customers where parentCustomerAutoId =3D @.customerId group by CustomerAutoId, = parentCustomerAutoId
if (@.parentId is not null) = insert into #t (customerId, parentId, levelId) = select c.CustomerAutoId, &nbs= p; c.parentCustomerAutoId, &nbs= p; @.levelId + 1 from customers c where c.customerAutoId =3D @.parentId and not exists (select = * from #t where c.customerAutoId =3D customerId) = group by CustomerAutoId, = parentCustomerAutoId
select @.dummyId =3D @.dummyId + 1
END
select t.levelId &nbs= p; as LevelId, t.customerId &= nbsp; as CustomerAutoId, c.[name] = ; as [Name], c.sic &n= bsp; as SIC, c.ParentCustomerAutoId as ParentCustomerAutoIdfrom #t t inner join customers c on c.customerAutoId =3D = t.customerIdgroup by t.levelId, t.customerId, c.[name], c.sic, c.ParentCustomerAutoIdorder by levelId desc, c.ParentCustomerAutoId, &nbs= p; c.[name]
drop table #tGO
--=_NextPart_000_0019_01C6AC39.4D136D70--If you're using SQL Server 2005, you should consider using Common Table
Expressions (CTE) instead. Temporary tables suffer from writing data to
the tempdb database, whereas CTEs are in memory only.
A good walktrough is available at the MSDN site:
http://msdn2.microsoft.com/en-us/library/ms186243.aspx
Hope this helps
"Mike" <none@.none.com> wrote in
news:uMSJXrFrGHA.4112@.TK2MSFTNGP02.phx.gbl:
> I wrote the query below, but I was wondering whether there was a
> better way of doing so. I am basically trying to build a recursive
> process.
> Any suggestion would be greatly appreciated.
> Thanks.
> Mike
>
> **********************************************************************
*
> **********************
> **********************************************************************
*
> **********************
> CREATE PROCEDURE dbo.CustomersFiltered
> (
> @.filter varchar(50)
> )
> AS
>
> create table #t
> (
> dummyId bigint IDENTITY(1,1) PRIMARY KEY,
> customerId bigint,
> parentId bigint,
> levelId int
> )
>
> INSERT #t (customerId, parentId, levelId)
> SELECT customerAutoId, parentCustomerAutoId, 0
> FROM customers
> WHERE [name] like @.filter
>
> declare @.dummyId bigint
> select @.dummyId = 1
>
> declare @.customerId bigint
> declare @.parentId bigint
> declare @.levelId bigint
> WHILE (SELECT COUNT(*) FROM #t WHERE dummyId = @.dummyId) > 0
> BEGIN
> select @.customerId = customerId,
> @.parentId = parentId,
> @.levelId = levelId
> FROM #t
> WHERE dummyId = @.dummyId
> insert into #t (customerId, parentId, levelId)
> select CustomerAutoId,
> parentCustomerAutoId,
> @.levelId - 1
> from customers
> where parentCustomerAutoId = @.customerId
> group by CustomerAutoId,
> parentCustomerAutoId
> if (@.parentId is not null)
> insert into #t (customerId, parentId, levelId)
> select c.CustomerAutoId,
> c.parentCustomerAutoId,
> @.levelId + 1
> from customers c
> where c.customerAutoId = @.parentId
> and not exists (select * from #t where c.customerAutoId => customerId)
> group by CustomerAutoId,
> parentCustomerAutoId
>
> select @.dummyId = @.dummyId + 1
> END
>
> select t.levelId as LevelId,
> t.customerId as CustomerAutoId,
> c.[name] as [Name],
> c.sic as SIC,
> c.ParentCustomerAutoId as ParentCustomerAutoId
> from #t t
> inner join customers c on c.customerAutoId = t.customerId
> group by t.levelId,
> t.customerId,
> c.[name],
> c.sic,
> c.ParentCustomerAutoId
> order by levelId desc,
> c.ParentCustomerAutoId,
> c.[name]
>
> drop table #t
> GO
--
Ole Kristian Bangås
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||I am using SQL Server 2000 SP4.
Is there anything that I could improve in my query? I checked all indexes,
even in the temp table, but I ran out of ideas.
Thanks.
Mike
"Ole Kristian Bangås" <olekristian.bangas@.masterminds.no> wrote in message
news:Xns98075D16535A0olekristianbangaas@.207.46.248.16...
> If you're using SQL Server 2005, you should consider using Common Table
> Expressions (CTE) instead. Temporary tables suffer from writing data to
> the tempdb database, whereas CTEs are in memory only.
> A good walktrough is available at the MSDN site:
> http://msdn2.microsoft.com/en-us/library/ms186243.aspx
> Hope this helps
>
> "Mike" <none@.none.com> wrote in
> news:uMSJXrFrGHA.4112@.TK2MSFTNGP02.phx.gbl:
>> I wrote the query below, but I was wondering whether there was a
>> better way of doing so. I am basically trying to build a recursive
>> process.
>> Any suggestion would be greatly appreciated.
>> Thanks.
>> Mike
>>
>> **********************************************************************
> *
>> **********************
>> **********************************************************************
> *
>> **********************
>> CREATE PROCEDURE dbo.CustomersFiltered
>> (
>> @.filter varchar(50)
>> )
>> AS
>>
>> create table #t
>> (
>> dummyId bigint IDENTITY(1,1) PRIMARY KEY,
>> customerId bigint,
>> parentId bigint,
>> levelId int
>> )
>>
>> INSERT #t (customerId, parentId, levelId)
>> SELECT customerAutoId, parentCustomerAutoId, 0
>> FROM customers
>> WHERE [name] like @.filter
>>
>> declare @.dummyId bigint
>> select @.dummyId = 1
>>
>> declare @.customerId bigint
>> declare @.parentId bigint
>> declare @.levelId bigint
>> WHILE (SELECT COUNT(*) FROM #t WHERE dummyId = @.dummyId) > 0
>> BEGIN
>> select @.customerId = customerId,
>> @.parentId = parentId,
>> @.levelId = levelId
>> FROM #t
>> WHERE dummyId = @.dummyId
>> insert into #t (customerId, parentId, levelId)
>> select CustomerAutoId,
>> parentCustomerAutoId,
>> @.levelId - 1
>> from customers
>> where parentCustomerAutoId = @.customerId
>> group by CustomerAutoId,
>> parentCustomerAutoId
>> if (@.parentId is not null)
>> insert into #t (customerId, parentId, levelId)
>> select c.CustomerAutoId,
>> c.parentCustomerAutoId,
>> @.levelId + 1
>> from customers c
>> where c.customerAutoId = @.parentId
>> and not exists (select * from #t where c.customerAutoId =>> customerId)
>> group by CustomerAutoId,
>> parentCustomerAutoId
>>
>> select @.dummyId = @.dummyId + 1
>> END
>>
>> select t.levelId as LevelId,
>> t.customerId as CustomerAutoId,
>> c.[name] as [Name],
>> c.sic as SIC,
>> c.ParentCustomerAutoId as ParentCustomerAutoId
>> from #t t
>> inner join customers c on c.customerAutoId = t.customerId
>> group by t.levelId,
>> t.customerId,
>> c.[name],
>> c.sic,
>> c.ParentCustomerAutoId
>> order by levelId desc,
>> c.ParentCustomerAutoId,
>> c.[name]
>>
>> drop table #t
>> GO
> --
> Ole Kristian Bangås
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment