update - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

update

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-01-27 : 08:41:43

Hello,
I would like to have an update statement which takes several parameters.
But only the table fields with the approprioate parameters which have passed in values are updated.
Example:

if (@param1 is not null)
begin
update tblMain set field1 = @param1 where ID = @ID
end
if (@param2 is not null)
begin
update tblMain set field2 = @param2 where ID = @ID
end
if (@param1 is not null and @param2 is not null)
begin
update tblMain set field1 = @param1 and field2 = @param2 where ID = @ID
end
...

Question
How can the above statement be put into one update stored procedure?

Thank you

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-27 : 08:46:57

[code]
update tblMain
set field1 = coalesce(@param1, field1),
field2 = coalesce(@param2, field2)
where ID = @ID
and (
@param1 is not null
or @param2 is not null
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-01-27 : 08:52:07

Is this ok?

create procedure uspUpdate

@param1 varchar(20) = null,
@param2 varchar(20) = null
as
update tblMain
set field1 = @param1,
field2 = @param2
where ID = @ID
and (
@param1 is not null
or @param2 is not null
)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-27 : 08:55:27

Nope. What if only 1 of the @param1 is with value and @param2 is null ? You are setting field2 to NULL

KH
[spoiler]Time is always against us[/spoiler]

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-01-27 : 08:57:30

Is this ok?
create procedure uspUpdate

@param1 varchar(20) = null,
@param2 varchar(20) = null
as
update tblMain
set
field1 = coalesce(@param1, field1),
field2 = coalesce(@param2, field2)
where ID = @ID
and (
@param1 is not null
or @param2 is not null
)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-27 : 09:01:32

what do you think ?

Give it a test run and you will know

KH
[spoiler]Time is always against us[/spoiler]

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-01-27 : 09:04:30

This does not seem to update anything.

uspUpdate @ID = 3, @val = 1

create procedure uspUpdate

@param1 varchar(20) = null,
@param2 varchar(20) = null,
@ID int,
@val smallint = null

as
update tblMain
set
field1 = coalesce(@param1, field1),
field2 = coalesce(@param2, field2),
field3 = coalesce(@val, field3)
where ID = @ID
and (
@param1 is not null
or @param2 is not null
or @val is not null
)

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 09:12:03

How did you call uspUpdate ?

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 09:12:28

Ah ... I think you just added it

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-01-27 : 09:14:20

note that I updated the query once again by placing = null infront of @val

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 09:14:24

[code]
SELECT field1,
field2,
field3
FROM tblMain
where ID = 3
[/code]
what is the value for field3 before update? If it is already "1" then the update won't change anything

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-01-27 : 09:18:36

no, it is 3 before the update

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 11:33:57

Definitely should be working, I can't see anything wrong with the Sproc.


SELECT field1,
       field2,
       field3
FROM tblMain 
where ID = 3

EXEC uspUpdate @ID = 3, @val = 1

SELECT field1,
       field2,
       field3
FROM tblMain 
where ID = 3

should give you a Before & After resultset so you can see if anything changed, or not.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-01-27 : 16:02:10

Thank you

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 16:20:27

Did it work ?? I'm waiting with baited breath!

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-01-28 : 09:10:53

Its easier to wait when you bait your breath with a good Weissen beer

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 09:26:24

... now waiting with stale breath

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-01-28 : 14:02:56

Hi,
Yes it did.
Many thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-29 : 02:26:14

quote:
Originally posted by Kristen

Did it work ?? I'm waiting with baited breath!

Many OPs dont tell us if the solutions work

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-01-29 : 04:36:09

Indeed, but the code I posted was just a diagnostic for the SProc which didn't work. It wasn't a fix ... so I was curious if the "Thank you" mean it had worked, or that the O/P was going to try the code and report back so we could try to find the cause.

Ho!Hum!

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-01-29 : 15:35:29

Yes, it is working fine.
Many thanks to y ou all.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources