471,092 Members | 1,566 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,092 software developers and data experts.

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.

Jul 23 '05 #1
6 6843
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
--

Jul 23 '05 #2
>> 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.

Jul 23 '05 #3
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.

Jul 23 '05 #4

jw56...@gmail.com wrote:

if i want to have one stored procedure to update a(ny) table.


what would be wrong

--Strider

Jul 23 '05 #5

jw56...@gmail.com wrote:

if i want to have one stored procedure to update a(ny) table.


what would be wrong

--Strider

Jul 23 '05 #6
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
--

Jul 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by amitbadgi | last post: by
1 post views Thread by doug.partch | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.