Question: Using Microsoft SQL server manager 2008.

Making a stored procedure that will "eventually" select the top 10 on the Pareto list. But I also would like to run this again to find the bottom 10.

Now, instead of replicating the query all over again, I'm trying to see if there's a way to pass a parameter into the query that will change the order by from asc to desc.

Is there any way to do this that will save me from replicating code?

```
CREATE PROCEDURE [dbo].[TopVRM]
@orderby varchar(255)
AS
SELECT Peroid1.Pareto FROM dbo.Peroid1
GROUP by Pareto ORDER by Pareto @orderby

```

Answer: 
Only by being slightly silly:

```
CREATE PROCEDURE [dbo].[TopVRM]
@orderby varchar(255)
AS
SELECT Peroid1.Pareto FROM dbo.Peroid1
GROUP by Pareto
ORDER by CASE WHEN @orderby='ASC' THEN Pareto END,
         CASE WHEN @orderby='DESC' THEN Pareto END DESC

```

You don't *strictly* need to put the second sort condition in a `CASE` expression at all(\*), and if `Pareto` is numeric, you may decide to just do `CASE WHEN @orderby='ASC' THEN 1 ELSE -1 END * Pareto`

(\*) The second sort condition only has an effect when the first sort condition considers two rows to be equal. This is either when both rows have the same *Pareto* value (so the reverse sort would also consider them equal), of because the first `CASE` expression is returning `NULL`s (so `@orderby` isn't `'ASC'`, so we want to perform the `DESC` sort.

---

You might also want to consider retrieving both result sets in one go, rather than doing two calls:

```
CREATE PROCEDURE [dbo].[TopVRM]
@orderby varchar(255)
AS

SELECT * FROM (
    SELECT
       *,
       ROW_NUMBER() OVER (ORDER BY Pareto) as rn1,
       ROW_NUMBER() OVER (ORDER BY Pareto DESC) as rn2
    FROM (
        SELECT Peroid1.Pareto
        FROM dbo.Peroid1
        GROUP by Pareto
    ) t
) t2
WHERE rn1 between 1 and 10 or rn2 between 1 and 10
ORDER BY rn1

```

This will give you the top 10 and the bottom 10, in order from top to bottom. But if there are less than 20 results in total, you won't get duplicates, unlike your current plan.
I know it's pretty old, but just wanted to share our solution here, hoping to help someone :)

After some performance tests for several candidate solutions (some of them posted in this thread), we realized **you must be really careful** with your implementation: your SP **performance could be hugely impacted**, specially when you combine it with **pagination** problem.

The best solution we found was to save raw results, ie. just applying filters, in a **temporal table** (`#RawResult` in the example), adding afterwards the `ORDER BY` and `OFFSET` clauses for pagination. Maybe it's not the prettiest solution (as you are force to copy & paste a clause twice for each column you want to sort), but we were unable to find other better in terms of performance.

Here it goes:

```sql
CREATE PROCEDURE [dbo].[MySP]
    -- Here goes your procedure arguments to filter results
    @Page INT = 1,                  -- Resulting page for pagination, starting in 1
    @Limit INT = 100,               -- Result page size
    @OrderBy NVARCHAR(MAX) = NULL,  -- OrderBy column
    @OrderByAsc BIT = 1             -- OrderBy direction (ASC/DESC)
AS

-- Here goes your SP logic (if any)

SELECT 
    * -- Here goes your resulting columns
INTO
    #RawResult
FROM 
    ...
-- Here goes your query data source([FROM], [WHERE], [GROUP BY], etc)
-- NO [ORDER BY] / [TOP] / [FETCH HERE]!!!!

--From here, ORDER BY columns must be copy&pasted twice: ASC and DESC orders for each colum
IF (@OrderByAsc = 1 AND @OrderBy = 'Column1')
    SELECT * FROM #RawResult ORDER BY Column1 ASC OFFSET @Limit * (@Page - 1) ROWS FETCH NEXT @Limit ROWS ONLY
ELSE
IF (@OrderByAsc = 0 AND @OrderBy = 'Column1')
    SELECT * FROM #RawResult ORDER BY Column1 DESC OFFSET @Limit * (@Page - 1) ROWS FETCH NEXT @Limit ROWS ONLY
ELSE
IF (@OrderByAsc = 1 AND @OrderBy = 'Column2')
    SELECT * FROM #RawResult ORDER BY Column2 ASC OFFSET @Limit * (@Page - 1) ROWS FETCH NEXT @Limit ROWS ONLY
ELSE
IF (@OrderByAsc = 0 AND @OrderBy = 'Column2')
    SELECT * FROM #RawResult ORDER BY Column2 DESC OFFSET @Limit * (@Page - 1) ROWS FETCH NEXT @Limit ROWS ONLY
ELSE
    ...
ELSE --Default order, first column ASC
    SELECT * FROM #RawResult ORDER BY 1 ASC OFFSET @Limit * (@Page - 1) ROWS FETCH NEXT @Limit ROWS ONLY

```