Click here to monitor SSC
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 (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
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?
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
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)
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36048 Visits: 18736
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
SSC Eights!
SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)

Group: General Forum Members
Points: 991 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 (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
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!
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 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-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 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
SSC Eights!
SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)

Group: General Forum Members
Points: 991 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-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

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

BTW it's Remi

;-)
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36048 Visits: 18736
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