Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Posts
    52

    Question Unanswered: error in sp when using order by

    Code:
    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...

    Code:
    CREATE PROCEDURE getC
    
    @d char(6)
     AS
    (
    SELECT @d=top 1 c FROM table1 
    order by c Desc
    
    )
    GO

  2. #2
    Join Date
    Feb 2003
    Location
    @ home
    Posts
    163
    You can't use order by clause on an SP.

    Paulo

  3. #3
    Join Date
    Feb 2002
    Location
    Assam, India
    Posts
    55
    -- 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

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd just use something like:
    Code:
    CREATE PROCEDURE getC
       @d CHAR(6) OUTPUT
    AS
    
    SELECT @d = Max(c)
       FROM table1 
    
    RETURN
    GO
    -PatP

  5. #5
    Join Date
    Aug 2003
    Posts
    23

    Talking noooooooooooo!!!

    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??
    Alx_81 =)

  6. #6
    Join Date
    Aug 2003
    Posts
    23

    sorry...

    this message it was not for this thread..
    sorry =)))
    Alx_81 =)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •