Post reply

Default value based on case?

  • July 1, 2008 at 11:02 am

    #194595

    Am I doing something wrong, or can you not case a default value?

    CREATE TABLE #OrphanedUsers

    (

    [OrphanedUserName]SYSNAME

    ,[Type]VARCHAR(64)

    ,[Repair]BIT

    ,[Results]AS (CASE [Repair] WHEN 1 THEN 'Possible login found. Repair can be attempted.' ELSE 'No matching login found. User is orphaned.' END AS [Login])

    )

    Thanks

  • July 1, 2008 at 11:15 am

    #836834

    I don't think this is allowed directly. Maybe with a UDF?

  • July 1, 2008 at 11:32 am

    #836842

    You can use CASE in calculated columns (which is what this is). The problem isn't the first "AS", it's the second one. You can't alias a computed column in a table definition.

    Just get rid of "AS [Login]" and you'll be fine.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • July 1, 2008 at 12:03 pm

    #836860

    GSquared (7/1/2008)

    You can use CASE in calculated columns (which is what this is). The problem isn't the first "AS", it's the second one. You can't alias a computed column in a table definition.

    Just get rid of "AS [Login]" and you'll be fine.

    Perfect! Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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