mysql - MySql 存储过程中的可选 OrderBy 参数

Question

我正在传递一个可选的 OrderBy 值，该值应默认为 DESC，如果设置为 false，则应为 ORDER BY ASC。我不太确定如何根据输入进行排序。这是我目前拥有的:

CREATE DEFINER=`app`@`%` PROCEDURE `BLAH`(
pWithdrawalTransactionId INT,
pLimit INT,
pSortDescending TINYINT
)
BEGIN
    DECLARE vLimit INT DEFAULT COALESCE(pLimit, 100);
    DECLARE vSort TINYINT DEFAULT COALESCE(pSortDescending, 1);

    SELECT
        f.WithdrawalFulfillmentId, f.PaymentStatusId, f.PaymentProcessorId, f.PaymentTypeId, r.Amount, r.RequestedAmount, r.NativeAmount, r.NativeRequestedAmount, r.RefundTransactionId, r.UpdatedDate
    FROM
        FinOps.UserWithdrawalFulfillment f
    INNER JOIN 
        FinOps.UserRefundTransaction r ON f.RefundTransactionId = r.RefundTransactionId
    WHERE
        f.WithdrawalTransactionId = pWithdrawalTransactionId
    LIMIT
        vLimit;
END

Answer 1

所以正确的做法是这样的:

CREATE DEFINER=`app`@`%` PROCEDURE `BLAH`(
pWithdrawalTransactionId INT,
pLimit INT,
pSortDescending TINYINT
)
BEGIN
    DECLARE vLimit INT DEFAULT COALESCE(pLimit, 100);
    DECLARE vSort TINYINT DEFAULT COALESCE(pSortDescending, 1);

    SELECT
        f.WithdrawalFulfillmentId, f.PaymentStatusId, f.PaymentProcessorId, f.PaymentTypeId, r.Amount, r.RequestedAmount, r.NativeAmount, r.NativeRequestedAmount, r.RefundTransactionId, r.UpdatedDate
    FROM
        FinOps.UserWithdrawalFulfillment f
    INNER JOIN 
        FinOps.UserRefundTransaction r ON f.RefundTransactionId = r.RefundTransactionId
    WHERE
        f.WithdrawalTransactionId = pWithdrawalTransactionId
    ORDER BY CASE WHEN pSortDescending = 1 THEN WithdrawalFulfillmentId * -1 ELSE WithdrawalFulfillmentId END ASC
    LIMIT
        vLimit;
END