Best way to create dynamic update statement

In general, What is the best approach in creating a dynamic update
stored procedure, that can handle recieving varying input paramters and
update the approporiate columns.Depends on the requirements but one possibility is to use NULL
parameters to represent values that shouldn't be changed:

UPDATE YourTable
SET col1 = COALESCE(@.col1, col1),
col2 = COALESCE(@.col2, col2),
col3 = COALESCE(@.col3, col3)
... etc
WHERE ...

--
David Portas
SQL Server MVP
--|||>> In general, What is the best approach in creating a dynamic update
stored procedure, <<

In general, building dynamic is a bad idea. It says that you don't
know what you are doing, so you are turning over control of the system
at runtime to any random user, present or future. SQL is a compiled
language, not like BASIC.|||what would be wrong with using:
UPDATE YourTable
SET col1 = COALESCE(@.col1, col1),
col2 = COALESCE(@.col2, col2),
col3 = COALESCE(@.col3, col3)
... etc
WHERE ...

if i want to have one stored procedure to update a table.|||jw56...@.gmail.com wrote:
> if i want to have one stored procedure to update a(ny) table.

what would be wrong

--Strider|||jw56...@.gmail.com wrote:
> if i want to have one stored procedure to update a(ny) table.

what would be wrong

--Strider|||I think this is just some confusion over terminology. The term "dynamic
update" or "dynamic code" refers to code that references metadata
(usually table and column names) dynamically - elements of the code
being constructed at runtime. This is not generally good practice for
various reasons to do with performance, security, maintainability and
modular design. In your case however, no dynamic code is necessary.

--
David Portas
SQL Server MVP
--