Order Function Returned Table

  • 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

  • RMiranda (11/24/2011)


    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

    Give this a try:

    /* 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 axt

    order by axt.col--Error: Must declare the scalar variable "@AuxTable".

    --order by @ReturnTable.col--Error: Must declare the scalar variable "@ReturnTable".

    return

    end

    go

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply