You can't use SELECT statements in a computed column, but you can convert the SELECT into a user-defined function to return the correct value for use in your table:CREATE FUNCTION dbo.FindAllowedValues(@Category VARCHAR(20)) RETURNS BIT AS
BEGIN
DECLARE @ErrorCode BIT=1;
IF EXISTS(SELECT * FROM AllowedValues WHERE ColumnA = @Category) BEGIN
SET @ErrorCode=0;
END
RETURN @ErrorCode;
END
GO
-- add computed column
ALTER TABLE [Table] ADD ValidCategory AS dbo.FindAllowedValues(Category);
Be advised this can cause a significant performance hit if you SELECT the computed column, as it will essentially become a cursor on every row.
return a count of records in persisted field
Posted - 07/03/2012 : 09:57:18
