How to define a "complicated" ComputedColumn in SQL Server?
        Posted  
        
            by Slauma
        on Stack Overflow
        
        See other posts from Stack Overflow
        
            or by Slauma
        
        
        
        Published on 2010-04-07T21:26:19Z
        Indexed on 
            2010/04/07
            21:33 UTC
        
        
        Read the original article
        Hit count: 354
        
sql-server
|computed-columns
SQL Server Beginner question:
I'm trying to introduce a computed column in SQL Server (2008). In the table designer of SQL Server Management Studio I can do this, but the designer only offers me one single edit cell to define the expression for this column. Since my computed column will be rather complicated (depending on several database fields and with some case differentiations) I'd like to have a more comfortable and maintainable way to enter the column definition (including line breaks for formatting and so on).
I've seen there is an option to define functions in SQL Server (scalar value or table value functions). Is it perhaps better to define such a function and use this function as the column specification? And what kind of function (scalar value, table value)?
To make a simplified example:
I have two database columns:
DateTime1 (smalldatetime, NULL)
DateTime2 (smalldatetime, NULL)
Now I want to define a computed column "Status" which can have four possible values. In Dummy language:
if (DateTime1 IS NULL and DateTime2 IS NULL)
    set Status = 0
else if (DateTime1 IS NULL and DateTime2 IS NOT NULL)
    set Status = 1
else if (DateTime1 IS NOT NULL and DateTime2 IS NULL)
    set Status = 2
else
    set Status = 3
Ideally I would like to have a function GetStatus() which can access the different column values of the table row which I want to compute the value of "Status" for, and then only define the computed column specification as GetStatus() without parameters.
Is that possible at all? Or what is the best way to work with "complicated" computed column definitions?
Thank you for tips in advance!
© Stack Overflow or respective owner