Search code examples
sql-serverssassql-server-2017calculated-field

Declare a local variable in named calculation expression

I would like to create a named calculation field for age, and I want to declare a local variable inside an expression like the following, but it doesn't not work :

DECLARE
@age INT;

@age=DateDiff("yyyy",DATE_NAIS,getdate());

CASE WHEN @age<=10 THEN  1
WHEN @age>10 AND @age<=20 THEN 2
WHEN @age>20 AND @age<=35 THEN 3
ELSE 4
END
Solution

  • Correct, you cannot DECLARE a variable in the context of an expression in a SELECT, like you are trying to do.

    You have a few different options, one of which digital.aaron gave in his comments.

    Another is to create it as an artificial column in a CTE, and then select from the CTE instead of the table:

    WITH cte AS (SELECT *, DateDiff("yyyy",DATE_NAIS,getdate()) AS age FROM MyTable)
SELECT CASE 
  WHEN age<=10 THEN  1
  WHEN age>10 AND age<=20 THEN 2
  WHEN age>20 AND age<=35 THEN 3
  ELSE 4
END AS SomeColumn
FROM cte