ADO will not allow use of an input parameter in ORDER BY clause within a stored procedure

Results 1 to 3 of 3

Thread: ADO will not allow use of an input parameter in ORDER BY clause within a stored procedure

  1. #1
    Chris Favro Guest

    Default ADO will not allow use of an input parameter in ORDER BY clause within a stored procedure

    I want to pass a variable to my stored procedure to programatically determine on which column to sort the output. If I use an input parameter ADO complains as follows:<BR>"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."<BR><BR>my sp looks something like this:<BR><BR>CREATE STORED PROCEDURE sp<BR>(<BR> @FieldToSort<BR>)<BR>AS<BR> SELECT * FROM Table ORDER BY @FieldToSort<BR><BR>If I can&#039t do this, what can I do?<BR>

  2. #2
    Join Date
    Dec 1969
    Posts
    2,849

    Default RE: ADO will not allow use of an input parameter in ORDER BY clause within a stored procedure

    I&#039ve had this very problem. What I did was kind of a hack. The table that I wanted to return only had three columns that I was interested in sorting, so I did:<BR><BR>IF @FieldToSort = &#039ColumnName1&#039<BR>SELECT * FROM Table ORDER BY ColumnName1<BR>ELSE IF @FieldToSort = ColumnName2&#039<BR>SELECT * FROM Table ORDER BY ColumnName2<BR>....<BR><BR>and so forth. An idea, although not the solution you probably wanted to hear. There might be a way to dynamically sort on a passed in field like you&#039re trying to do, but I&#039ve yet to find a way! :( Sorry...

  3. #3
    Chris Favro Guest

    Default RE: ADO will not allow use of an input parameter in ORDER BY clause within a stored procedure

    Thanks. Unfortunately I am dealing with a rather large table. Nevertheless, if all else fails, I may use your suggestion.

Posting Permissions

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