SQL Server Forums
Profile | Register | 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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Zath
Constraint Violating Yak Guru

USA
286 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
286 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
286 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  
 New 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.08 seconds. Powered By: Snitz Forums 2000