Computed Column is not allowed to be used in another computed-column definition

Question

I get Computed Column is not allowed to be used in another computed-column definition error message when i tried to use a computed field in another computed field. is there any workaround to accomplish this?

Computed field1

ALTER TABLE DBO.[ASSy] ADD [CALC STOCK NO] AS (

CASE 
WHEN isnull([DIRECTIONAL TREAD],'') ='YES RIGHT' THEN isnull([STOCK NO],'')+'R'
WHEN isnull([DIRECTIONAL TREAD],'') ='YES LEFT' THEN isnull([STOCK NO],'')+'L'
ELSE isnull([STOCK NO],'')
end 
)

Below is the second computed field. when i execute the script i get

Msg 1759, Level 16, State 0, Line 5 Computed column 'CALC STOCK NO' in table 'ASSy' is not allowed to be used in another computed-column definition.

ALTER TABLE dbo.[ASSy] ADD [PN & DESCRIPTION] AS (ISNULL([CALC STOCK NO],'')+ ', '+ISNULL([TIRE SIZE],'')+', '+ ISNULL([BH SPEC],''))

Answer 1

As per Computed Columns documentation:

A computed column is computed from an expression that can use other columns in the same table. The expression can be a noncomputed column name, constant, function, and any combination of these connected by one or more operators. The expression cannot be a subquery.

A computed column cannot reference another computed column. You have these options:

1. Create a second computed column that includes the same logic as the first computed column (which cannot be referenced).
2. Replace the first computed column with an ordinary column:

ALTER TABLE DBO.[ASSy] DROP COLUMN [CALC STOCK NO]
ALTER TABLE ADD [CALC STOCK NO] VARCHAR(100) NULL
UPDATE TABLE t
    SET [CALC STOCK NO] = 
        CASE 
        WHEN isnull([DIRECTIONAL TREAD],'') ='YES RIGHT' THEN isnull([STOCK NO],'')+'R'
        WHEN isnull([DIRECTIONAL TREAD],'') ='YES LEFT' THEN isnull([STOCK NO],'')+'L'
        ELSE isnull([STOCK NO],'')
        END
FROM [CALC STOCK NO] t

3. Create a VIEW on top of the table and implement your second column in it:

CREATE VIEW DBO.[vASSy]
AS
SELECT   t.*
        ,[PN & DESCRIPTION] = (ISNULL([CALC STOCK NO],'')+ ', '+ISNULL([TIRE SIZE],'')+', '+ ISNULL([BH SPEC],''))
FROM DBO.[ASSy] t

Answer 2

Not allowed.

Workarounds are to repeat the entire code in the other column's definition or encapsulate the shared code in a scalar UDF or pull the logic up to a view (which is typically sufficient if it's not a persisted column for indexing purposes)