ORDER BY clause in Stored Procedure

Results 1 to 3 of 3

Thread: ORDER BY clause in Stored Procedure

  1. #1
    droy Guest

    Default ORDER BY clause in Stored Procedure

    I&#039;m trying to create the following stored procedure in SQL 6.5 and get an error message regarding the ORDER BY clause. Is it possible to create a stored procedure that contains an ORDER BY clause with a parameter passed from ASP? (Note: It doesn&#039;t matter to me if I need to pass a field ordinal number or the field name for the ORDER BY clause)<BR><BR>CREATE PROCEDURE sp_GetContactList( <BR>@intListId tinyint, <BR>@strSortCol varchar(12) <BR>) <BR>AS <BR><BR>SELECT personid, <BR>listid, <BR>companyname, <BR>lastname, <BR>firstname, <BR>phonenumber, <BR>faxnumber, <BR>email, <BR>activestatus, <BR>deleteflag <BR>FROM tblContactList <BR>WHERE deleteflag=0 <BR>AND listid=@iListId <BR>ORDER BY @strSortCol <BR>GO <BR><BR>When trying to create the procedure, I get the following error message in SQL Server:<BR><BR>&#039;The select list item identified by the order by number &#039;1&#039; contains a variable as part of the expression that identifies a column position. Variables are only allowed when ordering by an expression referencing a column name. &#039;

  2. #2
    patricklocke Guest

    Default Sure. Use CASE. That's the only way it's

    /*<BR>** Displays a list of Errors<BR> called by:<BR> EXECUTE sp_list_errors<BR> @Project_Id = 1,<BR> @SortColumn = &#039;Error_Id&#039;; <BR><BR>*/<BR>CREATE PROCEDURE sp_list_errors<BR>@Project_Id Int,<BR>@SortColumn varchar(100)<BR><BR>AS <BR>SELECT * From vu_list_errors<BR>WHERE Project_Id = @Project_Id<BR>ORDER BY CASE WHEN @SortColumn= &#039;Error_Id&#039; THEN Error_Id<BR>WHEN @SortColumn = &#039;Error_Catagory&#039; THEN Error_Catagory <BR>THEN @SortColumn = &#039;Error_Status&#039; THEN Error_Status <BR>WHEN @SortColumn = &#039;Date_Created&#039; THEN Date_Created <BR>WHEN @SortColumn = &#039;Date_Modified&#039; THEN Date_Modified <BR>WHEN @SortColumn = &#039;Assigned_To&#039; THEN Assigned_To<BR>WHEN @SortColumn = &#039;Created_By&#039; THEN Created_By<BR> <BR>END DESC<BR>GO

  3. #3
    Join Date
    Dec 1969
    Los Angeles, CA

    Default to make it slightly easier

    assuming everything else works<BR><BR>declare @Order<BR>select @Order = &#039;&#039;<BR>if @SortColumn is not null <BR>select @Order = " Order by " + @SortColumn <BR><BR>then just concatinate this to your query<BR><BR>so if there is no value passed as in no order by required then there will be nothing otherwise the column name will be ther along with the ORDER BY

Posting Permissions

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