Null values in SQL

[xerouk]

Hi guys im having trouble with Null values in SQL

Create Procedure uspCheckParsandYards

@CourseName varchar(50),
@CheckPars int Output,
@NoOfHoles int Output,
@CheckYards int Output

AS

Declare @CourseID int = (Select CourseID From tblCourses Where CourseName = @CourseName)

Select @NoOfHoles = (Select tblCourses.NumberOfHoles From tblCourses Where CourseID = @CourseID)

Select @Checkpars = (Select ISNULL(tblPar.Hole1Par,'0') Hole1Par From tblPar
Where CourseID = @CourseID)

Select @CheckYards = (Select ISNULL(tblYards.Hole1Yds,'0') AS Hole1Yds From tblYards
Where CourseID = @CourseID)

if one of the Select statements returns no records i get a NULL value and i cant seem to figure how to convert it to something i can use either 0 or "". is this possible from within the query?

 

[Andre]

Wrap the selects in ISNULL too!
e.g.

Select @NoOfHoles  = ISNULL((Select tblCourses.NumberOfHoles From tblCourses Where CourseID = @CourseID),0)

 

[SimonCHere]

Or just use:

select @NoOfHoles=c.NumberOfHoles, @CheckPars=isnull(p.Hole1Par,0), @CheckYards=isnul(y.Hole1Yrds,0)
from tblCourses c
left join tblPar p
on p.CourseID = c.CourseID
left join tblYards y
on y.CourseID  = c.CourseID
where c.CourseName = @CourseName

Of course this doesn't address why you have a Yards table and a Par table when they should probably be fields in a Holes table and have a CourseHoles table, but there you go.

Also in your original selects, you were trying to store a string '0' in the int variables.