How to define a “complicated” ComputedColumn in SQ

Question

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!

Answer 1

You can do this in an alter table statement:

alter table my_table_name
  add Status as 
    case 
      when (DateTime1 IS NULL and DateTime2 IS NULL) then 0
      when (DateTime1 IS NULL and DateTime2 IS NOT NULL) then 1
      when (DateTime1 IS NOT NULL and DateTime2 IS NULL) then 2
      else 3
    end

Edited to fix dumb copy-and-paste syntax error

Answer 2

You can use a trigger to ensure a column value upon insert or update.

Answer 3

You can always also use a user-defined function for this - wrap your "complicated" code into an UDF, and use that to define your computed column:

CREATE FUNCTION dbo.GetStatus(@DateTime1 DATETIME, @DateTime2 DATETIME)
RETURNS INT
AS BEGIN
    DECLARE @Result INT

    IF (@DateTime1 IS NULL AND @DateTime2 IS NULL)
       SET @Result = 0
    ELSE IF (@DateTime1 IS NULL AND @DateTime2 IS NOT NULL)
       SET @Result = 1
    ELSE IF (@DateTime1 IS NOT NULL AND @DateTime2 IS NULL)
       SET @Result = 2
    ELSE
       SET @Result = 3

    RETURN @Result
END

and then you define your computed column as:

ALTER TABLE dbo.YourTable
    ADD Status AS dbo.GetStatus(DateTime1, DateTime2)