DECLARE @RC int
DECLARE @ID int
DECLARE @Level int


EXEC @RC = [dbo].[sp_userHierarchy] @UserID, 1

declare @RowCnt int
declare @MaxRows int
declare @UserID int
select	@RowCnt = 1

declare @Import table (UserID varchar(9))
insert into @Import SELECT ID AS UserID FROM [dbo].[userHierarchy] (@UserID ,1)

declare @tblAir table (userID varchar(50), Distance varchar(50))

select @MaxRows=count(*) from @Import
while @RowCnt <= @MaxRows
begin
	SET @UserID = (SELECT TOP 1 UserID FROM @Import)

	insert into @tblAir SELECT * FROM [dbo].[getFlightDataNew] (@UserID, '2000-01-01', '2010-01-01')
    Select @RowCnt = @RowCnt + 1
end

SELECT * FROM @tblAir

quote:

---

Originally posted by BorisCallens

While this isn't a direct answer to your question, you might want to check on rowcount (http://doc.ddart.net/mssql/sql70/set-set_32.htm)
It's a built in function to limit your output rows. This could eliminate while @RowCnt <= @MaxRows and Select @RowCnt = @RowCnt + 1

Also, I didn't completely grasp your case, but usually, when I'm asking myself how to do a procedure on each line, the problem is solvable with a join of some kind.
A bit vague, but untill the specialsts chip in, maybe this can alread get you somewhere.

---