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
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 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
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63634 Visits: 17966
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 (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: Administrators
Points: 148490 Visits: 19444
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
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2531 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
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 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
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68541 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
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68541 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
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2531 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
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

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

BTW it's Remi

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

Group: Administrators
Points: 148490 Visits: 19444
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