Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2002
    Posts
    20

    Unanswered: Passing "ORDER BY" parameter causes error

    Hi everyone,

    I want to be able to pass an "ORDER BY" parameter into my stored procedure but I am receiving errors when I do. For example:

    CREATE PROCEDURE GetFromTable
    (
    @SortOn varchar(20)
    )
    AS
    SELECT *
    FROM Table
    ORDER BY @SortOn
    GO

    This is the error I get: Variables are only allowed when ordering by an expression referencing a column name. Any suggestions on what I can do to make this work?

    Thanks in advance,

    Piet

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why?

    This is bad on so many levels...why not go dynamic crazy?

    Code:
    USE Northwind
    GO
    
    CREATE PROC mySproc99 
    	@TABLE_NAME sysname, @WHERE varchar(2000), @ORDERBY varchar(2000)
    AS
      BEGIN
    	DECLARE @sql varchar(8000)
    	SELECT @sql = 'SELECT * FROM ' + @TABLE_NAME 
    		+ ' WHERE ' + @WHERE
    		+ ' ORDER BY ' + @ORDERBY
    	EXEC(@sql)
      END
    GO
    
    EXEC mySproc99 'Orders','OrderId > 0','ShippedDate'
    GO
    
    DROP PROC mySproc99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Brilliant code! Henceforth, the only stored proc in my databases shall be this "one-size-fits-all" piece of code. However, I think I may make it even more universal like this:

    Code:
    USE Northwind
    GO
    
    CREATE PROC mySproc99 
    	@SQLStatement(4000)
    AS
      BEGIN
    	EXEC(@SQLStatement)
      END
    GO
    
    EXEC mySproc99 'select * from Orders where OrderId > 0 order by ShippedDate'
    GO
    
    DROP PROC mySproc99
    GO
    Much more functional and versatile, wouldn't you agree? The only stored procedure you will ever need!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2002
    Posts
    20
    blindman,

    Are you for reals?

    Henceforth, the only stored proc in my databases shall be this "one-size-fits-all" piece of code.
    Or, are you being a bit facetious?

    Thanks for the help guys. Going dynamic makes things a bit easier for sure.

    Piet

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm suspecting the BRETT was being facetious...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What meaning does "This is bad on so many levels" carry?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Piet:

    When Brett wrote "This is bad on so many levels..." he was trying to convey the idea that dynamic code is potentially a bad thing, and when used in this way (to dynamically change the order of the results), it is worse than usual.

    Dynamic code opens up all kinds of possibilities for problems like SQL Injection, etc. If the user has any control over what string gets passed into your dynamic SQL, you've basically given them the keys to the kingdom as far as what they can do.

    Both Brett and Blindman were attempting to show some of the problems that can arise from dynamic code. While there are a few uses for dynamic code (and heaven knows I seem to post a lot of it), they are really few and far between for production code. Dynamic SQL belongs in the DBA's bag of tricks, but you need to be very stingy about using it... It should only be as a last resort, and no part of the dynamic SQL should EVER be controlled by the end user.

    -PatP

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You should think about why you are asking the database to custom-order results in the first place. Ordering the results may stray into the realm of data-presentation, and would then be better left to the interface to handle.

    What interface, dare I ask, are you using?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Many years ago (8?) I was experimenting with procedure subscripts in 6.5, and actually employed this rarely used feature in one of my apps. It may sound inefficient, but it is by far much better than resorting to dynamic SQL:

    create proc myproc;1 as
    select * from pubs.dbo.authors order by 1
    go
    create proc myproc;2 as
    select * from pubs.dbo.authors order by 2
    go
    create proc myproc;3 as
    select * from pubs.dbo.authors order by 3
    go
    create proc myproc;4 as
    select * from pubs.dbo.authors order by 4
    go

    Then all your FE needs to do is to make determination which subscript to append to the call of the proc to get the results in the specific order.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Feb 2002
    Posts
    20
    Blindman,

    Good question and I think you're right that I could probably take care of the sorting in a Dataview. I am using a repeater in a web form app using c#.

    Thanks for the reminder.

    Piet

Posting Permissions

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