asked on

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

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Nitin Sontakke

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Peter Chan

Hi Woodwyn,
Is this thread resolved?

woodwyn

ASKER

I am trying the Dynamic SQL approach and getting this error. I believe the SP saved with the new code the first time, but when I made addititional modifications to the SP I get this error message when I try to save the SP:
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'sys.sp_executesql'. The stored procedure will still be created.

And I get this error when I try to run it
Could not find stored procedure 'sys.sp_executesql'.

Here's the exact syntax of the code I added to the SP:
            DECLARE @SQL CHAR(1000)
            SELECT @SQL = '
            SELECT Customer,
                  TopMenu = RTRIM((SELECT LeftMenuHeader FROM WebSubDomainsMenus WHERE WebSubDomainsMenus.keyWebSubDomainsMenus=#MSR1.keyTopMenu)),
                  Category, Asset,OrderNumber,ShowName,StartDate,EndDate,UserName,Qty,Price,
                  AssetUsageTotal = (SELECT ItemTotal FROM #MSR2 WHERE #MSR1.keyWebItemAttributes=#MSR2.keyWebItemAttributes)
            FROM #MSR1
            ORDER BY ' + @cSortByFinal
            EXEC sys.sp_executesql @SQL

Here's the result if I run SELECT @SQL:
SELECT Customer, TopMenu = RTRIM((SELECT LeftMenuHeader FROM WebSubDomainsMenus WHERE WebSubDomainsMenus.keyWebSubDomainsMenus=#MSR1.keyTopMenu)), Category, Asset,OrderNumber,ShowName,StartDate,EndDate,UserName,Qty,Price, AssetUsageTotal = (SELECT ItemTotal FROM #MSR2 WHERE #MSR1.keyWebItemAttributes=#MSR2.keyWebItemAttributes) FROM #MSR1 ORDER BY ShowName ASC

woodwyn

ASKER

I was able to solve that last problem by replacing

EXEC sys.sp_executesql @SQL

With this simpler call

EXEC(@SQL)

In case anyone in the future finds this useful or if you have a better suggestion... There are 3 order by parameters accepted by this procedure (ShowName ASC for example). Because of the way the website is designed that calls the procedure I was concerned that any or all of them may be empty, so I created this block to combine them into a string with commas where necessary. Then I was able to simply add the string to the SQL call and not be concerned by what order or if there is a ASC or DESC in the parameters.

DECLARE @cSortByFinal AS CHAR(100)
-- Set the default sort by if none were passed to this SP
IF @cSortBy1='' AND @cSortBy2='' AND @cSortBy3=''
      BEGIN
            SET @cSortByFinal='Order_By, Asset'
      END
ELSE
-- Otherwise, combine the parameters into one string
      BEGIN
            IF @cSortBy1=''
                  BEGIN
                        IF @cSortBy2=''
                              BEGIN
                                    SET @cSortByFinal=RTRIM(@cSortBy3)
                              END
                        ELSE
                              IF @cSortBy3=''
                                    BEGIN
                                          SET @cSortByFinal=RTRIM(@cSortBy2)
                                    END
                              ELSE
                                    BEGIN
                                          SET @cSortByFinal=RTRIM(@cSortBy2)+','+RTRIM(@cSortBy3)
                                    END
                  END
            ELSE
                  BEGIN
                        IF @cSortBy2=''
                              BEGIN
                              IF @cSortBy3=''
                                    BEGIN
                                          SET @cSortByFinal=RTRIM(@cSortBy1)
                                    END
                              ELSE
                                    BEGIN
                                          SET @cSortByFinal=RTRIM(@cSortBy1)+','+RTRIM(@cSortBy3)
                                    END
                              END
                        ELSE
                              IF @cSortBy3=''
                                    BEGIN
                                          SET @cSortByFinal=RTRIM(@cSortBy1)+','+RTRIM(@cSortBy2)
                                    END
                              ELSE
                                    BEGIN
                                          SET @cSortByFinal=RTRIM(@cSortBy1)+','+RTRIM(@cSortBy2)+','+RTRIM(@cSortBy3)
                                    END
                  END
      END

SOLUTION

Nitin Sontakke

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

woodwyn

ASKER

Thanks guys!