The way I understand it you have two tables with schema like this:
table Control (Number int, Function nvarchar, Enable bit)
table Repository (Function nvarchar, TableName nvarchar)
Control and Repositories are related via Function column.
You also have a number of other tables and names of those tables are saved in Repositories tables. All those tables have ID column.
You want to get those table names based on a number and then select from all those tables by their ID column.
If that indeed is what you are trying to do, code bellow should be enough to solve your problem.
declare
-- arguments
@id int = 123,
@number int = 123456,
-- helper variables we'll use along the way
@function nvarchar(4000),
@tableName nvarchar(256),
@query nvarchar(4000)
-- create cursor to iterate over every returned row one by one
declare cursor #tables readonly fast_forward
for
select
c.Function,
r.TableName
from [Control] as c
join [Repository] as r on r.Function = c.Function
where c.Number = @number
and c.Enable = 1
-- initialise cursor
open #tables
-- get first row into variables
fetch next from #tables
into @function, @tableName
-- will be 0 as long as fetch next returns new values
while @@fetch_status = 0
begin
-- build a dynamic query
set @query = 'select * from ' + @tableName + ' where ID = ' + @id
-- execute dynamic query. you might get permission problems
-- dynamic queries are best to avoid, but I don't think there's another solution for this
exec(@query)
-- get next row
fetch next from #tables
into @function, @tableName
end
-- destroy cursor
close #tables
deallocate #tables
