CODEWITHSUNDEEP
sqlsql-servert-sqlstored-proceduressql-null
Carlos80
Carlos80

Reputation: 433

IF equals or clause

I have a stored procedure that runs two separate queries and puts the data into two temporary tables. I then have an IF statement below that, depending on the outcome will display one of the two tables.

DECLARE @DATASET1 AS FLOAT
DECLARE @DATASET2 AS FLOAT

SET @DATASET1 = (SELECT SUM(PREM) FROM #Prem1)
SET @DATASET2 = (SELECT SUM(PREM) FROM #Prem2)

IF (@DATASET1 = 0)
BEGIN

    SELECT  DATE,
            SUM(PREM) AS PREM
    FROM #DATASET2
    GROUP BY YEAR, MONTH, DATE
    ORDER BY YEAR, MONTH

END

IF (@DATASET2 = 0)
BEGIN

    SELECT  DATE,
            SUM(PREM) AS PREM
    FROM #DATASET1 
    GROUP BY YEAR, MONTH, DATE
    ORDER BY YEAR, MONTH

END

This was working well until I hit some output on dataset1 that didn't produce 0 but just produced no data at all.

So I was wondering if it is possible to update this part of the query to almost say:

IF (@DATASET1 = 0 or '')

I have tried something like that but doesn't seem to work, hence my question.

Upvotes: 0

Views: 222

Answers (2)

Nguyễn Văn Phong
Nguyễn Văn Phong

Reputation: 14228

You can achieve it by using ISNULL()

IF (ISNULL(@DATASET1, 0) = 0)

Syntax: ISNULL(expression, value)

Parameter           Description
expression          Required. The expression to test whether is NULL
value Required.     The value to return if expression is NULL

Upvotes: 1

GMB
GMB

Reputation: 222672

Your scalar aggregate query (SELECT SUM(PREM) FROM #Prem1) will return a NULL value if there is no record in the table or of column PREM contains only NULL values.

You can handle NULL with IS NULL, like so:

IF (@DATASET1 IS NULL OR @DATASET1 = 0)

You can also use COALESCE():

IF (COALESCE(@DATASET1, 0) = 0)

Upvotes: 4

Related Questions