formula for computed column based on different table's column

Question

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 1

You could create a user-defined function for this:

CREATE FUNCTION dbo.GetValue(INT @ncode, INT @recid)
RETURNS IN
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)

Answer 2

This seems to be more of a job for views (indexed views, if you need fast lookups on the computed column):

CREATE VIEW AnyView
WITH SCHEMABINDING
AS

SELECT a.rec_id, a.s_id, a.n_code, a.rec_id * c.nvalue AS foo
FROM AnyTable a
INNER JOIN C_Const c
    ON c.code = a.n_code

This has a subtle difference from the subquery version in that it would return multiple records instead of producing an error if there are multiple results for the join. But that is easily resolved with a UNIQUE constraint on c_const.code (I suspect it's already a PRIMARY KEY).

It's also a lot easier for someone to understand than the subquery version.

You can do it with a subquery and UDF as marc_s has shown, but that's likely to be highly inefficient compared to a simple JOIN, since a scalar UDF will need to be computed row-by-row.