/*CREATE TABLE t(id INT ,parentid INT ) INSERT INTO t SELECT 1, 0 UNION ALL SELECT 2, 1 UNION ALL SELECT 3, 2 UNION ALL SELECT 4, 3 UNION ALL SELECT 5, 4 */ CREATE PROC test ( @a INT ) as --DECLARE @a INT --SET @a=5 ;WITH cte (id,parentid) AS ( SELECT id,parentid FROM t WHERE id=@a UNION ALL SELECT b.id,b.parentid FROM cte a INNER JOIN T b ON b.id=a.parentid ) SELECT TOP 1 * FROM cte ORDER BY id
INSERT INTO t
SELECT 1, 0
UNION ALL
SELECT 2, 1
UNION ALL
SELECT 3, 2
UNION ALL
SELECT 4, 3
UNION ALL
SELECT 5, 4
*/
CREATE PROC test
( @a INT )
as
--DECLARE @a INT
--SET @a=5
;WITH cte (id,parentid)
AS
(
SELECT id,parentid
FROM t
WHERE id=@a
UNION ALL
SELECT b.id,b.parentid
FROM cte a INNER JOIN T b ON b.id=a.parentid
)
SELECT TOP 1 * FROM cte ORDER BY id