Can I update several columns with CASE expressions using SQL at the same time?

Hi, all here,

I am having a question about update several columns with CASE expression in SQL Server 2005 database engine. Is it possible to do that in SQL Server 2005 database engine?

Thanks a lot in advance for any help and guidance.

Can you be more specific about what you're trying to achieve? Using CASE in an update statement is perfectly legal. For instance you can set the value of a column based on a case statement:

declare @.var int

set @.var = 10

update T1 set Col1 = case @.var when 1 then 'Something' when 2 then 'Something2' else 'SomethingElse' end

where Col1 = 1

Can I update several columns by CASE expressions?

Hi, all here,

I am having a question-is it possible to update several columns using CASE expression in SQL language? like if I wanna set each CASE for each column.

Thanks a lot in advance for any help and guidance.

I think so. Have you tried something and it didn't work? A CASE expression just returns a scalar value, so you can do:

update tablename
set column1 = case when .... end,
column2 = case when ... end,

If you want. If the case statements are completely independent of one another this might be the best way to do something, but if they are from the same data set then you might want to use a join, and possibly a derived table.

Post an example and someone will give you an idea

|||Yes, you can.
Few times, i had to create Stored Procedures that work on just one table and all fields did not allow "NULL" values.
E.g.)

CREATE TABLE NewsTitle (
newstitle_id INT IDENTITY PRIMARY KEY
, title NVARCHAR(512)
, page SMALLINT NOT NULL
, media SMALLINT NOT NULL
)

If you would like to create a simple Sproc that will update a record in that table you might do something like(using CASE) the following:

CREATE PROCEDURE UpdateNewsTitle
@.id INT
, @.title NVARCHAR(512)
, @.page SMALLINT
, @.media SMALLINT
AS
BEGIN
UPDATE NewsTitle
SET title = CASE WHEN @.title IS NULL THEN title ELSE @.title END
, page = CASE WHEN @.page IS NULL THEN page ELSE @.page END
, media = CASE WHEN @.media IS NULL THEN media ELSE @.media END
WHERE newstitle_id = @.id
END
GO

But if you are just checking if passed argument is NULL or not and, if argument is not null then try to use that value, you can use COALESCE function to rid of CASE statement like the following:

ALTER PROCEDURE UpdateNewsTitle
@.id INT
, @.title NVARCHAR(512)
, @.page SMALLINT
, @.media SMALLINT
AS
BEGIN
UPDATE NewsTitle
SET title = COALESCE(@.title, title)
, page = COALESCE(@.page, page)
, media = COALESCE(@.media, media)
WHERE newstitle_id = @.id
END
GO

If you are not familiar with COALESCE, you can look it up on BOL(Books Online). In short, COALESCE returns FIRST non-null value.

Can I set expressions programmatically?

I'm building SSIS packages through code and I would like to set the properties of some custom tasks (not data flow tasks) to expressions. I've done some searches but turned up nothing. This is the only thing I'm hitting a brick wall on at the moment; Books Online has been excellent in detailing how to create packages via code up to this point.

For the sake of argument, let's say I want to set the SqlStatementSource property of an Execute SQL task to this value:

"INSERT INTO [SomeTable] VALUES (NEWID(), '" + @.[User:omeStringVariable] + "')"

What would the code look like?

TaskHost has a SetExpression method:

TaskHost.SetExpression Method

http://msdn2.microsoft.com/de-de/library/microsoft.sqlserver.dts.runtime.taskhost.setexpression.aspx

I presume all of the other container types (Package, Sequence etc..) will do too.

-Jamie

|||AAAGGGGHHH!! I was expecting a property, not a method! So then in order to retrieve all expressions on a task, you'd have to iterate the Properties collection and call GetExpression() with the name of each one to see if it's non-null. Doesn't seem like a particularly good design to me. Why not a simple collection? Oh well, thanks for the help.|||

JeffJohnsonMVPVB wrote:

AAAGGGGHHH!! I was expecting a property, not a method! So then in order to retrieve all expressions on a task, you'd have to iterate the Properties collection and call GetExpression() with the name of each one to see if it's non-null. Doesn't seem like a particularly good design to me. Why not a simple collection? Oh well, thanks for the help.

Yep. I was expecting a Collection as well.

Maybe a read-only collection would be nice that is populated by SetExpression(). Are there such things as read-only collections? I've no idea - I'm no developer.

I too would be interested in seeing the rationale for this. I hope someone from MSFT chimes in.

Regards

-Jamie