Pfthb

I have a table X in SQL Server which includes a column called Type varchar(10) with 15 potential values: AAA, BBB1, BBB2, CCC, ..., and another column called IfExists bit.

In the same DB, I have 15 different views, a view for each Type, including X_ID as well. Examples of view names:

View_ReportingAAA
View_ReportingBBB1
View_ReportingBBB2
View_ReportingCCC
....

Usually what I do is to see the Type of the record by using its X_ID provided to me in Table X and use a select query to the related view to get some data.

What I need to do is now to create a trigger for Table X to set 1 for the value of IfExists field if there is a record in the related view for the record id X_ID.

My question is: What is the best way to find and use the related view name for this purpose by using a value of a field in the main table?

I am using the code below for this, but I am not sure if it is the most efficient way to handle this. Any help or advice would be appreciated.

declare @ifExists int = 0;
declare @sql nvarchar(max) = 'set @ifExists = (Select count(*) from View_Reporting';

declare @tablename nvarchar(max) = (Select Type from X where X_ID = @X_ID)

set @sql = @sql + @tablename + ' where V_xid = @X_ID)';
EXECUTE sp_executesql @sql

if (@ifExists > 0)
begin
 update X set IfExists = 1 where X_ID = @X_ID
end

Maybe this could be easier:

declare @ifExists bit
select @ifExists=count(*) from INFORMATION_SCHEMA.VIEWS
 where TABLE_NAME='View_Reporting'+(select [Type] from X where X_ID=@X_ID)
update X set IfExists=@IfExists where X_ID=@X_ID and IfExists<>@IfExists