Posted by Hugo Kornelis on 09/10/06 21:54

On 10 Sep 2006 14:41:16 -0700, Dot Net Daddy wrote:

>Hello,
>
>I want to assign a column a computed value, which is the multiplication
>
>of a value from the table within and a value from another table.
(snip)

Hi Dot Net Daddy,

Here's what Books Online has to say:

>computed_column_expression
>Is an expression that defines the value of a computed column. (...) 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 contain alias data
>types.

So you can't embed a subquery in a computed column expression, but
user-defined functions are okay.

CREATE dbo.MyUDF (@A_ID AS int)
RETURNS int
AS
RETURN (SELECT column3 FROM B WHERE B.ID = @A_ID)
go

And then defined the computed column as

ALTER TABLE MyTable
ADD NewColumn AS column1 * dbo.MyUDF(ID)
go

--
Hugo Kornelis, SQL Server MVP

• Next in forum: Re: Computed Column Specification
• Prev in forum: Re: Creating a dynamic Global Temp Table within a stored procedure
• Thread view: Re: Computed Column Specification

[Reply to this message]