Using EXEC inside SELECT statement in SQL Server
sql, sql-server
Solution
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
Problem
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.