Can I use a case statement in a column default?

Question

Home
Forums
SQL Server 2005
SQL Server 2005 General Discussion
Can I use a case statement in a column default?

Can I use a case statement in a column default?

srferson-957148
SSC-Addicted

Points: 483
More actions
July 29, 2011 at 3:16 pm

#145807

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?


          
          
           
            
             
              srferson-957148
             
            
            
             
             
             
              
              
             
             
              SSC-Addicted
             
             
              Points: 483
             
             
              
               
                
                 More actions
                
               
              
              
               
               
              
              
              
             
             
             
            
            
            
            
             
             
             
              
               
                August 2, 2011 at 12:14 pm
               
              
              
               
               
               
                #1362464
               
              
              
              
              
               
                
               
              
             
             
             
             
             
             
              
               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
             
            
            
             
             
             
              
              
             
             
              SSC Guru
             
             
              Points: 294069
             
             
              
               
                
                 More actions
                
               
              
              
               
               
              
              
              
             
             
             
            
            
            
            
             
             
             
              
               
                August 2, 2011 at 12:18 pm
               
              
              
               
               
               
                #1362469
               
              
              
              
              
               
                
               
              
             
             
             
             
             
             
              
               Can be persisted or indexed, so no "need" for trigger here. Tho I might choose that option as well.
              
             
             
            
           
          
          
          
          
          
           
            
             
              Ninja's_RGR'us
             
            
            
             
             
             
              
              
             
             
              SSC Guru
             
             
              Points: 294069
             
             
              
               
                
                 More actions
                
               
              
              
               
               
              
              
              
             
             
             
            
            
            
            
             
             
             
              
               
                August 2, 2011 at 12:19 pm
               
              
              
               
               
               
                #1362471
               
              
              
              
              
               
                
               
              
             
             
             
             
             
             
              
               
                
                 
                  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
             
            
            
             
             
             
              
              
             
             
              SSCrazy Eights
             
             
              Points: 9655
             
             
              
               
                
                 More actions
                
               
              
              
               
               
              
              
              
             
             
             
            
            
            
            
             
             
             
              
               
                August 2, 2011 at 12:26 pm
               
              
              
               
               
               
                #1362479
               
              
              
              
              
               
                
               
              
             
             
             
             
             
             
              
               
                
                 
                  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
             
            
            
             
             
             
              
              
             
             
              SSC Guru
             
             
              Points: 294069
             
             
              
               
                
                 More actions
                
               
              
              
               
               
              
              
              
             
             
             
            
            
            
            
             
             
             
              
               
                August 2, 2011 at 12:28 pm
               
              
              
               
               
               
                #1362484
               
              
              
              
              
               
                
               
              
             
             
             
             
             
             
              
               I C.
              
              
               BTW it's Rem
               
                
                 i
                
               
              
              
               😉
              
             
             
            
           
          
          
          
          
          
           
            
             
              Steve Jones - SSC Editor
             
            
            
             
             
             
              
              
             
             
              SSC Guru
             
             
              Points: 736394
             
             
              
               
                
                 More actions
                
               
              
              
               
               
              
              
              
             
             
             
            
            
            
            
             
             
             
              
               
                August 2, 2011 at 12:59 pm
               
              
              
               
               
               
                #1362515
               
              
              
              
              
               
                
               
              
             
             
             
             
             
             
              
               Thanks for the catch. I thought that was a restriction, but apparently my memory is fading.
              
             
             
            
           
          
          
          
          
          
           
            
             
              tfifield
             
            
            
             
             
             
              
              
             
             
              SSCrazy Eights
             
             
              Points: 9655
             
             
              
               
                
                 More actions
                
               
              
              
               
               
              
              
              
             
             
             
            
            
            
            
             
             
             
              
               
                August 2, 2011 at 4:15 pm
               
              
              
               
               
               
                #1362747
               
              
              
              
              
               
                
               
              
             
             
             
             
             
             
              
               
                
                 
                  Ninja's_RGR'us (8/2/2011)
                 
                
                
                I C.
                
                 BTW it's Rem
                 
                  
                   i
                  
                 
                
                
                 😉
                
               
               
               
              
              
               Oops! Got it right now.
              
              
               Todd
              
             
             
            
           
          
          
          
          
          
           
            
             
              tfifield
             
            
            
             
             
             
              
              
             
             
              SSCrazy Eights
             
             
              Points: 9655
             
             
              
               
                
                 More actions
                
               
              
              
               
               
              
              
              
             
             
             
            
            
            
            
             
             
             
              
               
                August 2, 2011 at 4:18 pm
               
              
              
               
               
               
                #1362752
               
              
              
              
              
               
                
               
              
             
             
             
             
             
             
              
               
                
                 
                  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

Answer 1

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/

Answer 2

Steve Jones - SSC Editor

SSC Guru

Points: 736394

August 1, 2011 at 12:59 pm

#1361791

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.

Answer 3

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

Answer 4

srferson-957148

SSC-Addicted

Points: 483

August 2, 2011 at 12:14 pm

#1362464

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!

Answer 5

Ninja's_RGR'us

SSC Guru

Points: 294069

August 2, 2011 at 12:18 pm

#1362469

Can be persisted or indexed, so no "need" for trigger here. Tho I might choose that option as well.

Answer 6

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!

Answer 7

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