Passing ORDER BY Criteria to a Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Passing ORDER BY Criteria to a Stored Procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-09-25 : 01:49:02

Paul writes "How can I pass ORDER BY criteria to a Stored Procedure.
The following oversimplification produces a syntax error.

CREATE PROCEDURE MyProcedure
@strOrderBy varchar(8000) = NULL
AS
SELECT *
FROM MyTable
ORDER BY @strOrderBy

Error: The SELECT item identified by the ORDER BY
number 1 contains a variable as part of the expression
identifying a column position. Variables are only allowed
when ordering by an expression referencing a column
name."

petem
Starting Member

44 Posts

Posted - 2005-07-07 : 05:10:29

Hey,

Im having some problems with this (after having a look at the article mentioned above)

My code is as such:


    SELECT DISTINCT r.userID, r.firstname, r.surname, r.jobTitle, l.Suppliername1, r.dateAdded, r.deleted, getUsername(r.Addedby) AS AddedBy ,getUsername(r.deletedby) AS DeletedBy 
  FROM TLRegistrations r
    INNER JOIN Users u ON r.userID = u.UserID
      INNER JOIN l ON l.recordID = u.locationID
        INNER JOIN Companies c ON c.companyID = l.CompanyID
WHERE r.Deleted IS NULL
  ORDER BY CASE WHEN @orderBy = 'surname, r.firstname, l.suppliername1' THEN r.surname
                WHEN @orderBy = 'l.suppliername1, surname, r.firstname' THEN l.suppliername1
                END

I'm getting the error:

'ORDER BY items must appear in the select list if SELECT DISTINCT is specified.'

and I also get an error if trying to order by more than one column

ie: WHEN @orderBy = 'surname, r.firstname, l.suppliername1' THEN r.surname, r.firstname

any ideas?

Any help much appreciated, ta

Pete

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-07-07 : 08:09:45

To have dynamic sql part cached use exec sp_executeSql.

shijobaby
Starting Member

44 Posts

Posted - 2009-08-19 : 05:42:52

Hi

The reasons and ways to avoid this error have discussed in this

site with good examples. By making small changes in the query

http://sqlerror104.blogspot.com/2009/08/order-by-items-must-appear-in-select_19.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources