请求协助创建SQL视图/存储过程/函数 实现按分类账查Dr/Cr数据

Hey there! Let's work through this problem together. First, since you didn't share the exact schema of your JvDetails table, I'll start with a common, practical structure for journal voucher details—this should align with what you're using:

CREATE TABLE JvDetails (
    JVNumber VARCHAR(20) NOT NULL, -- Unique journal voucher number
    LedgerName VARCHAR(50) NOT NULL, -- Name of the ledger account (e.g., Cash A/c, Sales A/c)
    DebitAmount DECIMAL(18,2) DEFAULT 0.00, -- Debit amount for the ledger
    CreditAmount DECIMAL(18,2) DEFAULT 0.00, -- Credit amount for the ledger
    JVDate DATE NOT NULL, -- Date of the journal voucher
    PRIMARY KEY (JVNumber, LedgerName) -- Composite key to avoid duplicate entries per voucher+ledger
);

Below are three solutions tailored to different needs: a view for quick aggregated queries, a stored procedure for flexible parameterized searches, and a function for single-ledger balance checks.

1. Create a View for Ledger-Wise Aggregated Data #

A view is perfect for repeated, straightforward queries to get total debits, credits, and balances per ledger. It wraps up the aggregation logic so you don't have to rewrite it every time:

CREATE VIEW vw_LedgerSummary AS
SELECT
    LedgerName,
    SUM(DebitAmount) AS TotalDebit,
    SUM(CreditAmount) AS TotalCredit,
    SUM(DebitAmount) - SUM(CreditAmount) AS LedgerBalance -- Optional: Calculate net balance
FROM JvDetails
GROUP BY LedgerName;

How to Use the View: #

-- Get data for Cash A/c alone
SELECT * FROM vw_LedgerSummary WHERE LedgerName = 'Cash A/c';

-- Get data for multiple ledgers at once
SELECT * FROM vw_LedgerSummary 
WHERE LedgerName IN ('Cash A/c', 'Sales A/c', 'Salery1');

2. Create a Stored Procedure for Flexible Ledger Queries #

If you need a reusable way to query one or multiple ledgers (with the option to adjust logic later), a stored procedure with parameters is ideal. This one accepts a comma-separated list of ledger names:

CREATE PROCEDURE sp_GetLedgerDetails
    @LedgerNames VARCHAR(MAX) -- Example input: 'Cash A/c,Sales A/c,Salery1'
AS
BEGIN
    SET NOCOUNT ON; -- Suppress extra row count messages

    -- Convert the comma-separated string into a temporary table of ledger names
    CREATE TABLE #TempLedgers (LedgerName VARCHAR(50));
    INSERT INTO #TempLedgers
    SELECT value FROM STRING_SPLIT(@LedgerNames, ',');

    -- Fetch aggregated data for the specified ledgers
    SELECT
        j.LedgerName,
        SUM(j.DebitAmount) AS TotalDebit,
        SUM(j.CreditAmount) AS TotalCredit,
        SUM(j.DebitAmount) - SUM(j.CreditAmount) AS LedgerBalance
    FROM JvDetails j
    INNER JOIN #TempLedgers t ON j.LedgerName = t.LedgerName
    GROUP BY j.LedgerName;

    -- Clean up the temporary table
    DROP TABLE #TempLedgers;
END;

How to Execute the Procedure: #

-- Query a single ledger
EXEC sp_GetLedgerDetails 'Cash A/c';

-- Query multiple ledgers
EXEC sp_GetLedgerDetails 'Cash A/c,Sales A/c,Salery1';

Note: STRING_SPLIT works in SQL Server 2016+. If you're using an older version, you'll need a custom string-split function—just let me know if you need help with that.

3. Create a Scalar Function for Single Ledger Balance Checks #

If you only need to quickly fetch the net balance of a single ledger, a scalar function is lightweight and easy to use in other queries:

CREATE FUNCTION fn_GetLedgerBalance
(
    @LedgerName VARCHAR(50)
)
RETURNS DECIMAL(18,2)
AS
BEGIN
    DECLARE @NetBalance DECIMAL(18,2);

    SELECT @NetBalance = SUM(DebitAmount) - SUM(CreditAmount)
    FROM JvDetails
    WHERE LedgerName = @LedgerName;

    RETURN ISNULL(@NetBalance, 0.00); -- Return 0 if the ledger has no entries
END;

How to Use the Function: #

-- Get balance for Cash A/c
SELECT dbo.fn_GetLedgerBalance('Cash A/c') AS CashAccountBalance;

-- Get balance for Salery1
SELECT dbo.fn_GetLedgerBalance('Salery1') AS Salery1Balance;

Quick Tips: #

• If your JvDetails table has extra fields (like narration, reference numbers), you can easily add them to the view or procedure.
• All these objects are persistent—once created, you can use them anytime without redefining the logic.
• For detailed line-item queries instead of aggregates, just remove the SUM() and GROUP BY clauses in the view/procedure.

内容的提问来源于stack exchange，提问作者Benedict