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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 If parameter is Null
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Zath
Constraint Violating Yak Guru

USA
298 Posts

Posted - 01/17/2007 :  09:07:56  Show Profile  Visit Zath's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 01/17/2007 :  09:10:57  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

Zath
Constraint Violating Yak Guru

USA
298 Posts

Posted - 01/17/2007 :  09:35:07  Show Profile  Visit Zath's Homepage  Reply with Quote
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
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 01/17/2007 :  09:41:20  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
Try this:

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


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

Zath
Constraint Violating Yak Guru

USA
298 Posts

Posted - 01/17/2007 :  09:47:08  Show Profile  Visit Zath's Homepage  Reply with Quote
I see, thanks again!!!!

Zath
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000