Geek asks and answers

Error passing SQL stored procedure to "order by"

Using Microsoft SQL Server 2008 Manager

Create a stored procedure that "ultimately" displays the top 10 on the Pareto list. But I would also like to run this again to find the bottom 10.

Now, instead of replicating the request again, I'm trying to see if there is a way to pass the parameter to the request, which will change the order from asc to desc.

Is there a way to do this that will save me from code replication?

CREATE PROCEDURE [dbo].[TopVRM]
@orderby varchar(255)
AS
SELECT Peroid1.Pareto FROM dbo.Peroid1
GROUP by Pareto ORDER by Pareto @orderby
+5
source share
2 answers

Just a little stupid:

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 do not need to strictly specify the second sorting condition in the expression CASE(*), and if it Paretois numeric, you can simply doCASE WHEN @orderby='ASC' THEN 1 ELSE -1 END * Pareto

(*) , . Pareto ( ), CASE NULL ( @orderby 'ASC', DESC.

:

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

10 10, . 20, , .

+7

:

 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
+1

More articles:


All Articles