July 29, 2011 at 3:16 pm
So, when creating a table can I create column defaults that include some sort of case statement?
For instance, I want to add a default to a column (SevType). If, when the record is entered, another column (ApptType) is 1, I want the default value of SevType to be 0. If ApptType is 2, I want the default to be 3 instead.
When I tried this (not having a clue whether it would work or not), it complained about incorrect syntax near keyword 'SET.'
ALTER TABLE tblMine
ALTER COLUMN SevType SET DEFAULT CASE ApptType WHEN 1 THEN 0 WHEN 2 THEN 3 ELSE NULL END
Seems like there must be some way to do this and I'm just having a serious case of Friday brain. Perhaps not... Any help?
August 1, 2011 at 12:48 pm
You could achieve this fairly easily in an instead of insert trigger.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 1, 2011 at 12:59 pm
The default isn't an expression run against the row. It's a value. Same for computed columns, these don't allow logical expressions.
A trigger is the best way to handle this.
August 2, 2011 at 12:08 pm
Steve Jones - SSC Editor (8/1/2011)
The default isn't an expression run against the row. It's a value. Same for computed columns, these don't allow logical expressions.A trigger is the best way to handle this.
Steve,
Actually logical expressions can be used for computed columns:
CREATE TABLE tblMine
( RecIDINT
, ApptType INT
, SevType AS CASE WHEN ApptType = 1 THEN 0
WHEN ApptType = 2 THEN 3
ELSE NULL END
)
INSERT INTO tblMine
( RecID, ApptType )
VALUES
( 1, 1 ),
( 2, 2 ),
( 3, 3),
( 4, NULL)
SELECT * FROM tblMine
Of course you can't update the computed column so it's kind of useless here.
I agree a trigger would be the way to go.
Todd Fifield
August 2, 2011 at 12:14 pm
Oh, yeah. I've used case statements in computed columns before. That's actually part of the reason I was hoping there was a way to do it in default. But you're right. The fact that computed columns can't be updated is a problem in this case.
Looks like I'm going to have to go with a trigger.
Thanks everyone!
August 2, 2011 at 12:18 pm
Can be persisted or indexed, so no "need" for trigger here. Tho I might choose that option as well.
August 2, 2011 at 12:19 pm
srferson-957148 (8/2/2011)
Oh, yeah. I've used case statements in computed columns before. That's actually part of the reason I was hoping there was a way to do it in default. But you're right. The fact that computed columns can't be updated is a problem in this case.Looks like I'm going to have to go with a trigger.
Thanks everyone!
Why would you want the computed column to be updated?? That kind of defeat the purpose!
August 2, 2011 at 12:26 pm
Ninja's_RGR'us (8/2/2011)
srferson-957148 (8/2/2011)
Oh, yeah. I've used case statements in computed columns before. That's actually part of the reason I was hoping there was a way to do it in default. But you're right. The fact that computed columns can't be updated is a problem in this case.Looks like I'm going to have to go with a trigger.
Thanks everyone!
Why would you want the computed column to be updated?? That kind of defeat the purpose!
Remy,
I think the OP originally wanted to use the same kind of syntax for a DEFAULT and the column could be updated later.
I was just pointing out that CASE statements like that could be used for computed columns, which is not what the OP was originally requesting help on.
Todd Fifield
August 2, 2011 at 12:28 pm
I C.
BTW it's Remi
😉
August 2, 2011 at 12:59 pm
Thanks for the catch. I thought that was a restriction, but apparently my memory is fading.
August 2, 2011 at 4:15 pm
Ninja's_RGR'us (8/2/2011)
I C.BTW it's Remi
😉
Oops! Got it right now.
Todd
August 2, 2011 at 4:18 pm
Steve Jones - SSC Editor (8/2/2011)
Thanks for the catch. I thought that was a restriction, but apparently my memory is fading.
Father time eventually gets around to us all :crying:
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply