Using EXEC inside SELECT statement in SQL Server

If you can modify your Stored Procedure GetStringForIsActive to return TableName, TableFKey and IsActive, you can use a cursor to get execute it for each row and add the results to a temp table.

ie:

exec GetStringForIsActive 'TableName', 'TableFKey'
returns
select [TableName], [TableFKey], IsActive from [TableName] where PKID= cast([TableFKey] as int)

The code would be like this:

declare @TableName nvarchar(50)
declare @TableFKey nvarchar(50)
declare @Results table (TableName nvarchar(50), TableFKey nvarchar(50), IsActive bit)

declare TableCursor cursor fast_forward for
   select TableName, TableFKey from MasterTableForLanguage
   
open TableCursor

fetch next from TableCursor into @TableName, @TableFKey

if @@FETCH_STATUS <> -1
   print 'MasterTableForLanguage check'

while (@@FETCH_STATUS <> -1)
begin


   insert into @Results
   exec GetStringForIsActive @TableName, @TableFKey

   fetch next from TableCursor into @TableName, @TableFKey

end
close TableCursor
deallocate TableCursor

select * from @Results

use Functions instead of Stored procedures in SELECT clause.

Edited:

create that function

CREATE FUNCTION function1 (@TableName nvarchar(10),@TableFKey nvarchar(10))
RETURNS nvarchar(100) AS  
BEGIN 
    -- do whatever here
END

then

SELECT distinct 
     MTMain.[TableName],
     MTMain.[TableFKey], 
     function1(MTMain.[TableName],MTMain.[TableFKey])
 FROM
     [MasterTableForLanguage] MTMain

Does this make sense?

Well, I think to answer the full question, I don't believe a stored procedure would EXEC a SELECT statement, it would simply perform the SELECT.

You EXEC your current proc and pass it vars, and it returns a value BASED ON the select statement it runs. It's not EXEC'ing that statement, simply performing a select. I have several stored procs I use daily in some SQL agent processes, they all perform selects to query various tables, and none of them call an EXEC to perform those actions. This is my own example:

CREATE PROCEDURE [myproc]
    @job_ident      INT
AS
BEGIN
    SET NOCOUNT ON;

    ...

    SELECT TOP(1) @filter_type = filter_type FROM [place] WHERE [ident] = @job_ident

    ...
 END

As mentioned previously, the most effective way to perform your query would be to perform that select inside a function, something similar to this I think will do:

CREATE FUNCTION ThisFunction (
    @TableName nvarchar(10),
    @TableFKey nvarchar(10)
    )
    RETURNS nvarchar(100)
    AS  
        BEGIN
            RETURN 
            (
                select IsActive from [TableName] where PKID= cast([TableFKey] as int)
            )
        END

You could then do exactly as you want...

SELECT distinct 
     MTMain.[TableName],
     MTMain.[TableFKey], 
     ThisFunction(MTMain.[TableName],MTMain.[TableFKey])
 FROM
     [MasterTableForLanguage] MTMain