how to overcome Null values in storedprocedure

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Dear Friends,

Here iam Facing problem in storedprocedure.

In my table if the data is without null there is no problem in my query.if the table data is inserted with null value then the problem is coming.So iam giving my query.

SQL

SELECT        Table1.District, Table1.School, Table1.Students, Table2.Teachers, ROUND(CONVERT(Decimal(10, 2), Table1.Students / CONVERT(Decimal(10, 2), 
                         Table2.Teachers)), 3) AS ratio
FROM            (SELECT        ntblDistrict_1.txtDistrictName AS District, SUM(tblSummaryStudents.intMale) + SUM(tblSummaryStudents.intFemale) AS Students, 
                                                    ntblPrimarySchoolList.txtPrimarySchoolName AS School
                          FROM            ntblDistrict AS ntblDistrict_1 INNER JOIN
                                                    ntblPrimarySchoolList ON ntblDistrict_1.intDistrictID = ntblPrimarySchoolList.intDistrictID INNER JOIN
                                                    tblSchool ON ntblPrimarySchoolList.intPrimaryListID = tblSchool.intPrimaryListID INNER JOIN
                                                    tblSummaryStudents ON tblSchool.intSchoolID = tblSummaryStudents.intSchoolID
                          GROUP BY ntblDistrict_1.txtDistrictName, ntblDistrict_1.intDistrictID, ntblPrimarySchoolList.txtPrimarySchoolName
                          HAVING         (ntblDistrict_1.intDistrictID = 9)) AS Table1 INNER JOIN
                             (SELECT        tblSummaryOthers.intTeachersMale + tblSummaryOthers.intTeachersFemale AS Teachers, 
                                                         ntblPrimarySchoolList_1.txtPrimarySchoolName AS School
                               FROM            tblSummaryOthers INNER JOIN
                                                         tblSchool AS tblSchool_1 ON tblSummaryOthers.intSchoolID = tblSchool_1.intSchoolID INNER JOIN
                                                         ntblPrimarySchoolList AS ntblPrimarySchoolList_2 ON tblSchool_1.intPrimaryListID = ntblPrimarySchoolList_2.intPrimaryListID AND 
                                                         tblSchool_1.intPrimaryListID = ntblPrimarySchoolList_2.intPrimaryListID INNER JOIN
                                                         ntblPrimarySchoolList AS ntblPrimarySchoolList_1 ON tblSchool_1.intPrimaryListID = ntblPrimarySchoolList_1.intPrimaryListID AND 
                                                         tblSchool_1.intPrimaryListID = ntblPrimarySchoolList_1.intPrimaryListID
                               WHERE        (ntblPrimarySchoolList_2.intDistrictID =  @intDistrictID)
                               GROUP BY tblSummaryOthers.intTeachersMale + tblSummaryOthers.intTeachersFemale, ntblPrimarySchoolList_2.intDistrictID, 
                                                         ntblPrimarySchoolList_1.txtPrimarySchoolName
                               HAVING         (ntblPrimarySchoolList_2.intDistrictID = 9)) AS Table2 ON Table1.School = Table2.School

Out put is like this there is no Null values in my table

Zanzibar Urban JANGOMBE-A 630 42 15.00
Zanzibar Urban JANGOMBE-B 24 21 1.14
Zanzibar Urban K/CHEKUNDU-B 562 24 23.42
Zanzibar Urban KAJIFICHENI 341 33 10.33
Zanzibar Urban KILIMAHEWA-B 2 23 0.09
Zanzibar Urban KISIWANDUI 629 34 18.50
Zanzibar Urban NYERERE 5 21 0.24
If the data in my table having any null value then the out put is:

Zanzibar Urban JANGOMBE-A 630 42 15.00
Zanzibar Urban JANGOMBE-B Null Null Null
Zanzibar Urban K/CHEKUNDU-B 562 24 23.42
Zanzibar Urban KAJIFICHENI 341 33 10.33
Zanzibar Urban KILIMAHEWA-B 2 23 0.09
Zanzibar Urban KISIWANDUI 629 34 18.50
Zanzibar Urban NYERERE Null Null Null

