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.

you should use functions instead of stored procedure if you want to use them inside select clause — Boomer, Jun 27 '12 at 09:22
Post your proc. I don't know what you do inside the proc, maybe you can change it to a function or even better solve it as subselects. — YvesR, Jun 27 '12 at 09:33
I am using the stored procedure for execution of a string. I need to do it this way because i need to use column [TableName] as table and check whether [TableFKey] value exits in particular column of that table. — Ruchit Rami, Jun 27 '12 at 09:36

score 2 · Answer 1 · answered Jun 27 '12 at 10:12

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

Boomer · Answer 2 · 2012-06-27T09:46:41.880

0

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?

edited Jun 27 '12 at 09:46

answered Jun 27 '12 at 09:24

Boomer

1,468
1
15
19

3

There are many things that can't be done in a function that can be done in a proc though. – Jon Egerton Jun 27 '12 at 09:27
1

you still can't call stored procedure from function. try to convert your stored procedure to a function – Boomer Jun 27 '12 at 09:38
1

I need to use EXEC inside the function. check my comment on the question. – Ruchit Rami Jun 27 '12 at 09:41
Why do you need to use EXEC? A function does exactly what you're trying to do – Sergio Rosas Jun 27 '12 at 09:53
@RuchitRami anyways if that doesn't help you, check that link http://stackoverflow.com/questions/6344880/execute-stored-procedure-from-a-function – Boomer Jun 27 '12 at 09:56
5

-1 this answer amounts to "How do I get to X from here?" "Oh I wouldn't start from here." Often the sproc is provided by someone else, so unless you hack their database, this is of no use. – Corvus Nov 23 '15 at 20:35

user2746894 · Answer 3 · 2017-05-05T17:20:09.723

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

Using EXEC inside SELECT statement in SQL Server

3 Answers3