Order Function Returned Table

[shaq]

Hi, here is the problem

/* Order Function Returned Table *
Need to minimaly change a function(GetBigTableData), 
so that it returns the same results but ordered.
PS: Working in SQL Server 2005
*/
/* Assuming that: BigTable, @ReturnTable e @AuxTable have the same schema */
create function GetBigTableData (@All bit=0)
returns @ReturnTable table(col int)
as
begin
declare @AuxTable Table(col int)
/*-----UNCHANGABLE CODE--------------------------------------*/
    insert into @AuxTable
        select * from BigTable where BigTable.col < 5
    if @All=1
    begin
        insert into @AuxTable
            select * from BigTable where BigTable.col >= 5
    end
/*----------------------------------------------------------*/            
    insert into @ReturnTable
        select * from @AuxTable
            order by @AuxTable.col        --Error: Must declare the scalar variable "@AuxTable".
            --order by @ReturnTable.col    --Error: Must declare the scalar variable "@ReturnTable".
    return
end
go

Any Solutions?

Many Thanks,

Rui Miranda

[fulvio]

Dear Shap,

Remove the variable reference order by @AuxTable.col by order by col.

/* Order Function Returned Table *
Need to minimaly change a function(GetBigTableData),
so that it returns the same results but ordered.
PS: Working in SQL Server 2005
*/
/* Assuming that: BigTable, @ReturnTable e @AuxTable have the same schema */
create function GetBigTableData (@All bit=0)
returns @ReturnTable table(col int)
as
begin
declare @AuxTable Table(col int)
/*-----UNCHANGABLE CODE--------------------------------------*/
    insert into @AuxTable
        select * from BigTable where BigTable.col < 5
    if @All=1
    begin
        insert into @AuxTable
            select * from BigTable where BigTable.col >= 5
    end
/*----------------------------------------------------------*/            
    insert into @ReturnTable
        select * from @AuxTable
            order by col        --Error: Must declare the scalar variable "@AuxTable".
            --order by @ReturnTable.col    --Error: Must declare the scalar variable "@ReturnTable".
    return
end
go