如何合并RCB与Banking Cheques的SQL查询实现参数化查询？

I get that static views lock you into hard-coded values, so let’s merge your two view-based queries into one flexible, parameterized statement using Common Table Expressions (CTEs). This lets you plug in dynamic values for location, date, bank details, etc., without relying on fixed views.

Step-by-Step Breakdown: #

1. Define Parameters: We’ll declare variables for all the hard-coded values from your original queries. These can be adjusted on the fly or wrapped in a stored procedure for reusability.
2. RCB Summary CTE: Replicates the logic from your vwRCB_BankingSelectReceipts view, aggregating cheque amounts from the Receipt Cash Book.
3. Banking Cheques Selection CTE: Replaces your vwRCB_BankingChequesSelect view, calculating the Selected flag by checking for matching records between the two tables.
4. Join the CTEs: Perform the right outer join exactly like you did with the views, then apply filtering and sorting.

Full Parameterized SQL Statement: #

-- Declare parameter variables (adjust data types if your schema uses different ones)
DECLARE @LocationCode VARCHAR(2) = '01',
        @ReceiptDate DATE = CONVERT(date, '20200918', 112),
        @DepQBank VARCHAR(4) = '7010',
        @DepQBranch VARCHAR(3) = '660',
        @DepQAccountNo VARCHAR(12) = '0000000502';

-- CTE 1: Aggregate RCB data (matches vwRCB_BankingSelectReceipts)
WITH RCB_Summary AS (
    SELECT 
        RCBBankCode,
        RCBBranchCode,
        RCBChequeDate,
        RCBChequeNo,
        SUM(RCBOrginalAmount) AS ChqAmount,
        RCBLocationCode,
        RCBReceiptDate
    FROM dbo.[Receipt Cash Book]
    WHERE 
        RCBLocationCode = @LocationCode
        AND RCBReceiptDate = @ReceiptDate
        AND RCBCancelTag = 0
        AND RCBPaymentCode <> 'CASH'
    GROUP BY 
        RCBBankCode,
        RCBBranchCode,
        RCBChequeDate,
        RCBChequeNo,
        RCBLocationCode,
        RCBReceiptDate
),
-- CTE 2: Calculate Selected flag for Banking Cheques (matches vwRCB_BankingChequesSelect)
BC_Selection AS (
    SELECT 
        DepQChqBank,
        DepQChqBranch,
        DepQChqDate,
        DepQChqNo,
        DepQRDate,
        DepQRLocation,
        CASE 
            WHEN EXISTS (
                SELECT DISTINCT 
                    bc_inner.DepQDate,
                    bc_inner.DepQBank,
                    bc_inner.DepQBranch,
                    bc_inner.DepQAccountNo,
                    bc_inner.DepQRLocation,
                    bc_inner.DepQRDate,
                    bc_inner.DepQChqBank,
                    bc_inner.DepQChqBranch,
                    bc_inner.DepQChqDate,
                    bc_inner.DepQChqNo
                FROM [Banking Cheques] bc_inner
                INNER JOIN [Receipt Cash Book] rcb_inner
                    ON bc_inner.DepQRLocation = rcb_inner.RCBLocationCode
                    AND bc_inner.DepQRDate = rcb_inner.RCBReceiptDate
                    AND bc_inner.DepQChqBank = rcb_inner.RCBBankCode
                    AND bc_inner.DepQChqBranch = rcb_inner.RCBBranchCode
                    AND bc_inner.DepQChqDate = rcb_inner.RCBChequeDate
                    AND bc_inner.DepQChqNo = rcb_inner.RCBChequeNo
                WHERE 
                    bc_inner.DepQBank = @DepQBank
                    AND bc_inner.DepQBranch = @DepQBranch
                    AND bc_inner.DepQAccountNo = @DepQAccountNo
                    AND bc_inner.DepQRLocation = @LocationCode
                    AND bc_inner.DepQRDate = @ReceiptDate
            ) THEN 1 
            ELSE 0 
        END AS Selected
    FROM dbo.[Banking Cheques]
)
-- Final join to combine results (matches your original view join logic)
SELECT 
    rcb.RCBBankCode,
    rcb.RCBBranchCode,
    rcb.RCBChequeDate,
    rcb.RCBChequeNo,
    rcb.ChqAmount,
    ISNULL(bc.Selected, 0) AS Selected
FROM BC_Selection bc
RIGHT OUTER JOIN RCB_Summary rcb
    ON bc.DepQRDate = rcb.RCBReceiptDate
    AND bc.DepQRLocation = rcb.RCBLocationCode
    AND bc.DepQChqBank = rcb.RCBBankCode
    AND bc.DepQChqBranch = rcb.RCBBranchCode
    AND bc.DepQChqDate = rcb.RCBChequeDate
    AND bc.DepQChqNo = rcb.RCBChequeNo
WHERE 
    rcb.RCBLocationCode = @LocationCode
    AND rcb.RCBReceiptDate = @ReceiptDate
ORDER BY 
    rcb.RCBBankCode,
    rcb.RCBBranchCode;

Key Notes: #

• Parameter Flexibility: Update the variable values at the top to use different dates, locations, or bank accounts. For repeated use, wrap this query in a stored procedure to accept parameters directly from your app.
• Preserved Logic: This query maintains exactly the same filtering, aggregation, and join behavior as your original view setup—just without the static view limitations.
• Readability: CTEs keep the code organized, making it easy to tweak individual parts (like the aggregation or selection logic) without breaking the whole query.

内容的提问来源于stack exchange，提问作者Mark S Fernando