how to get rid of this CURSOR - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

how to get rid of this CURSOR

Author

Topic

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2007-07-21 : 04:22:18

I've a table with the following sample Data

col1 col2
10 11
10 24
12 11
14 18
17 10
11 15
18 12
19 21
19 30
21 16

for a given col2 I want to get all records that cols2 equals to that value and any other record that thier col1 or col2 equal to any value that match the first criteria. for example

SET col2 = 11
cols
10
11
12
14
15
17
18
24

I could write it with simulating a cursor:

SET NOCOUNT ON
CREATE TABLE Tbl (col1 int, col2 int)
GO
INSERT INTO Tbl
SELECT 10, 11 UNION ALL
SELECT 10, 24 UNION ALL
SELECT 12, 11 UNION ALL
SELECT 14, 18 UNION ALL
SELECT 17, 10 UNION ALL
SELECT 11, 15 UNION ALL
SELECT 18, 12 UNION ALL
SELECT 19, 21 UNION ALL
SELECT 19, 30 UNION ALL
SELECT 21, 16
GO

DECLARE @col2 int
DECLARE @t TABLE (col2 int PRIMARY KEY, Traversed bit)

SET @col2 = 11
INSERT INTO @t
SELECT DISTINCT col1, 0 FROM Tbl WHERE col2 = @col2 AND col1 <> @col2
IF @@ROWCOUNT <> 0 INSERT INTO @t VALUES (@col2, 0)

SELECT @col2 = MIN(col2)
FROM @t
WHERE Traversed = 0

WHILE @col2 IS NOT NULL
BEGIN
INSERT INTO @t
SELECT DISTINCT col1, 0
FROM Tbl
WHERE col2 = @col2 AND col1 NOT IN (SELECT col2 FROM @t)

INSERT INTO @t
SELECT DISTINCT col2, 0
FROM Tbl
WHERE col1 = @col2 AND col2 NOT IN (SELECT col2 FROM @t)

UPDATE @t SET Traversed = 1 WHERE col2 = @col2

SELECT @col2 = MIN(col2) FROM @t WHERE Traversed = 0
END

SELECT * FROM @t
GO

DROP TABLE Tbl
GO

I want to know if there is a way to do this job with one select query.

Thanks guys

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-07-22 : 15:47:52

Are you saying that Col1 is a "ParentID" and Col2 is the "ChildID" and you want to find the full hierarchy (upstream and downstream) for any given ChildID?

--Jeff Moden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-22 : 16:10:44

This do not handle circular reference, yet

-- Prepare sample data
DECLARE	@Sample TABLE (Col1 INT, Col2 INT)

INSERT	@Sample
SELECT	10, 11 UNION ALL
SELECT	10, 24 UNION ALL
SELECT	12, 11 UNION ALL
SELECT	14, 18 UNION ALL
SELECT	17, 10 UNION ALL
SELECT	11, 15 UNION ALL
SELECT	18, 12 UNION ALL
SELECT	19, 21 UNION ALL
SELECT	19, 30 UNION ALL
SELECT	21, 16

-- Initialize test
DECLARE	@StartingPoint INT
SET @StartingPoint = 11

-- Declare CTE's
;WITH Upstream (Col)
AS (
	SELECT	Col1
	FROM	@Sample
	WHERE	Col2 = @StartingPoint

	UNION ALL

	SELECT	@StartingPoint

	UNION ALL

	SELECT		s.Col2
	FROM		@Sample AS s
	INNER JOIN	Upstream AS u ON u.Col = s.Col1
)
,Downstream (Col)
AS (
	SELECT	Col1
	FROM	@Sample
	WHERE	Col2 = @StartingPoint

	UNION ALL

	SELECT	@StartingPoint

	UNION ALL

	SELECT		s.Col1
	FROM		@Sample AS s
	INNER JOIN	Downstream AS u ON u.Col = s.Col2
)

-- Show the expected result
SELECT Col
FROM	Upstream

UNION

SELECT Col
FROM	Downstream

Peter Larsson
Helsingborg, Sweden

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2007-07-23 : 08:14:31

Thanks Peter, you used CTE in your solution which doesnot exists in SQLServer 2000. Thats why i posted my problem SQL Server Development (2000) part of this forum. Can you show me how can i do it in SQL2K?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-23 : 08:42:39

Sorry. Forgot about the SQL 2000 part.
I think you are stuck with the solution you have. Unless you rewrite the CTE's as functions.

Peter Larsson
Helsingborg, Sweden

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2007-07-23 : 09:35:30

So you want to say this is one of the rare situations that we have to use a cursor or a simulated cursor? I feel there should be a way but i dont know how. anybody can help me?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-23 : 16:36:12

Since the upstream depth is unknown, there are no alternatives.
But, if you know the maximum depth, you can use left join.

Peter Larsson
Helsingborg, Sweden

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2007-07-24 : 06:57:19

No I dont Know. It's dynamic and depends on user's input.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources