formula for computed column based on different table's column

sql sql-server-2008 calculated-columns

Adnan M. TÜRKEN · May 4, 2010 · Viewed 71.8k times · Source

Consider this table: c_const

 code  |  nvalue
 --------------
 1     |  10000
 2     |  20000

and another table t_anytable

 rec_id |  s_id  | n_code
 ---------------------
 2      |  x     | 1

The goal is to have s_id be a computed column, based on this formula:

 rec_id*(select nvalue from c_const where code=ncode)

This produces an error:

Subqueries are not allowed in this context. Only scalar expressions are allowed.

How can I calculate the value for this computed column using another table's column as an input?

Answer

You could create a user-defined function for this:

CREATE FUNCTION dbo.GetValue(@ncode INT, @recid INT)
RETURNS INT
AS 
   SELECT @recid * nvalue 
   FROM c_const 
   WHERE code = @ncode

and then use that to define your computed column:

ALTER TABLE dbo.YourTable
   ADD NewColumnName AS dbo.GetValue(ncodeValue, recIdValue)