I have a stored procedure that inserts values into five columns of a
table. I need another stored procedure that will allow the user to pass
one or more of those parameters and update only the column for the data
that was passed. In other words, the update may only have one or two of
the parameters that was originally provided in the insert. Therefore, I
do not want to update the columns that did not change. What is the
proper way to handle this situation?

binder (rgondzur@gmail.com) writes:
> I have a stored procedure that inserts values into five columns of a
> table. I need another stored procedure that will allow the user to pass
> one or more of those parameters and update only the column for the data
> that was passed. In other words, the update may only have one or two of
> the parameters that was originally provided in the insert. Therefore, I
> do not want to update the columns that did not change. What is the
> proper way to handle this situation?

In our application, most insert/update procedures exposes about all
columns in the table, and when the client fills the GUI, it reads all
columns from the table. Thus there is little reason to check what
actually changed. (Except for auditing.)

If you want to expose an interface of a procedure where the caller only
specifies what the to change, because it has for some reason not read
all existing columns (and this makes sense for a client that is a
monitori or similar), you could do:

CREATE PROCEDURE update_sp @keyvalue sometype,
@par1 someothertype = NULL,
@par2 yetanothertype = NULL,
.... AS

UPDATE tbl
SET col1 = coalesce(@par1, col1),
col2 = coalesce(@par2, col2),
...
WHERE keycol = @keyvalue

That is, the procedure accepts parameters for all updatable columns,
but the caller passes non-NULL values for those it does not want to
change. This presumes that a caller never want to set a column to
NULL. If this is a required, you need to add one flag parameter for
each value parameter.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx