Best way to create dynamic update statement

[jw56578@gmail.com]

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.

[David Portas]

Re: Best way to create dynamic update statement

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
--

[--CELKO--]

Re: Best way to create dynamic update statement

>> 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.

[jw56578@gmail.com]

Re: Best way to create dynamic update statement

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.

[strider5]

Re: Best way to create dynamic update statement


jw56...@gmail.c om wrote:[color=blue]
>
> if i want to have one stored procedure to update a(ny) table.[/color]

what would be wrong

--Strider

[strider5]

Re: Best way to create dynamic update statement


jw56...@gmail.c om wrote:[color=blue]
>
> if i want to have one stored procedure to update a(ny) table.[/color]

what would be wrong

--Strider

[David Portas]

Re: Best way to create dynamic update statement

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
--