<p>I am trying to do something like this:</p>
<pre><code>while @nrOfAuthlevels &gt;= @myAuthLevel
begin
    set @myAuthLevel = @myAuthLevel + 1 
    SELECT      Role.name, Role.authorityLevel
    FROM        [dbo].[Role]
    ORDER BY Role.authorityLevel
end
</code></pre>
<p>The result of this stored procedure shall be a table with all Role.authorityLevel below my own. But this generates several tables.</p>

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
```
```