In SQL server management if not null then replace the value with one

Question

ALTER FUNCTION [dbo].[Get_CGPA] (@s1 DECIMAL(20, 2), @s2 DECIMAL(20, 2), @s3 DECIMAL(20, 2), @s4 DECIMAL(20, 2), @s5 DECIMAL(20, 2), @s6 DECIMAL(20, 2), @s7 DECIMAL(20, 2), @s8 DECIMAL(20, 2)) RETURNS DECIMAL(20, 2) AS 
BEGIN
    DECLARE @cgpa DECIMAL(20, 2);
DECLARE @total DECIMAL(20, 2) = (
    COALESCE(@s1, 0) + COALESCE(@s2, 0) + COALESCE(@s3, 0) + COALESCE(@s4, 0) + COALESCE(@s5, 0) + COALESCE(@s6, 0) + COALESCE(@s7, 0) + COALESCE(@s8, 0)
);
DECLARE @DIV DECIMAL(20, 2) = (
    dbo.fun_notnull(@s1) + dbo.fun_notnull(@s2) + dbo.fun_notnull(@s3) + dbo.fun_notnull(@s4) + dbo.fun_notnull(@s5) + dbo.fun_notnull(@s6) + dbo.fun_notnull(@s7) + dbo.fun_notnull(@s8)
);
IF(@DIV = 0) 
BEGIN
    SET
        @DIV = NULL;
END
SET
    @cgpa = (
        @total / @DIV
    );
RETURN @cgpa 
END

I made another function (not_null) to get 1 if the parameter is non-zero. Is there any other alternative?

Welcome to Stack Overflow. You will have a better experience here if you take the [Tour](https://stackoverflow.com/tour) and read through [How To Ask](https://stackoverflow.com/help/how-to-ask), then write your question with the details needed to create [a Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example). See [How to post a T-SQL question on a public forum](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for tips. — Eric Brandt, Jun 09 '20 at 12:02
Eric is right. We really need to see the code from your function. Without seeing your code, we can only make guesses or give suggestions. — tgolisch, Jun 09 '20 at 13:09

score 0 · Answer 1 · answered Jun 09 '20 at 13:06

0

The question isn't entirely clear, but it sounds like you want the COALESCE() function:

COALESCE(SomeField, 1)

In the context of a GPA, which is the result of a division operation, you may also need to check for 0 on the bottom half of the expression:

COALESCE(NULLIF(SomeField,0),1)

answered Jun 09 '20 at 13:06

Joel Coehoorn

399,467
113
570
794

that changes it to 1 if it is null, not if it is not null. – Hogan Jun 09 '20 at 13:12

score 0 · Accepted Answer · answered Jun 09 '20 at 13:14

0

You can do it with a case statement like this

 CASE WHEN SomeField is null THEN 1 ELSE null END as SomeField

answered Jun 09 '20 at 13:14

Hogan

69,564
10
76
117

In SQL server management if not null then replace the value with one

2 Answers2