How to run a stored procedure in a loop

I have a table for "Departments" (ID,deptID)

ID  parentdeptID  
2 null  
3 null  
7 2  
8 2  
9 3  

and a second table "Filter" contains IDs only 开发者_开发问答

ID  
2  
9  

and a stored procedure with only input prameter @deptID and the output is a result from joining some of tables depending on the input ID

my question is: a way read from table "Filter" and gets IDs, and for each ID get its childs if it has

in our example:

2,7,8,9 (2,9 from "Filter" and 7,8 from "Departments" as childs of 2)

and then for each one of this 4 IDs excute the stored procedure by ID

This is the overview, if any one has a better way without using stored procedure he is welcome

I'm not sure I understand your model too well, but here's how you can do a loop and execute a stored procedure inside of it:

Edit I think I understand your question a little better now.

declare @filterId int
declare @deptId int

select @filterId = min(ID) from Filter
while @filterId is not null
   begin
      -- run the stored procedure for the main filter ID
      exec procedureName @filterId

      -- run the SP for the related IDs in Department table
      select @deptId = min(ID) from Departments where parentdeptID = @filterID
      while @deptId is not null
         begin
            exec procedureName @deptId
            select @deptId = min(ID) from Departments where ID > @deptId and parentdeptID = @filterID
         end

      select @filterId = ID from Filter where ID > @filterId
   end

Hopefully this gets you started.

To do what you want without the stored procedure you should join the DeptID's with the query inside the stored procedure.

Assume that your SP does a select DeptID, DeptInfo from @SomeOtherTableWithData where DeptiID = @DeptID.

Here is sample code with your tables that replaces the stored procedure with a join instead.

-- Setup sample data
declare @Departments table (DeptID int, ParentDeptID int)
declare @Filter table (DeptID int)
declare @SomeOtherTableWithData table (DeptID int, DeptInfo varchar(50))

insert into @Departments values (2, null)  
insert into @Departments values (3, null)  
insert into @Departments values (7, 2)  
insert into @Departments values (8, 2)  
insert into @Departments values (9, 3) 

insert into @Filter values(2)
insert into @Filter values(9)

insert into @SomeOtherTableWithData values (2, 'Info DeptID 2')
insert into @SomeOtherTableWithData values (3, 'Info DeptID 3')
insert into @SomeOtherTableWithData values (7, 'Info DeptID 7')
insert into @SomeOtherTableWithData values (8, 'Info DeptID 8')
insert into @SomeOtherTableWithData values (9, 'Info DeptID 9')

-- Get the DeptID's into temporary table #DeptIDs
select D.DeptID  -- Parents
into #DeptIDs 
from @Departments as D
    inner join @Filter as F
        on D.DeptID = F.DeptID
union
select D.DeptID  -- Children
from @Departments as D
    inner join @Filter as F
        on D.ParentDeptID = F.DeptID

-- Use #DeptID in a join with the query in the stored procedure
select S.DeptID, S.DeptInfo
from #DeptIds as D
    inner join @SomeOtherTableWithData as S
        on D.DeptID = S.DeptID

-- Drop the temporary table
drop table #DeptIDs

The result is

DeptID  DeptInfo
2   Info DeptID 2
7   Info DeptID 7
8   Info DeptID 8
9   Info DeptID 9

You can use a sub-query instead of the temporary table if you want just one SQL statement.