SQL How to loop through a table and merge result?

Question

Hi, I am trying to do something like this:

while @nrOfAuthlevels >= @myAuthLevel
begin
 set @myAuthLevel = @myAuthLevel + 1 
 SELECT  Role.name, Role.authorityLevel
 FROM        [dbo].[Role]
 ORDER BY Role.authorityLevel
end

The result of this stored procedure shall be a table with all Role.authorityLevel below my own. But this generates several tables.

Thanks in advance

Br Christoffer

Answer 1

Create a temp table before the loop and don't select data, but insert data to this temp table:

create table #tmp (
 Name type,
 authorityLevel type
)

while @nrOfAuthlevels >= @myAuthLevel
begin
        set @myAuthLevel = @myAuthLevel + 1     
        insert into #tmp values(
            SELECT          Role.name, Role.authorityLevel
            FROM        [dbo].[Role]
            where ...
        )
end

Answer 2

If you want to keep that current structure, then you would need to insert into a temporary table for every step through the while loop, and outside of that return from the TEMP table.

That said, why not just use a WHERE clause to get the expected return results:

SELECT Role.Name, Role.AuthorityLevel
    FROM dbo.Role
    WHERE Role.AuthorityLevel < @MyAuthLevel
    ORDER BY Role.AuthorityLevel