如何正确修复subtotal函数并将其嵌入存储过程？

我编写了subtotal函数并将其嵌入showdata存储过程中，但运行结果不符合预期。相关数据库表定义、函数及存储过程代码如下：

CREATE TABLE product(
    codeproduct CHAR(5),
    nameproduct VARCHAR(50),
    unit VARCHAR(20),
    price INT
);

CREATE TABLE producer(
    codeproducer CHAR(5),
    nameproducer VARCHAR(50),
    address VARCHAR(20),
    city VARCHAR(20),
    province VARCHAR(20)
);

CREATE TABLE transaction(
    codeproduct CHAR(5),
    codeproducer CHAR(5),
    qty INT
);

CREATE FUNCTION [dbo].[subtotal](@codeproduct AS CHAR(5)) 
RETURNS INT
AS
BEGIN
    DECLARE @subtotal INT
    SELECT @subtotal = product.price * transaction.qty FROM transaction inner join product 
    ON product.codeproduct=transaction.codeproduct
    WHERE product.codeproduct=@codeproduct
    RETURN @subtotal
END
GO

CREATE PROCEDURE showdata
AS
BEGIN
    SET NOCOUNT ON;
    SELECT producer.nameproducer, product.nameproduct, product.unit, transaction.qty,
    product.price, [dbo].[subtotal](product.codeproduct) AS 'subtotal'
    FROM product join transaction on product.codeproduct=transaction.codeproduct
    join producer on producer.codeproducer=transaction.codeproducer
    ORDER BY producer.nameproducer;

END
GO

EXEC showdata

问题分析 #

你的subtotal函数存在两个核心问题：

• 当同一产品有多条交易记录时，SELECT @subtotal = ...只会保留最后一条交易的计算结果，不会累加所有交易的金额
• 函数未限定关联的交易记录范围，会把该产品所有交易的price*qty值覆盖赋值，导致结果完全不符合预期

修正方案 #

方案1：修复subtotal函数（适用于需要计算产品总交易金额的场景） #

修改函数，使用SUM()函数累加该产品所有交易的金额，并处理无交易时的NULL情况：

CREATE FUNCTION [dbo].[subtotal](@codeproduct AS CHAR(5)) 
RETURNS INT
AS
BEGIN
    DECLARE @subtotal INT
    SELECT @subtotal = SUM(product.price * transaction.qty) 
    FROM transaction 
    INNER JOIN product ON product.codeproduct=transaction.codeproduct
    WHERE product.codeproduct=@codeproduct
    RETURN ISNULL(@subtotal, 0)
END
GO

方案2：直接在存储过程中计算（更高效，推荐） #

如果需求是单条交易的小计金额，完全不需要自定义函数，直接在查询中计算即可：

CREATE PROCEDURE showdata
AS
BEGIN
    SET NOCOUNT ON;
    SELECT 
        producer.nameproducer, 
        product.nameproduct, 
        product.unit, 
        transaction.qty,
        product.price,
        product.price * transaction.qty AS 'subtotal'
    FROM product 
    JOIN transaction ON product.codeproduct=transaction.codeproduct
    JOIN producer ON producer.codeproducer=transaction.codeproducer
    ORDER BY producer.nameproducer;
END
GO

如果需求是该产品所有交易的总金额，可以用窗口函数实现：

CREATE PROCEDURE showdata
AS
BEGIN
    SET NOCOUNT ON;
    SELECT 
        producer.nameproducer, 
        product.nameproduct, 
        product.unit, 
        transaction.qty,
        product.price,
        product.price * transaction.qty AS 'single_subtotal',
        SUM(product.price * transaction.qty) OVER(PARTITION BY product.codeproduct) AS 'total_per_product'
    FROM product 
    JOIN transaction ON product.codeproduct=transaction.codeproduct
    JOIN producer ON producer.codeproducer=transaction.codeproducer
    ORDER BY producer.nameproducer;
END
GO

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