return a count of records in persisted field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

return a count of records in persisted field

Author

Topic

aSystemOverload
Starting Member

1 Post

Posted - 2012-07-03 : 09:57:18

The following is required due to records being entered by 3rd parties in a web application.

Certain columns (such as Category) require validation including the one below. I have a table OtherTable with the allowed values.

I need to identify how many occurrences (ie: IF) there are of the current table's column's value in a different table's specified column. If there are no occurrences this results in a flagged error '1', if there are occurrences, then it results in no flagged error '0'.

If `Category` can be found in `OtherTable.ColumnA` then return 0 else 1

It has been suggested that I use:

SELECT CASE WHEN EXISTS(
SELECT NULL
FROM AllowedValues av
WHERE av.ColumnA = Category
) THEN 0 ELSE 1 END AS ErrorCode
, Category
FROM [Table]

which works stand alone (returning the required codes for every single row), but when I place it in a persisted field it throws a "Incorrect Syntax near SELECT and Incorrect Syntax near NULL"

Does anyone have any pointers?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-07-03 : 10:44:10

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources