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: 
try:

```
 CREATE PROCEDURE [dbo].[TopVRM]
(@orderby varchar(255)
AS
IF @orderby='asc'
SELECT Peroid1.Pareto FROM dbo.Peroid1
GROUP by Pareto ORDER by Pareto asc
ELSE
SELECT Peroid1.Pareto FROM dbo.Peroid1
GROUP by Pareto ORDER by Pareto desc

```
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

```