Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dgarcia59
Starting Member
7 Posts |
 Posted - 2003-04-03 : 12:09:39
|
| I am trying to write a stored procedure that updates 5 different fields in a single row. There are several places in the program that I will call the stored procedure. Each time I will be updating different values. One time I will update all 5, another time I will update just the first three field and another time I will update just the last two.All of the values are of the money datatype. I would never update a value to null, so one idea I had was to pass a null whenever I do not want to update a particular field. But I am not sure how to implement that cleanly, any ideas? |
|
|
MichaelP
Jedi Yak
2489 Posts |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-04-03 : 12:26:35
|
| create proc updtbl@id int ,@m1 money ,@m2 money ,@m3 money ,@m4 money ,@m5 moneyas update tbl set m1 = coalesce(@m1,m1) , m2 = coalesce(@m2,m2) , m3 = coalesce(@m3,m3) , m4 = coalesce(@m4,m4) , m5 = coalesce(@m5,m5) where id = @idgo==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-04-03 : 12:35:15
|
| Doh, why didn't I think of that.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
dgarcia59
Starting Member
7 Posts |
Posted - 2003-04-03 : 12:37:53
|
| Thanks nr, that worked perfectly! Never even heard of COALESCE, learn something new every day. I actually read on a Dynamic SQL tutorial that 'use dynamic sql' is a typical response to this question, but that there are much better methods. Of course, being a tutorial on dynamic sql, it did not outline the better methods. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-04-03 : 12:49:18
|
| You can also use isnull which would do the same thing in this case.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-04-03 : 13:59:42
|
| Dynamic SQL = last resort, in my humble opinion.It can be useful and can be necessary, and it's a great way to solve problems when you need it, but always look for a more traditional (and optimizable and complilable) approach.- Jeff |
|
|
|
|
|
|
|
|
Update only some of the fields