How to call dynamic query in Mssql function?

Question

SQL

The task is to create a function that will return the top students. The problem is that you cannot use Exec in a function, and without a dynamic query, there is no way to do it because of @tab_name.

spoiler

GO
CREATE FUNCTION TopStud(@numk int)
RETURNS varchar(max)
BEGIN
  DECLARE @c int, @stud varchar(max), @tb_name varchar(max), @tb_sql varchar(max);
  SET @c = (SELECT COUNT(name) FROM tempdb.sys.tables WHERE name LIKE '##tb_'+ '[0-9]'+ '[0-9]'+ '[0-9]'+'_'+ CAST((@numk * 2 - 1) as varchar)
    OR name LIKE '##tb_'+ '[0-9]'+ '[0-9]'+ '[0-9]'+'_'+ CAST((@numk * 2) as varchar));
  WHILE @c > 0
    BEGIN
      SET @tb_name = (SELECT name FROM (SELECT name,ROW_NUMBER() over (order by name) as rnum FROM tempdb.sys.tables WHERE name LIKE '##tb_'+ '[0-9]'+ '[0-9]'+ '[0-9]'+'_'+ CAST((@numk * 2 - 1) as varchar)
      OR name LIKE '##tb_'+ '[0-9]'+ '[0-9]'+ '[0-9]'+'_'+ CAST((@numk * 2) as varchar)) as t1 WHERE rnum = @c);

      SET @tb_sql = 'SELECT DISTINCT TOP(5) CONCAT(Id, FIO, CAST(SUM(Mark)/COUNT(Mark) as varchar)) FROM Students 
      JOIN Discipline ON DGroupNum = GroupNum 
      JOIN '+ @tb_name+' ON Id = IdStud WHERE DSemestr = '+CAST((@numk * 2 - 1) as varchar)+' OR DSemestr = '+CAST((@numk * 2) as varchar)+' GROUP BY Id, FIO;'
      EXEC(@tb_sql);
      SET @c = @c - 1; 
    END;	
  RETURN @stud;
END;

Answer 1

The problem is that you can't use Exec in a function.

Too lazy to check if it works?EXECUTE sp_executesql @tb_sql

Answer 2

No way. But the question is why the function is needed. What is the reason for such a need? Why not a procedure?
If it’s completely pinned down, then CLR