Query assistance - 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 2008 Forums

Transact-SQL (2008)

Query assistance

Author

Topic

Wrangler
Starting Member

35 Posts

Posted - 2011-11-03 : 16:50:31

How can I do this a little more eloquently?

@catid1 = 14

SELECT LastName, CatID
FROM CatPub
WHERE CatID = @catid1

SELECT @catid2 = ParentID FROM Category WHERE CategoryID = @catid1

SELECT LastName, CatID
FROM CatPub
WHERE CatID = @catid2

SELECT @catid3 = ParentID FROM Category WHERE CategoryID = @catid2

SELECT LastName, CatID
FROM CatPub
WHERE CatID = @catid3

SELECT @catid4 = ParentID FROM Category WHERE CategoryID = @catid3

SELECT LastName, CatID
FROM CatPub
WHERE CatID = @catid4

Thanks,

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-11-03 : 17:09:17

This compiles, but I'm not sure it's correct:

DECLARE @catid1 INT = 14
;WITH CTE(LastName, CatID, Parent) AS(
SELECT A.LastName, A.CatID, B.ParentID
FROM CatPub A
INNER JOIN Category C ON A.CatID=C.CategoryID
WHERE A.CatID = @catid1
UNION ALL
SELECT A.LastName, A.CatID, C.ParentID
FROM CTE A
INNER JOIN Category C ON A.Parent=C.CategoryID)
SELECT LastName, CatID FROM CTE OPTION (MAXRECURSION 4)

I'm assuming you don't actually need the @catid2,3,4 variables.

Wrangler
Starting Member

35 Posts

Posted - 2011-11-03 : 18:21:40

Thats very eloquent. Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources