Using EXEC inside SELECT statement in SQL Server

Question

I need to use exec inside of select clause. The query for the exec is created according to the columns of the table to on which select clause if used. What i want to do is something like following:

 SELECT distinct 
     MTMain.[TableName],
     MTMain.[TableFKey], 
     (select IsActive (exec GetStringForIsActive MTMain.[TableName],MTMain.[TableFKey]))
 FROM
     [MasterTableForLanguage] MTMain

Here, GetStringForIsActive is the stored procedure I want to execute for every row selected from MasterTableForLanguage.
The stored procedure will use EXEC to execute the following string

select IsActive from [TableName] where PKID= cast([TableFKey] as int)

TableName and TableFKey will be inputs of the stored procedure.

Answer 1

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 TaleCursor
deallocate TableCursor

select * from @Results