Can I throw an error in a computed column select case?

Question

I have a "case when" in my computed column, but I can't figure out how to throw an exception.

Here's sample code that doesn't work...

CREATE TABLE OrderDetail
( OrderID INT
, ProductID INT
, Qty INT
, OrderDate DATETIME
, ShipDate DATETIME
, STATUS AS CASE
       WHEN shipdate is NULL AND orderdate < DATEADD( dd, -7, GETDATE()) THEN 3
       WHEN shipdate is NOT NULL THEN 2
       ELSE RAISERROR ('Error in shipdate',-1,-1)
   end
 )
GO

But it is invalid.

Isn't it possible to raise an error in computed columns?

Answer 1

This can't be done like this. A case expression can't be used as a flow control. It's specifically documented:

The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures.

You can add a check constraint to the table, but that would not allow you to raise your own custom error:

CREATE TABLE OrderDetail
( 
      OrderID INT
    , ProductID INT
    , Qty INT
    , OrderDate DATETIME
    , ShipDate DATETIME
    , STATUS AS CASE
                    WHEN shipdate is NULL AND orderdate < DATEADD( dd, -7, GETDATE()) THEN 3
                    WHEN shipdate is NOT NULL THEN 2
                    ELSE NULL
                END
    , CONSTRAINT Chk_OrderDetails_Dates CHECK(
        shipdate IS NOT NULL 
        OR orderdate < DATEADD( dd, -7, GETDATE())
    )

)
GO

Or you can use triggers - instead of insert and instead of update to only allow rows where the dates are valid. Personally, I would go with a check constraint - It's safe and more easier to write and maintain.