error in sp when using order by

CREATE PROCEDURE getC

AS
(
SELECT top 3 c FROM table1
order by c Desc

)
GO


and it gives me error "Incorrect syntax near keyword order", and secondly how will i get the result in a var like...

CREATE PROCEDURE getC

@.d char(6)
AS
(
SELECT @.d=top 1 c FROM table1
order by c Desc

)
GOYou can't use order by clause on an SP.

Paulo|||-- SQL Code Begins Here
-- exec test23
create proc test23
as

declare @.top as varchar(50)
declare @.top1 as varchar(50)
declare @.top2 as varchar(50)
declare @.top3 as varchar(50)
declare @.row_count as int

set @.row_count = 1

DECLARE top3_cursor CURSOR FOR

select top 3 author_code from lauthors
order by author_code desc

OPEN top3_cursor
FETCH NEXT FROM top3_cursor into @.top
-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
if @.row_count = 1
begin
set @.top1 = @.top
set @.top = ''
end

if @.row_count = 2
begin
set @.top2 = @.top

set @.top = ''
end

if @.row_count = 3
begin
set @.top3 = @.top

set @.top = ''
end

set @.row_count = @.row_count + 1

FETCH NEXT FROM top3_cursor into @.top
END
CLOSE top3_cursor
DEALLOCATE top3_cursor

select @.top1 as top1, @.top2 as top2, @.top3 as top3

go

-- SQL Code Ends Here

Hope this is what you are looking for

Roshmi Choudhury|||I'd just use something like:CREATE PROCEDURE getC
@.d CHAR(6) OUTPUT
AS

SELECT @.d = Max(c)
FROM table1

RETURN
GO-PatP|||the problem is DESC field..
you can't use this word.. because is a reserved word!!!
rename field or use order by [DESC]

DESC is a reserved word for DESCENDING in order by clause..
ex. select * fro mauthors order by aut_id desc
orders in descending mode..

it's ok??|||this message it was not for this thread..
sorry =)))