SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can I use a case statement in a column default?


Can I use a case statement in a column default?

Author
Message
srferson-957148
srferson-957148
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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?
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25969 Visits: 17525
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.

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)
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62156 Visits: 19102
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
My Blog: www.voiceofthedba.com
tfifield
tfifield
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1289 Visits: 2890
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
srferson-957148
srferson-957148
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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!
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28797 Visits: 9671
Can be persisted or indexed, so no "need" for trigger here. Tho I might choose that option as well.
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28797 Visits: 9671
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!
tfifield
tfifield
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1289 Visits: 2890
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
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28797 Visits: 9671
I C.

BTW it's Remi

;-)
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62156 Visits: 19102
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
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search