conditional 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 2008 Forums

Transact-SQL (2008)

conditional update

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2011-04-15 : 06:38:21

Hi, I'm trying to update a record with a counter value.
However, when the counter is 3, it should also update the
'active' column.

So I do this

CREATE PROCEDURE [dbo].[UpdateCounter]
@ItemID int,
@Counter int
AS
UPDATE
dbo.[Items]
SET
Counter = @counter
WHERE
[ItemId] = @ItemID

And I want something like

case @counter
when 3 then Active = 'false'
end

However, when it is not 3, it should not modify the Active column.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-15 : 07:03:09

[code]CREATE PROCEDURE [dbo].[UpdateCounter]
@ItemID int,
@Counter int
AS
UPDATE dbo.[Items] SET
Counter = @counter,
Active = case when @counter = 3 then 'false' else 'true' end
WHERE
[ItemId] = @ItemID
[/code]
If you don't want Active to have nulls when @counter is not 3, leave out the "else 'true'".

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2011-04-15 : 07:12:24

Actually, I did what you said and left out the "else true" part.
However, this gives an error:

Cannot insert the value NULL into column 'Active', table 'dbo.Items'; column does not allow nulls.

The secret to creativity is knowing how to hide your sources. (Einstein)

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2011-04-15 : 07:19:13

Never mind, I did

Active = case when @counter = 3 then 'false' else Active end

The secret to creativity is knowing how to hide your sources. (Einstein)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources