在SQL Server 2014中遇到被零除错误

SELECT 
    CASE 
        WHEN divisor <> 0 THEN dividend / divisor 
        ELSE NULL -- 或者你可以选择一个默认值
    END AS result
FROM your_table;

SELECT 
    dividend / NULLIF(divisor, 0) AS result
FROM your_table;

BEGIN TRY
    SELECT dividend / divisor AS result
    FROM your_table;
END TRY
BEGIN CATCH
    -- 处理错误
    SELECT ERROR_NUMBER() AS ErrorNumber,
           ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

UPDATE your_table
SET divisor = CASE WHEN divisor = 0 THEN 1 ELSE divisor END;

-- 创建示例表
CREATE TABLE Sales (
    ID INT PRIMARY KEY,
    Revenue DECIMAL(10, 2),
    Quantity INT
);

-- 插入示例数据
INSERT INTO Sales (ID, Revenue, Quantity) VALUES
(1, 100.00, 5),
(2, 200.00, 0), -- 这里故意设置一个除数为零的情况
(3, 150.00, 3);

-- 使用条件判断避免被零除错误
SELECT 
    ID,
    CASE 
        WHEN Quantity <> 0 THEN Revenue / Quantity 
        ELSE NULL 
    END AS AveragePrice
FROM Sales;

-- 使用NULLIF函数避免被零除错误
SELECT 
    ID,
    Revenue / NULLIF(Quantity, 0) AS AveragePrice
FROM Sales;

-- 使用TRY...CATCH块捕获和处理被零除错误
BEGIN TRY
    SELECT ID, Revenue / Quantity AS AveragePrice
    FROM Sales;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,
           ERROR_MESSAGE() AS ErrorMessage;
END CATCH;