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: 295

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

Related posts about sql-server

Related posts about computed-columns