Optimize conditional update - 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 2005 Forums

Transact-SQL (2005)

Optimize conditional update

Author

Topic

johnstern
Yak Posting Veteran

67 Posts

Posted - 2007-05-03 : 11:57:23

I have this Stored Procedure that does an update, sometimes thought one of the fields will comeback null, and I don't want the procedure to set the field to null but rather, leave the field along.

can this be acomplish in a more efficient way than what I am doing now ?

Current Procedure
CREATE PROCEDURE dbo.Dictionary_Term_Update
@TermID int,
@Term varCHAR(200),
@Definition varCHAR (1000),
@ISCNN bit,
@IsGOOGLE bit,
@IsNEWS bit

AS
SET NOCOUNT ON

UPDATE Glossary SET
Term = @Term,
Definition = @Definition,
ISCNN = @ISCNN,
IsGOOGLE = @IsGOOGLE,
IsNEWS = @IsNEWS
WHERE
TermID = @TermID

my probably not optimal idea to acomplish this

e.g.
-- if definition null don't seet definition
if @Definition = null
BEGIN

UPDATE Glossary SET
Term = @Term,
ISCNN = @ISCNN,
IsGOOGLE = @IsGOOGLE,
IsNEWS = @IsNEWS
WHERE
TermID = @TermID
END

-- if @ISCNN = nul don't set ISCNN
if @ISCNN = null
BEGIN
UPDATE Glossary SET
Term = @Term,
Definition = @Definition,
IsGOOGLE = @IsGOOGLE,
IsNEWS = @IsNEWS
WHERE
TermID = @TermID
END

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-03 : 11:59:57

change
ISCNN = @ISCNN,
to
ISCNN = isnull(@ISCNN, ISCNN),

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-03 : 12:01:32

Make use of Coalesce()

UPDATE Glossary SET 
Term = Coalesce(@Term, Term),
Definition = Coalesce(@Definition,Definition), 
ISCNN = Coalesce(@ISCNN, ISCNN),
IsGOOGLE = Coalesce(@IsGOOGLE,IsGOOGLE),
IsNEWS = Coalesce(@IsNEWS,IsNEWS)
WHERE
TermID = @TermID

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

johnstern
Yak Posting Veteran

67 Posts

Posted - 2007-05-03 : 12:11:40

thank you, :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources