sql server递归调用

shangban

INSERT loc(dgid,dgname,parentid) VALUES( 1,'河北省',NULL);
INSERT loc(dgid,dgname,parentid) VALUES( 2,'石家庄',1) ;
INSERT loc(dgid,dgname,parentid) VALUES( 3,'保定',1) ;
INSERT loc(dgid,dgname,parentid) VALUES( 4,'山西省',NULL) ;
INSERT loc(dgid,dgname,parentid) VALUES( 5,'太原',4) ;
INSERT loc(dgid,dgname,parentid) VALUES( 6,'新华区',2) ;
INSERT loc(dgid,dgname,parentid) VALUES( 7,'北焦村',6) ;
INSERT loc(dgid,dgname,parentid) VALUES( 8,'大郭村',6) ;
INSERT loc(dgid,dgname,parentid) VALUES( 9,'河南省',NULL) ;
INSERT loc(dgid,dgname,parentid) VALUES( 10,'大郭村南',8) ;
INSERT loc(dgid,dgname,parentid) VALUES( 11,'大郭村北',8) ;
INSERT loc(dgid,dgname,parentid) VALUES( 12,'北焦村东',7) ;
INSERT loc(dgid,dgname,parentid) VALUES( 13,'北焦村西',7) ;
INSERT loc(dgid,dgname,parentid) VALUES( 14,'桥东区',3) ;
INSERT loc(dgid,dgname,parentid) VALUES( 15,'桥西区',3);


CREATE PROCEDURE [dbo].[pr_GetLocations]
AS
BEGIN
    WITH locs(dgid,dgname,parentid,loclevel)
    AS
    (
        SELECT dgid,dgname,parentid,0 AS loclevel FROM loc
        WHERE parentid IS NULL
        UNION ALL
        SELECT l.dgid,l.dgname,l.parentid,loclevel+1 FROM loc l
            INNER JOIN locs p ON l.parentid=p.dgid
    )

    SELECT * FROM locs
END