Best way for Stored Procedure to update fields conditionally?  
Author Message
GDURAN





PostPosted: Sun Sep 24 03:05:17 CDT 2006 Top

SQL Server >> Best way for Stored Procedure to update fields conditionally?

I want to write a stored procedure that updates a record, only updating
the fields where the value passed is not null.

So ideally in the UPDATE statement I want something like

CREATE PROCEDURE myproc
@param1 nvarchar(20),
@param2 nvarchar(20)

UPDATE myrec
SET




etc.

but I'm guessing I can't do that.

Obviously I don't want to have to read the record first to compare the
current values with the ones I'm passing in.

What's the best (shortest, most efficient) way to do this?

Thanks,

ChrisN

SQL Server145  
 
 
Jens





PostPosted: Sun Sep 24 03:05:17 CDT 2006 Top

SQL Server >> Best way for Stored Procedure to update fields conditionally? Hi,



HTH, Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---

 
 
Andrew





PostPosted: Sun Sep 24 08:55:23 CDT 2006 Top

SQL Server >> Best way for Stored Procedure to update fields conditionally? First off I would ask why you care? If you are going to update the row the
extra overhead to update columns that don't need to is extremely small and
trivial compared to the other operations that must occur for an update.
Second how can you check for NULL? Are you saying none of your columns
allow NULL's? If so how would you pass in a NULL?

--
Andrew J. Kelly SQL MVP



>I want to write a stored procedure that updates a record, only updating
> the fields where the value passed is not null.
>
> So ideally in the UPDATE statement I want something like
>
> CREATE PROCEDURE myproc


>
> UPDATE myrec
> SET




> etc.
>
> but I'm guessing I can't do that.
>
> Obviously I don't want to have to read the record first to compare the
> current values with the ones I'm passing in.
>
> What's the best (shortest, most efficient) way to do this?
>
> Thanks,
>
> ChrisN
>


 
 
Michael





PostPosted: Tue Sep 26 09:32:10 CDT 2006 Top

SQL Server >> Best way for Stored Procedure to update fields conditionally? If you want to update based on the values passed in, you can do something
such as this


BEGIN
UPDATE tabel

WHERE ...
END


BEGIN
UPDATE table

WHERE ..
END

Or you could split it up into multiple stored proceduces.

Why you would want to do this, I am not sure...
Keep in mind that when using IF conditions in a stored procedure, SQL is
less likely to re-use execution plans, lessening the benefit of using a
stored procedure.



>I want to write a stored procedure that updates a record, only updating
> the fields where the value passed is not null.
>
> So ideally in the UPDATE statement I want something like
>
> CREATE PROCEDURE myproc


>
> UPDATE myrec
> SET




> etc.
>
> but I'm guessing I can't do that.
>
> Obviously I don't want to have to read the record first to compare the
> current values with the ones I'm passing in.
>
> What's the best (shortest, most efficient) way to do this?
>
> Thanks,
>
> ChrisN
>