SQL存储过程sum和count为0时返回NULL不触发除零错误如何修改

• 当position表无数据时，COUNT(*)对空集返回0，但SUM(base_salary)对空集返回NULL而非0
• SQL Server 中所有包含NULL的算术运算结果都为NULL，NULL / 0不会触发运行时错误，只会直接返回NULL，因此不会进入CATCH块
• 第二个存储过程的运算为1 / 0，两个操作数均为非NULL数值，会触发标准除零错误，因此正常进入CATCH块

如果要让空表场景触发和第二个存储过程一致的除零错误捕获，只需要将SUM聚合的NULL结果转为0，此时空表时运算变为0 / 0，会触发原生除零错误：

create procedure avgSalaryByPositions
as
begin
begin try  
    declare @sum int
    select @sum=ISNULL(sum(base_salary), 0) from position
    declare @count int
    select @count=count(*) from position
    select @sum/@count
end try
begin catch
    select
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_MESSAGE() AS ErrorMessage;  
end catch
end

如果需要更可控的错误触发逻辑，也可以主动判断行数为0时抛出对应错误：

create procedure avgSalaryByPositions
as
begin
begin try  
    declare @sum int
    select @sum=sum(base_salary) from position
    declare @count int
    select @count=count(*) from position
    -- 主动判断行数为0时抛错
    if @count = 0
    begin
        THROW 8134, N'遇到以零作除数错误。', 1;
    end
    select @sum/@count
end try
begin catch
    select
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_MESSAGE() AS ErrorMessage;  
end catch
end

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