Replace NULL value with 0 in SQL Server

elvisidrizi1
 
on Aug 26, 2020 06:26 AM
866 Views

Hello Everyone, 

I have this code in SQL that I use for percentage in VB.net but, I need to replace the NULL values to 0 in SQL so I don't have any troubles in Vb.net forms.  This is the SQL code :

DECLARE @Year1 DECIMAL,@Year2 DECIMAL,@Current VARCHAR(7),@Previous VARCHAR(7)
SET @Current='2020-03'
SET @Previous =CAST(SUBSTRING(@Current,1,6) AS VARCHAR(5)) + RIGHT('0' + CAST(SUBSTRING(@Current,6,2) - 1 AS VARCHAR(2)),2)
 
SELECT @Year1 = Sum(Price) FROM dbo.Expenses WHERE CONVERT(char(7), date, 120) = @Previous AND Department IN ('Grocery','Department','FixCost','Clothes','Other','Vacation')
SELECT @Year2 = Sum(Price) FROM dbo.Expenses WHERE CONVERT(char(7), date, 120) = @Current AND Department IN ('Grocery','Department','FixCost','Clothes','Other','Vacation')
SELECT 100.0*(@Year2 - @Year1) / @Year1 As PercentageDiff

And when there are empty values I would like to have as result 0 and not NULL. 

Thank you, Best REgards

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Aug 26, 2020 07:14 AM
on Aug 26, 2020 08:05 AM

Hi elvisidrizi1,

For null you have to use ISNULL function.

SQL

DECLARE @Year1 DECIMAL,@Year2 DECIMAL,@Current VARCHAR(7),@Previous VARCHAR(7)
SET @Current='2020-03'
SET @Previous =CAST(SUBSTRING(@Current,1,6) AS VARCHAR(5)) + RIGHT('0' + CAST(SUBSTRING(@Current,6,2) - 3 AS VARCHAR(2)),2)
 
SELECT @Year1 = NULLIF(Sum(Price),0) FROM dbo.Expenses WHERE CONVERT(char(7), date, 120) = @Previous AND Department IN ('Grocery','Department','FixCost','Clothes','Other','Vacation')
SELECT @Year2 = ISNULL(Sum(Price),0) FROM dbo.Expenses WHERE CONVERT(char(7), date, 120) = @Current AND Department IN ('Grocery','Department','FixCost','Clothes','Other','Vacation')
SELECT ISNULL((100.0*(@Year2 - @Year1) / @Year1),0) As PercentageDiff