Aug 4, 2013

The name "" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.


Suppose we want to create a table tblEmp where default value of Age column of any employee will be diffrence of current date and his or her date of birth. For example:

CREATE TABLE tblEmp(
     EmpID BIGINT PRIMARY KEY,
     EmpName VARCHAR(500),
     DOB DATETIME,
     Age INT DEFAULT(DATEDIFF(YEAR,DOB,GETDATE()))
)

Now if we will execute following sql statement, we will get error message like:

The name "" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Cause: In the expression of default value of any column we cannot use column of that table.

Solution:

Instead of default we shoude create a compute column Age like this:

CREATE TABLE tblEmp(
     EmpID BIGINT PRIMARY KEY,
     EmpName VARCHAR(500),
     DOB DATETIME,
     Age AS DATEDIFF(YEAR,DOB,GETDATE())
)

No comments:

Post a Comment