update certain feilds and response back with code

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

General SQL Server Forums

New to SQL Server Programming

update certain feilds and response back with code

Author

Topic

tpiazza55
Posting Yak Master

162 Posts

Posted - 2011-02-10 : 17:47:17

i am writing a sp that will update a table. i have a bunch of optional parameters that may or may not be updated dependent on the request

what is the best way to only update the fields passed? kind of like i have an update statement with 5 feilds, 3 are passed the other 2 are passed as blank.

also after the update occurs I want to send back a value of 4

is there a better way than:

update command then the below

SET @Return = 4
SELECT @Return AS myresponse

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-02-11 : 04:09:05

You could do something like this:


UPDATE table SET 
  Col1 = CASE WHEN @Col1 IS NOT NULL THEN @Col1 ELSE Col1 END,
  Col2 = CASE WHEN @Col2 IS NOT NULL THEN @Col2 ELSE Col1 END

This return value of 4, what does it mean? You could either do as you posted or you can simply do a

...
RETURN 4

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-02-12 : 03:59:13

short way is:-


..
UPDATE table SET 
  Col1 = COALESCE(NULLIF(@Col1,''),Col1),
  Col2 = COALESCE(NULLIF(@Col2,''),Col2)
..

this will make sure updation of value dont happen when variable is blank or NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources