Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2006-11-21
2
Medium Priority
?
543 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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 304 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 Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

571 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