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]
SELECT Peroid1.Pareto FROM dbo.Peroid1
GROUP by Pareto ORDER by Pareto @orderby
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
'ASC', so we want to perform the
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.