当动态T-SQL语句遇到除零的问题

如果一个T-SQL语句是动态构造的，例如动态地将几个公式组织在一个语句中，那么就会有一个情况，如果某个公式出现错误（典型的例子是除0错误），那么整个语句会失败。

那么，能不能够有一个什么方法，忽略某个别的公式错误，继续执行T-SQL语句呢？可惜的是，T-SQL并没有类似ISError的函数

我自己写了一个函数，代码如下，实际上我是将其解释成CASE WHEN语句

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ProcessFormula](@formula VARCHAR(4000))
RETURNS VARCHAR(4000)
AS
BEGIN
                    DECLARE @startindex INT, @index INT,@tempindex INT,@minindex INT
                    SET @startindex=0
                    SET @index=CHARINDEX('/',@formula,@startindex) --该函数是从1开始编号的
                    IF @index=0  --如果找不到除号的话，则直接返回
                        RETURN @formula
                    --如果至少找到一个除号，那么继续往下执行
                    DECLARE @tempformula VARCHAR(4000)
                    DECLARE @foundcount INT
                    SET @foundcount=0
                    SET @tempformula='CASE WHEN '
                    WHILE @index >0 --进入一个循环，来查找所有的除号
                    BEGIN
                        SET @foundcount=@foundcount+1
                        IF SUBSTRING(@formula,@index+1,1)='(' --如果除号右侧是一个括号的话
                            BEGIN
                                --注意，这里可能会有括号嵌套的情况
                                --首先找到最近的一个右括号)
                                SET @tempindex=CHARINDEX(')',@formula,@index+1)
                                DECLARE @count INT
                                DECLARE @temp VARCHAR(256)
                                SET @temp=SUBSTRING(@formula,@index+1,@tempindex-@index+1)
                                SET @count=LEN(@temp)-LEN(REPLACE(@temp,'(',''))
                                IF @count >1
                                    BEGIN
                                        WHILE @count>1
                                            BEGIN
                                                    SET @tempindex=CHARINDEX(')',@formula,@tempindex+1)
                                                    SET @count=@count-1
                                            END
                                        SET @temp=SUBSTRING(@formula,@index+1,@tempindex-@index+1)
                                    END

                            --只有一个左括号，则直接返回该字符串
                                IF @foundcount>1
                                    SET @tempformula=@tempformula + ' OR ' +@temp +' =0 '
                                ELSE
                                    SET @tempformula=@tempformula +@temp +' =0 '
                                IF @tempindex=len(@formula) --如果最后一个括号是公式的末尾，则退出循环
                                    SET @index=-1
                                ELSE
                                    SET @index=@tempindex+1                   
                            END

                        ELSE
                            BEGIN
                                SET @minindex=0 --初始化该变量为0，如果处理完四个步骤，都没有改变该值，则表示后面没有其他的运算符了
                                --如果不是一个括号，则继续向后面查找+，-，*，/这几个字符的位置
                                SET @tempindex=CHARINDEX('+',@formula,@index+1)
                                IF @tempindex>0
                                    SET @minindex=@tempindex
                                SET @tempindex=CHARINDEX('-',@formula,@index+1)
                                IF (@tempindex>0)
                                    BEGIN
                                            IF @minindex>0 AND     @tempindex<@minindex
                                                    SET @minindex=@tempindex
                                            ELSE IF @minindex=0
                                                    SET @minindex=@tempindex
                                    END
                                SET @tempindex=CHARINDEX('*',@formula,@index+1)
                                IF (@tempindex>0)
                                    BEGIN
                                            IF @minindex>0 AND     @tempindex<@minindex
                                                    SET @minindex=@tempindex
                                            ELSE IF @minindex=0
                                                    SET @minindex=@tempindex
                                    END

                                SET @tempindex=CHARINDEX('/',@formula,@index+1)
                                IF (@tempindex>0)
                                    BEGIN
                                            IF @minindex>0 AND     @tempindex<@minindex
                                                    SET @minindex=@tempindex
                                            ELSE IF @minindex=0
                                                    SET @minindex=@tempindex
                                    END

                                IF @minindex=0
                                    BEGIN
                                            DECLARE @formulalength INT
                                            SET @formulalength=LEN(@formula)
                                            IF @foundcount>1
                                                SET @tempformula=@tempformula + ' OR ' +SUBSTRING(@formula,@index+1,@formulalength-@index+1) +' =0 '
                                            ELSE
                                                SET @tempformula=@tempformula +SUBSTRING(@formula,@index+1,@formulalength-@index+1) +' =0 '
                                        SET @index=-1 --将这个变量设置小于0，退出循环
                                    END
                                ELSE
                                    BEGIN
                                            IF @foundcount>1
                                                SET @tempformula=@tempformula + ' OR ' +SUBSTRING(@formula,@index+1,@minindex-@index-1) +' =0 '
                                            ELSE
                                                SET @tempformula=@tempformula +SUBSTRING(@formula,@index+1,@minindex-@index-1) +' =0 '
                                            SET @index=@tempindex
                                    END
                            END
                    END

                    RETURN @tempformula +'  THEN 0 ELSE ' + @formula +' END'
END

 

测试的结果如下图