IIF with Multiple Case Statements for Computed Column

Question

I'm trying to add this as a Formula (Computed Column) but I'm getting an error message saying it is not valid.

Can anyone see what is wrong with the below formula?

IIF
(
    select * from Config where Property = 'AutomaticExpiry' and Value = 1,
    case when [ExpiryDate] IS NULL OR sysdatetimeoffset()<[ExpiryDate] then 1 else 0 end,
    case when [ExpiryDate] IS NULL then 1 else 0 end
)

First argument of the IIF function must be a boolean condition, not a query. — CrimsonKing, Dec 05 '18 at 10:55
I wouldn't use a computed column for this (you can't, unless you create a UDF; https://stackoverflow.com/questions/13488822/create-computed-column-using-data-from-another-table). Don't create a UDF for this; nearly always a bad idea. Use a view, or a trigger — Caius Jard, Dec 05 '18 at 10:56

score 3 · Answer 1 · answered Dec 05 '18 at 10:56

3

From BOL: ALTER TABLE computed_column_definition

computed_column_expression Is an expression that defines the value of a computed column. A computed column is a virtual column that is not physically stored in the table but is computed from an expression that uses other columns in the same table. For example, a computed column could have the definition: cost AS price * qty. The expression can be a noncomputed column name, constant, function, variable, and any combination of these connected by one or more operators. The expression cannot be a subquery or include an alias data type.

answered Dec 05 '18 at 10:56

Denis Rubashkin

2,151
1
9
14

Thanks, seems as though it can't be done then which is why I was struggling to get it working in the first place. – Philip Dec 05 '18 at 11:01
Do you know a way around this using another method? – Philip Dec 05 '18 at 11:06

gotqn · Answer 2 · 2018-12-05T11:26:58.773

Wrap the login in function. Something like this:

CREATE FUNCTION [dbo].[fn_CustomFunction]
(
    @ExpireDate DATETIME2
) 
RETURNS BIT
AS
BEGIN;

    DECLARE @Value BIT = 0;

    IF EXISTS(select * from Config where Property = 'AutomaticExpiry' and Value = 1)
    BEGIN;
        SET @Value = IIF (sysdatetimeoffset()< @ExpireDate, 1, 0)
        RETURN @value;
    END;

    RETURN IIF(@ExpireDate IS NULL, 1, 0);

END;

GO

--DROP TABLE IF EXISTS dbo.TEST;

CREATE TABLE dbo.TEST
(
    [ID] INT IDENTITY(1,1)
   ,[ExpireDate] DATETIME2
   ,ComputeColumn AS [dbo].[fn_CustomFunction] ([ExpireDate])
)

GO

INSERT INTO dbo.TEst (ExpireDate)
VALUES ('2019-01-01')
      ,('2018-01-01')
      ,(NULL);

SELECT *
FROM dbo.Test;

What changes were made, and how does it solve the problem? – kchason Dec 05 '18 at 10:55 — kchason, Dec 05 '18 at 10:55

Caius Jard · Answer 3 · 2018-12-05T11:48:05.010

Youre trying to do something, what we're not quite sure - you've made a classic XY problem mistake.. You have some task, like "implement auto login expiry if it's on in the prefs table" and you've devised this broken solution (use a computed column/IIF) and have sought help to know why it's broken.. It's not solving the actual core problem.

In transitioning from your current state to one where you're solving the problem, you can consider:

As a view:

CREATE VIEW yourtable_withexpiry AS
SELECT 
  *, 
  CASE WHEN [ExpiryDate] IS NULL OR config.[Value] = 1 AND SysDateTimeOffset() < [ExpiryDate] THEN 1 ELSE 0 END AS IsValid
FROM 
  yourtable
  LEFT JOIN 
  config
  ON config.property = 'AutomaticExpiry'

As a trigger:

CREATE TRIGGER trg_withexpiry ON yourtable 
AFTER INSERT OR UPDATE  
AS  
    IF NOT EXISTS(select * from Config where Property = 'AutomaticExpiry' and Value = 1)
    RETURN;

    UPDATE yourtable SET [ExpiryDate] = DATE_ADD(..some current time and suitable offset here..)
    FROM yourtable y INNER JOIN inserted i ON y.pk = i.pk;

END;

But honestly, you should be doing this in your front end app. It should be responsible for reading/writing session data and keeping things up to date and kicking users out if they're over time etc.. Using the database for this is, to a large extent, putting business logic/decision processing into a system that shouldn't be concerned with it..

Have your front end language implement a code that looks up user info upon some regular event (like page navigation or other activity) and refreshes the expiry date as a consequence of the activity, only if the expiry date isn't passed. For sure too keep the thing valid if the expiry is set to null if you want a way to have people active forever (or whatever)

IIF with Multiple Case Statements for Computed Column

3 Answers3