If parameter is Null - 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)

If parameter is Null

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-01-17 : 09:07:56

I have in my table an image field called picture.

There must be a better way than an if else statement to detect if the parameter is null and if so, not update that field....


		IF (@picture Is NULL)
		BEGIN

		UPDATE tblEquipments
		SET    	assignedID = @assignedID,		
			assetNumber = @assetNumber	
		WHERE  id = @id;	
		END			

		ELSE

		IF (@picture Is NOT NULL)
		BEGIN

		UPDATE tblEquipments
		SET    	assignedID = @assignedID,  	
			assetNumber = @assetNumber,		
			picture = @picture			
		WHERE  id = @id;	
		END

                END

It really wouldn't be a problem but I removed a lot of other fields here for a better read so the update is a lot longer.

Any suggestions?

Thanks,

Zath

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-17 : 09:10:57

How about this?

UPDATE tblEquipments
SET    	assignedID = @assignedID,  	
	assetNumber = @assetNumber,		
	picture = Coalesce(@picture, picture)
WHERE  id = @id

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-01-17 : 09:35:07

Knew there had to be a better way - worked perfectly.

Thanks so much!!!

And on a similar senario, if another parameter = 0 then don't update that field????

Zath

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-17 : 09:41:20

Try this:

Update Tbl
Set fld = Coalesce(NullIf(@param, 0), fld)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-01-17 : 09:47:08

I see, thanks again!!!!

Zath

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources