Varun Vu Varun Vu - 4 months ago 12
MySQL Question

Optional OrderBy parameter in MySql Stored Procedure

I'm being passed in an optional OrderBy value that should default to

DESC
, and if set to false, should
ORDER BY ASC
. I'm not really sure how to do this ordering based off the input. Here is what I have currently:

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

So the correct way to do this would be like this:

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