November 24, 2011 at 2:59 pm
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
November 24, 2011 at 11:18 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy