如何在SQL Server存储过程中用变量存储表多行数据并计算税费

一、跨行列数据运算并存储到变量的实现 #

假设你的示例表名为SampleTable，结构与你给出的一致（三行三列），我们可以通过行号定位获取对应单元格的值，再代入计算逻辑：

存储过程代码 #

CREATE PROCEDURE CalculateSampleVariables
AS
BEGIN
    SET NOCOUNT ON;

    -- 声明变量存储各单元格值
    DECLARE @Cell1 DECIMAL(18,4), @Cell2 DECIMAL(18,4), @Cell3 DECIMAL(18,4);
    DECLARE @Cell4 DECIMAL(18,4), @Cell5 DECIMAL(18,4), @Cell6 DECIMAL(18,4);
    DECLARE @Cell7 DECIMAL(18,4), @Cell8 DECIMAL(18,4), @Cell9 DECIMAL(18,4);

    -- 为表格添加行号，定位每一行的数据
    WITH NumberedRows AS (
        SELECT 
            ColumnA, ColumnB, ColumnC,
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum -- 若有主键可替换ORDER BY条件
        FROM SampleTable
    )
    -- 赋值各单元格变量
    SELECT
        @Cell1 = CASE WHEN RowNum = 1 THEN ColumnA END,
        @Cell2 = CASE WHEN RowNum = 1 THEN ColumnB END,
        @Cell3 = CASE WHEN RowNum = 1 THEN ColumnC END,
        @Cell4 = CASE WHEN RowNum = 2 THEN ColumnA END,
        @Cell5 = CASE WHEN RowNum = 2 THEN ColumnB END,
        @Cell6 = CASE WHEN RowNum = 2 THEN ColumnC END,
        @Cell7 = CASE WHEN RowNum = 3 THEN ColumnA END,
        @Cell8 = CASE WHEN RowNum = 3 THEN ColumnB END,
        @Cell9 = CASE WHEN RowNum = 3 THEN ColumnC END
    FROM NumberedRows;

    -- 执行计算逻辑
    DECLARE @var1 DECIMAL(18,4), @var2 DECIMAL(18,4), @var3 DECIMAL(18,4);
    SET @var1 = (@Cell1 * @Cell8) - @Cell2 / 109;
    SET @var2 = (@Cell5 * @Cell7) - @Cell1;
    SET @var3 = (@Cell2 * @Cell3) - @Cell9 * @Cell3;

    -- 输出结果（可根据需求改为插入表或返回结果集）
    SELECT @var1 AS var1, @var2 AS var2, @var3 AS var3;
END

二、复杂税费计算的存储过程实现 #

你的税费计算逻辑完全可以通过存储过程实现，我们可以先读取税表中的区间和税率值，再根据NetSalary的范围执行对应计算：

假设税表名为TaxBracket，结构为：N_LowerRange INT, N_HigherRange VARCHAR(20), Rate DECIMAL(5,4)

存储过程代码 #

CREATE PROCEDURE CalculateTax
    @NetSalary DECIMAL(18,4), -- 输入参数：净薪资
    @TaxResult DECIMAL(18,4) OUTPUT -- 输出参数：计算后的税费
AS
BEGIN
    SET NOCOUNT ON;

    -- 声明变量存储税表中的关键值
    DECLARE @NH1 INT, @Rate2 DECIMAL(5,4), @Rate3 DECIMAL(5,4), @NH2 INT;

    -- 从税表读取对应区间的参数值
    SELECT @NH1 = N_HigherRange FROM TaxBracket WHERE N_LowerRange = 0;
    SELECT @Rate2 = Rate FROM TaxBracket WHERE N_LowerRange = 20001;
    SELECT @NH2 = N_HigherRange FROM TaxBracket WHERE N_LowerRange = 20001;
    SELECT @Rate3 = Rate FROM TaxBracket WHERE N_LowerRange = 80001;

    -- 根据NetSalary区间计算税费
    IF @NetSalary <= @NH1
        SET @TaxResult = 0;
    ELSE IF @NetSalary > 20001 AND @NetSalary <= @NH2
        SET @TaxResult = (@NetSalary * 26 - @NH1 * @Rate2) / 26;
    ELSE IF @NetSalary > 80001
        SET @TaxResult = (@NetSalary * 26 - (@NH1 * @Rate3 + (@NH2 - @NH1) * (@Rate3 - @Rate2))) / 26;
    ELSE
        SET @TaxResult = 0; -- 处理边界值（如20000-20001之间的情况）
END

使用方式 #

调用存储过程并获取结果：

DECLARE @CalculatedTax DECIMAL(18,4);
EXEC CalculateTax @NetSalary = 50000, @TaxResult = @CalculatedTax OUTPUT;
SELECT @CalculatedTax AS CalculatedTax;

关键说明 #

• 若税表的行顺序固定，可通过ROW_NUMBER()替代直接按条件查询，增强灵活性；
• 所有数值类型建议使用DECIMAL而非FLOAT，避免精度丢失；
• 可根据需求扩展存储过程，比如添加参数指定要计算的薪资列表，或直接从薪资表批量计算税费。

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