Help needed please

I'm developing a database in sql and i'm faced with this problem in a procedure:
Server: Msg 156, Level 15, State 1, Procedure prospect, Line 35
Incorrect syntax near the keyword 'order'.
the line is:
declare reg_cs cursor local dynamic scroll for (select cod_imovel,area,preco from imoveis where (cod_reg=@tmpreg and cod_acab=@tmpacab and cod_estado=@tmpest and cod_tipo=@tmptipo and cod_tipologia=@tmptipolo and cod_local=@tmplocal and area=@area and preco!=@preco) order by preco asc))
The preco variable is of typ money in my database, and the @preco variable is of type float, i use the same like before to obtain is there is a line in the database with the parameter that i want and it doesn't have no error, if i take the order by state theres no error.
If anybody could help i thanks in advance. I can't finish my work because of this procedure, i'm stuck
If it's of any help this the full procedure:
create procedure prospect @preco float,@area float,@local varchar(30),@reg varchar(30),@tipolo varchar(20),@acab varchar(20),@estado varchar(20),@tipo varchar(30)
as
declare @tmplocal as int
declare @tmpreg as int
declare @tmptipolo as int
declare @tmpacab as int
declare @tmpest as int
declare @tmptipo as int
declare @codtmp as int
declare @precotmp as money
declare @areatmp as float
if ((select cod_local from localizacao where localizacao = @local)>0 and (select cod_regiao from regiao where nome = @reg)>0 and (select cod_tipologia from tipologia where tipologia = @tipolo)>0 and (select cod_acabamentos from acabamentos where acabamentos = @acab)>0 and (select cod_estado from estado where estado=@estado)>0 and (select cod_tipo from tipos where tipo=@tipo)>0)begin
select @tmplocal=cod_local from localizacao where localizacao = @local
select @tmpreg=cod_regiao from regiao where nome = @reg
select @tmptipolo=cod_tipologia from tipologia where tipologia = @tipolo
select @tmpacab=cod_acabamentos from acabamentos where acabamentos = @acab
select @tmpest=cod_estado from estado where estado=@estado
select @tmptipo=cod_tipo from tipo where tipo=@tipo
--Para todos os parametros
create table #prospects(cod_imov int,area float, preco money)
if exists (select cod_imovel from imoveis where cod_reg=@tmpreg and cod_acab=@tmpacab and cod_estado=@tmpest and cod_tipo=@tmptipo and cod_tipologia=@tmptipolo and cod_local=@tmplocal and preco=@preco and area=@area) begin
declare reg_cs cursor local dynamic scroll for (select cod_imovel,area,preco from imoveis where cod_reg=@tmpreg and cod_acab=@tmpacab and cod_estado=@tmpest and cod_tipo=@tmptipo and cod_tipologia=@tmptipolo and cod_local=@tmplocal and preco=@preco and area=@area)
open reg_cs
fetch next from reg_cs into @codtmp,@areatmp,@precotmp
while @@fetch_status = 0 begin
insert into #prospects(cod_imov,area,preco) values (@codtmp,@areatmp,@precotmp)
fetch next from reg_cs into @codtmp,@areatmp,@precotmp
end
close reg_cs
deallocate reg_cs
end

--Para todos menos o preço
if exists (select cod_imovel from imoveis where cod_reg=@tmpreg and cod_acab=@tmpacab and cod_estado=@tmpest and cod_tipo=@tmptipo and cod_tipologia=@tmptipolo and cod_local=@tmplocal and area=@area and preco!=@preco ) begin
declare reg_cs cursor local dynamic scroll for (select cod_imovel,area,preco from imoveis where cod_reg=@tmpreg and cod_acab=@tmpacab and cod_estado=@tmpest and cod_tipo=@tmptipo and cod_tipologia=@tmptipolo and cod_local=@tmplocal and area=@area and preco != @preco order by preco asc)
open reg_cs
fetch next from reg_cs into @codtmp,@areatmp,@precotmp
while @@fetch_status = 0 begin
insert into #prospects(cod_imov,area,preco) values (@codtmp,@areatmp,@precotmp)
fetch next from reg_cs into @codtmp,@areatmp,@precotmp
end
close reg_cs
deallocate reg_cs
end
--para todos menos o preco e a area
if exists (select cod_imovel from imoveis where cod_reg=@tmpreg and cod_acab=@tmpacab and cod_estado=@tmpest and cod_tipo=@tmptipo and cod_tipologia=@tmptipolo and cod_local=@tmplocal and area<>@area and preco!=@preco ) begin
declare reg_cs cursor local dynamic scroll for (select cod_imovel,area,preco from imoveis where cod_reg=@tmpreg and cod_acab=@tmpacab and cod_estado=@tmpest and cod_tipo=@tmptipo and cod_tipologia=@tmptipolo and cod_local=@tmplocal and area!=@area and preco != @preco order by (preco/area) asc)
open reg_cs
fetch next from reg_cs into @codtmp,@areatmp,@precotmp
while @@fetch_status = 0 begin
insert into #prospects(cod_imov,area,preco) values(@codtmp,@areatmp,@precotmp)
fetch next from reg_cs into @codtmp,@areatmp,@precotmp
end
close reg_cs
deallocate reg_cs
end
--para todos menos o preco, area e localização
if exists (select cod_imovel from imoveis where cod_reg=@tmpreg and cod_acab=@tmpacab and cod_estado=@tmpest and cod_tipo=@tmptipo and cod_tipologia=@tmptipolo and cod_local!=@tmplocal and area!=@area and preco!=@preco ) begin
declare reg_cs cursor local dynamic scroll for (select cod_imovel,area,preco from imoveis where cod_reg=@tmpreg and cod_acab=@tmpacab and cod_estado=@tmpest and cod_tipo=@tmptipo and cod_tipologia=@tmptipolo and cod_local!=@tmplocal and area!=@area and preco != @preco order by cod_local desc,preco,area)
open reg_cs
fetch next from reg_cs into @codtmp,@area,@precotmp
while @@fetch_status = 0 begin
insert into #prospects(cod_imov,area,preco) values(@codtmp,@areatmp,@precotmp)
fetch next from reg_cs into @codtmp,@areatmp,@precotmp
end
close reg_cs
deallocate reg_cs
end
end
else
print 'Campos introduzidos não estam correctos'

Ruben Monteiro

What happens to your results if you don't sort?


Try removing the asc (it is ascending by default)

S-

This may be the error.

I don't think you can order by a calculation unless that calculation has been declared previously...like this

The procedure gives an error on each order by. You can do the calculation on the order by i tried the select with values and it works just fine. I don't know why it gives the error on each of the order by, if you don't use the @preco varaible it works just perfect. I'm hang on this to finish my job

?????
S-

it order by the preco column 'preco' means price of something.