CODEWITHSUNDEEP
sqlsql-servert-sql
Antogram
Antogram

Reputation: 17

SQL Division with zero must not be done and must not affect other calculations

I am trying to create a table valued function in SQL Server that calculates any mathematical operation between 2 numbers. Division with zero must return a warning and the computation must not be done. I noticed that by system there is a message "Divide by zero error encountered" but it affects the other calculations (addition, subtraction, multiplication).

So here is my code with the version of NULL as result of division with error so it does not mess up.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER FUNCTION get_math
(
    @p1 decimal, 
    @p2 decimal
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT @p1 + @p2 AS 'add',
     @p1 - @p2 AS 'substract',
     @p1 * @p2 AS 'multiply',
    
     CASE 
        WHEN @p2 = 0
        THEN null
        ELSE  CONVERT(DECIMAL(10,2),(@p1/@p2))
    END AS 'divide'     
)
GO

results from the code for p1 = 15 and p2= 5

I want when the second parameter is zero, only the division to be stopped and show a warning message in the 4th column (divide). The other calculations should be done without any problem. Can someone offer me an idea?

Upvotes: 0

Views: 100

Answers (1)

Dale K
Dale K

Reputation: 27471

You cannot do it exactly as you ask for, because that would be mixing data types, every column returned in the table must be consistent, so if column 4 returns a decimal you can't in some cases, instead, return a string. You need a column 5 for warnings.

    DECLARE @p1 decimal = 0, @p2 decimal = 0;

    SELECT
        @p1 + @p2 add

        , @p1 - @p2 substract
        
        , @p1 * @p2 multiply
    
        , CASE 
            WHEN @p2 = 0
            THEN null
            ELSE CONVERT(DECIMAL(10,2),(@p1/@p2))
        END AS divide

        , CASE 
            WHEN @p2 = 0
            THEN 'Cannot divide by zero'
            ELSE null
        END AS divideError

Upvotes: 1

Related Questions