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

```