Then these null values should be replace with 0

Please find solution for this.

Regards,

ANILKUMAR.D

Posted 28-Nov-11 16:25pm

Anil Honey 206

Updated 28-Nov-11 17:01pm

RaisKazi

v4

Add a Solution

Comments

[no name] 28-Nov-11 22:32pm

try my solution below

RaisKazi 28-Nov-11 23:00pm

Edited: 1) Formatting 2) Correction of "pre" tag.

chain singh aanjana 10-Oct-12 11:40am

hghggggggggggggggggghhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Eduard Lu · Answer 1 · 2011-11-28T16:31:00

Solution 1

Try something like this in your SELECT statement

for varchar:

SQL

SELECT ISNULL(firstname, '')

for int:

SQL

SELECT ISNULL(age, 0)

Please mark as answer if this solved your problem

Best Regards,
Eduard

Posted 28-Nov-11 16:31pm

Eduard Lu

Updated 28-Nov-11 16:33pm

v2

sachin10d · Answer 2 · 2011-11-28T17:06:00

Try this

SQL

SELECT  Table1.District, Table1.School, isnull(Table1.Students,0) Students, isnull(Table2.Teachers,0) Teachers, 
	ROUND(CONVERT(Decimal(10, 2), isnull(Table1.Students,0) / CONVERT(Decimal(10, 2), isnull(Table2.Teachers,0))), 3) AS ratio
FROM            (SELECT        ntblDistrict_1.txtDistrictName AS District, SUM(tblSummaryStudents.intMale) + SUM(tblSummaryStudents.intFemale) AS Students, 
                                                    ntblPrimarySchoolList.txtPrimarySchoolName AS School
                          FROM            ntblDistrict AS ntblDistrict_1 INNER JOIN
                                                    ntblPrimarySchoolList ON ntblDistrict_1.intDistrictID = ntblPrimarySchoolList.intDistrictID INNER JOIN
                                                    tblSchool ON ntblPrimarySchoolList.intPrimaryListID = tblSchool.intPrimaryListID INNER JOIN
                                                    tblSummaryStudents ON tblSchool.intSchoolID = tblSummaryStudents.intSchoolID
                          GROUP BY ntblDistrict_1.txtDistrictName, ntblDistrict_1.intDistrictID, ntblPrimarySchoolList.txtPrimarySchoolName
                          HAVING         (ntblDistrict_1.intDistrictID = 9)) AS Table1 INNER JOIN
                             (SELECT        tblSummaryOthers.intTeachersMale + tblSummaryOthers.intTeachersFemale AS Teachers, 
                                                         ntblPrimarySchoolList_1.txtPrimarySchoolName AS School
                               FROM            tblSummaryOthers INNER JOIN
                                                         tblSchool AS tblSchool_1 ON tblSummaryOthers.intSchoolID = tblSchool_1.intSchoolID INNER JOIN
                                                         ntblPrimarySchoolList AS ntblPrimarySchoolList_2 ON tblSchool_1.intPrimaryListID = ntblPrimarySchoolList_2.intPrimaryListID AND 
                                                         tblSchool_1.intPrimaryListID = ntblPrimarySchoolList_2.intPrimaryListID INNER JOIN
                                                         ntblPrimarySchoolList AS ntblPrimarySchoolList_1 ON tblSchool_1.intPrimaryListID = ntblPrimarySchoolList_1.intPrimaryListID AND 
                                                         tblSchool_1.intPrimaryListID = ntblPrimarySchoolList_1.intPrimaryListID
                               WHERE        (ntblPrimarySchoolList_2.intDistrictID =  @intDistrictID)
                               GROUP BY tblSummaryOthers.intTeachersMale + tblSummaryOthers.intTeachersFemale, ntblPrimarySchoolList_2.intDistrictID, 
                                                         ntblPrimarySchoolList_1.txtPrimarySchoolName
                               HAVING         (ntblPrimarySchoolList_2.intDistrictID = 9)) AS Table2 ON Table1.School = Table2.School