Solved

How to replace Null Values with a 0 in a stored procedure?

Posted on 2006-11-21
2
495 Views
Last Modified: 2012-05-05
I have the following stored procedure that works fine. I would like to tidy up one thing - the 'Jan' column displays data and null values, I would like to replace the null values with 0. Can anyone tell me how to do this (I am using SQL Server 2005)?

ALTER PROCEDURE [dbo].[RollingAFR]
(
@Year INT
) AS
BEGIN
SELECT O.Region, O.Description, (SELECT (Sum([VSeverity].[CountOfSeverity])*100000)/Sum([VHours].[Total]) AS AFR
FROM [VHours] LEFT JOIN VSeverity ON ([VHours].Unit_Office = VSeverity.Unit_Office) AND ([VHours].Year = VSeverity.Year) AND ([VHours].Period = VSeverity.Period)
WHERE ((([VHours].Unit_Office)= O.Description) AND (([VHours].Year)=@Year) AND (([VHours].Period)<=1)) OR ((([VHours].Unit_Office)= O.Description) AND (([VHours].Year)=(@Year-1)) AND (([VHours].Period)>1))
) AS Jan
FROM dbo.tbl_RegionOffice AS O
END
RETURN
0
Comment
Question by:kenmck
2 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 76 total points
ID: 17986986
ALTER PROCEDURE [dbo].[RollingAFR]
(
@Year INT
) AS
BEGIN
SELECT O.Region, O.Description, coalesce((SELECT (Sum( coalesce([VSeverity].[CountOfSeverity],0) )*100000)/Sum([VHours].[Total]) AS AFR
FROM [VHours] LEFT JOIN VSeverity ON ([VHours].Unit_Office = VSeverity.Unit_Office) AND ([VHours].Year = VSeverity.Year) AND ([VHours].Period = VSeverity.Period)
WHERE ((([VHours].Unit_Office)= O.Description) AND (([VHours].Year)=@Year) AND (([VHours].Period)<=1)) OR ((([VHours].Unit_Office)= O.Description) AND (([VHours].Year)=(@Year-1)) AND (([VHours].Period)>1))
),0) AS Jan
FROM dbo.tbl_RegionOffice AS O
END
RETURN
0
 

Author Comment

by:kenmck
ID: 18018122
Thanks for that worked a treat.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now