使用游标查询子部门下的数据

使用游标查询数据

 

--这个是根据部门ID，和公司，找到这个部门下的子部门，然后根据子部门ID查找该部门有多少人多少辆车

-- UP3_Tdept_SelectByTDtPID 2

alter proc  UP3_Tdept_SelectByTDtPID

@TDtID int

as

begin

    create table #aa

    (

    TDtID int,

    TDtName varchar(50),

    car int,

    people int

    )

 

    declare @car int

    DECLARE Employee_Cursor CURSOR  local FOR(select TDtID from tdept where tdtpid=@TDtID )--父部门下的子部门的部门ID(TDtID)

    OPEN Employee_Cursor

 

    FETCH NEXT FROM Employee_Cursor into @car

 

    WHILE @@FETCH_STATUS = 0

        BEGIN

           insert  into #aa(TDtID,TDtName,car ,people)

           select  TDtID ,TDtName,dbo.Select_car_bydep2(TDtID) ,dbo.[Select_car_byperson2](TDtID)--将TDtID作为参数传给函数并将查询出来的数据插入临时表中

           from tdept

           where TDtID =@car

           order by TDtID desc

           FETCH NEXT FROM Employee_Cursor into  @car

        END

    select * from #aa

    CLOSE Employee_Cursor;

    drop table #aa

end