Can I use a case statement in a column default?

  • 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?

  • 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/

  • 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 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

  • 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!

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

  • 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!

  • 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

  • I C.

    BTW it's Remi

    😉

  • Thanks for the catch. I thought that was a restriction, but apparently my memory is fading.

  • Ninja's_RGR'us (8/2/2011)


    I C.

    BTW it's Remi

    😉

    Oops! Got it right now.

    Todd

  • 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 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply