SQL 存储过程将参数传递给“order by" [英] SQL stored procedure passing parameter into "order by"

问题描述

使用 Microsoft SQL 服务器管理器 2008.

Using Microsoft SQL server manager 2008.

制作一个最终"选择帕累托列表中的前 10 个的存储过程.但我也想再次运行它以找到底部 10 个.

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.

现在，我不是再次复制查询，而是尝试查看是否有办法将参数传递到查询中，从而将顺序从 asc 更改为 desc.

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

推荐答案

只是有点傻:

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

您根本不需要严格将第二个排序条件放在CASE 表达式中(*)，并且如果Pareto 是数字，你可以决定只做 CASE WHEN @orderby='ASC' THEN 1 ELSE -1 END * Pareto

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

(*) 只有当第一个排序条件认为两行相等时，第二个排序条件才有效.这要么是当两行具有相同的 Pareto 值(因此反向排序也会认为它们相等)，或者因为第一个 CASE 表达式返回 NULLs(所以 @orderby 不是 'ASC'，所以我们要执行 DESC 排序.

(*) 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 NULLs (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

这将按从上到下的顺序为您提供前 10 名和后 10 名.但如果结果总数少于 20 个，则不会出现重复的结果，这与您当前的计划不同.

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.

这篇关于SQL 存储过程将参数传递给“order by"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！