Test for Null value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Test for Null value

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-12-19 : 07:54:21

This may be something as simple as my syntax is off.

I have a stored procedure with 2 select statements.
I first test a value and if it's 0 or null, I do one select else the other...

It's not finding the NULL value and I know the record I'm testing is NULL. The 0 value is ok though....


	DECLARE @lab 	INT
	SET @lab = ( SELECT calLab FROM tblEquipments WHERE ID = @id )

	IF( (@lab = 0) OR (@lab = NULL) )

	BEGIN		
		SELECT  a.assignedID,
			...		
	END  
	ELSE
	BEGIN
		SELECT  a.assignedID,
			...
	END

Suggestions?

Thanks,

Zath

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-19 : 08:00:51

[code]IF( (@lab = 0) OR (@lab IS NULL) )

BEGIN
SELECT a.assignedID,
...
END
ELSE
BEGIN
SELECT a.assignedID,
...
END[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-19 : 08:02:32

IF ISNULL(@Lab, 0) = 0
SELECT a.assignedID
ELSE
SELECT a.assignedID

Peter Larsson
Helsingborg, Sweden

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-12-19 : 08:06:44

Once again thanks!!

Knew it was the syntax, but maybe I can improve the stored procedure and not have duplicat (well almost) select statements.


		SELECT  a.assignedID,
			b.Manufacturer,
			a.modelNumber,
			a.serialNumber,
			e.equipmentType,
			a.description,
			c.Location,
			d.Status,
			a.modifiedDate,
			a.modifiedBy,
			a.Notes,
			a.Picture,			
			f.LabName,
			a.calibrationRequired,
			a.calDate,
			a.CalDueDate,
			a.assetNumber
					

		FROM 	tblEquipments a, 
			tblManufacturers b, 
			tblLocation c, 
			tblStatus d, 
			tblEquipment_Type e,
			tblLabs f

		WHERE a.ID = @id
			AND a.manufacturer = b.manufacturerID
			AND a.location = c.locationID
			AND a.Status = d.statusID
			AND a.EquipmentType = e.ID
			AND f.ID = a.calLab

Problem is, the field calLab. If it's NULL or 0, it returns nothing and that only means the piece of equipment does not require outside calibration.

So, is there a better way than an if statement?

Thanks,

Zath

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-19 : 08:26:34

Try this (as suggested here SELECT" rel="nofollow">http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76538)[code]SELECT a.assignedID,
b.Manufacturer,
a.modelNumber,
a.serialNumber,
e.equipmentType,
a.description,
c.Location,
d.Status,
a.modifiedDate,
a.modifiedBy,
a.Notes,
a.Picture,
f.LabName,
a.calibrationRequired,
a.calDate,
a.CalDueDate,
a.assetNumber
FROM tblEquipments AS a,
INNER JOIN tblManufacturers AS b ON b.manufacturerID = a.manufacturer
INNER JOIN tblLocation AS c ON c.locationID = a.location
INNER JOIN tblStatus AS d ON d.statusID = a.Status
INNER JOIN tblEquipment_Type AS e ON e.ID = a.EquipmentType
LEFT JOIN tblLabs AS f ON f.ID = a.calLab
WHERE a.ID = @id

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-19 : 23:00:03

<<
SET @lab = ( SELECT calLab FROM tblEquipments WHERE ID = @id )
>>

1 You have to make sure that the query returns only one value
You will be safer side if you use

SELECT @lab = calLab FROM tblEquipments WHERE ID = @id

2 Use ANSI joins as written by Peso

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources