Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Can I use a case statement in a column default? Expand / Collapse
Author
Message
Posted Friday, July 29, 2011 3:16 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 12, 2011 7:57 PM
Points: 17, Visits: 89
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?
Post #1151332
Posted Monday, August 1, 2011 12:48 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:26 PM
Points: 13,093, Visits: 12,573
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1152082
Posted Monday, August 1, 2011 12:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:15 PM
Points: 31,210, Visits: 15,654
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1152092
Posted Tuesday, August 2, 2011 12:08 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, September 28, 2014 1:41 PM
Points: 959, Visits: 2,888
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
( RecID INT
, 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
Post #1152848
Posted Tuesday, August 2, 2011 12:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 12, 2011 7:57 PM
Points: 17, Visits: 89
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!
Post #1152867
Posted Tuesday, August 2, 2011 12:18 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
Can be persisted or indexed, so no "need" for trigger here. Tho I might choose that option as well.
Post #1152874
Posted Tuesday, August 2, 2011 12:19 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
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!
Post #1152876
Posted Tuesday, August 2, 2011 12:26 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, September 28, 2014 1:41 PM
Points: 959, Visits: 2,888
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
Post #1152885
Posted Tuesday, August 2, 2011 12:28 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
I C.

BTW it's Remi

Post #1152890
Posted Tuesday, August 2, 2011 12:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:15 PM
Points: 31,210, Visits: 15,654
Thanks for the catch. I thought that was a restriction, but apparently my memory is fading.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1152927
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse