RESOLVED: Not updating fields when null

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)

RESOLVED: Not updating fields when null

Author

Topic

FruitBatInShades
Yak Posting Veteran

51 Posts

Posted - 2006-01-10 : 04:29:13

Is there a simple way to not update fields when the passed parameter is null? AT the moment I using IF..ELSE statment to execute slightly modified statements depending on whether a parameter is null or not. This is fine when its inly 1 field but is cumbersome when there is more than 1 field.


CREATE PROCEDURE UpdateDirectoryItem 
@ItemType int,
@ItemName varchar(50),
@KeyWords varchar(255),
@ShortDesc text,
@ImageURL varchar(128),
@UniqueID bigint

AS

IF (@ShortDesc IS NULL)
BEGIN
  Update DirectoryItems SET
  ItemType = @ItemType,
  ItemName=@ItemName,
  Keywords=@Keywords,
  ImageURL=@ImageURL
  WHERE UniqueID = @UniqueID;
END
ELSE
BEGIN
 Update DirectoryItems SET
  ItemType = @ItemType,
  ItemName=@ItemName,
  Keywords=@Keywords,
  ShortDesc=@ShortDesc,
  ImageURL=@ImageURL
  WHERE UniqueID = @UniqueID;
END
GO

Any tips, ideas, advice greatfully recieved

FruitBatInShades
Yak Posting Veteran

51 Posts

Posted - 2006-01-10 : 04:30:44

Forgot to get email notification

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-10 : 04:33:18

Update the table with ist non null value
Update DirectoryItems SET
ItemType = coalesce (@ItemType, itemtype),
ItemName= coalesce (@ItemName, itemname),
Keywords= coalesce (@Keywords,keywords),
ImageURL= coalesce (@ImageURL ,imageurl),
WHERE UniqueID = @UniqueID;

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-10 : 04:33:50

[code]Update DirectoryItems SET
ItemType = @ItemType,
ItemName=@ItemName,
Keywords=@Keywords,
ShortDesc=coalesce(@ShortDesc, ShortDesc)
ImageURL=@ImageURL
WHERE UniqueID = @UniqueID[/code]

-----------------
'KH'

FruitBatInShades
Yak Posting Veteran

51 Posts

Posted - 2006-01-10 : 04:39:45

Thanks guys, something so simple :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-10 : 05:07:55

FruitBatInShades,
What did you edit in your question after getting the answers?

Madhivanan

Failing to plan is Planning to fail

FruitBatInShades
Yak Posting Veteran

51 Posts

Posted - 2006-01-10 : 08:04:39

[code]
CREATE PROCEDURE UpdateDirectoryItem
@ItemType int,
@ItemName varchar(50),
@KeyWords varchar(255),
@ShortDesc text,
@ImageURL varchar(128),
@UniqueID bigint

AS

Update DirectoryItems SET
ItemType = @ItemType,
ItemName=@ItemName,
Keywords=@Keywords,
ShortDesc=COALESCE(@ShortDesc,ShortDesc),
ImageURL=@ImageURL
WHERE UniqueID = @UniqueID;
GO
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources