troubleshooting Question

How to use variables passed to a SQL stored procedure in the Order By clause

Avatar of woodwyn
woodwynFlag for United States of America asked on
Microsoft SQL Server
7 Comments3 Solutions140 ViewsLast Modified:
FYI, this client is running an old site still using SQL 2000 as the backend database.  We'll be upgrading them to SQL 2016 soon.  

Here are parts the stored procedure.  You can see they want to pass up to 5 variables that may be used to sort the data.  It will be common that none or less then 5 order by variables will be passed to the stored procedure.  I.e., @cSortBy1 may contain

CREATE PROCEDURE [EOLReportOrderHistory]
      @cKeyWebSubDomains UNIQUEIDENTIFIER,
      @dStartDate SMALLDATETIME,      
      @dEndDate SMALLDATETIME,
      @cWebSubDomainsMenusKeys varchar(8000),      -- Keys will be seperated by a comma
      @cOrderNumber char(8),                        -- Search dbo.WebOrders.OrderNumber for this string
      @cOrderDescription varchar(8000),            -- Search UniqueID questions for this string
      @nIsPDF TinyInt,                        -- Will this data be used for a PDF report of Excel spreadsheet
      @cSortBy1 char(200),                  -- Sort By parameter 1
      @cSortBy2 char(200),                  -- Sort By parameter 2
      @cSortBy3 char(200),                  -- Sort By parameter 3
      @cSortBy4 char(200),                  -- Sort By parameter 4
      @cSortBy5 char(200)                  -- Sort By parameter 5
AS
...
SELECT
        Customer = QUOTENAME(RTRIM(Customer),''''),
      TopMenu = RTRIM((SELECT LeftMenuHeader FROM WebSubDomainsMenus WHERE WebSubDomainsMenus.keyWebSubDomainsMenus=#MSR1.keyTopMenu)),
      OrderNumber = OrderNumber,
        OrderDescription,
        DateCreated,
        UserName,
        RTRIM(ShortDescription),
        Qty, Price, Qty*Price AS ItemTotal,
      ISNULL((SELECT OrderTotal FROM #MSR2 WHERE #MSR2.keyWebOrder=#MSR1.keyWebOrder),0) AS OrderTotal,
      ISNULL((SELECT CategoryTotal FROM #MSR3 WHERE #MSR3.keyTopMenu=#MSR1.keyTopMenu),0) AS CategoryTotal,
      GrandTotal = @nTotalSales
FROM #MSR1
ORDER BY @cSortBy1, @cSortBy2, @cSortBy3, @cSortBy4, @cSortBy5, CategorySort, OrderNumber

The stored procedure will have default Order By columns CategorySort, OrderNumber
ASKER CERTIFIED SOLUTION
Peter Chan
IT Consultant

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 3 Answers and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros