Order Function Returned Table

• RMiranda

  SSC Rookie

  Points: 26

  More actions

  November 24, 2011 at 2:59 pm

  #252548

  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

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 24, 2011 at 11:18 pm

  #1412847

  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