How to execute a stored procedure inside a select query

Question

SELECT col1,
       col2,
       col3,

EXEC GetAIntFromStoredProc(T.col1) AS col4
     FROM Tbl AS T
     WHERE (col2 = @parm) 

How to write this SQL query in SQL Server 2008?

Pluralism

You should look at functions, you cannot call a stored procedure from within a select query. – Lan

select col1, col2, col3, EXEC GetAIntFromStoredProc(t.col1) as col4 FROM tbl as t where (col2 = @parm) IS NOT select col1, col2 FROM EXEC MyStoredProc 'param1', 'param2'. this is not a duplicate, tried editing but was rejected, the answer in this post is correct – Gibbon

Answer 1

Thanks @twoleggedhorse.

Here is the solution.

1. First we created a function

   CREATE FUNCTION GetAIntFromStoredProc(@parm Nvarchar(50)) RETURNS INTEGER
   
   AS
   BEGIN
      DECLARE @id INTEGER
   
      set @id= (select TOP(1) id From tbl where col=@parm)
   
      RETURN @id
   END
   

2. then we do the select query

   Select col1, col2, col3,
   GetAIntFromStoredProc(T.col1) As col4
   From Tbl as T
   Where col2=@parm
   

Pluralism

Exactly. The correct answer should have been "not possible". – Thorman

This is not the answer to the literal question posed regarding procedures. It's an alternate solution with huge limitations. See my actual answer. – Informant

How can this be a solution to your question you just converted the stored proc to a function :/ – Churchman

What if the store procedure has the dynamic queries to execute? It wont work with function. – Nanaam

Answer 2

Functions are easy to call inside a select loop, but they don't let you run inserts, updates, deletes, etc. They are only useful for query operations. You need a stored procedure to manipulate the data.

So, the real answer to this question is that you must iterate through the results of a select statement via a "cursor" and call the procedure from within that loop. Here's an example:

DECLARE @myId int;
DECLARE @myName nvarchar(60);
DECLARE myCursor CURSOR FORWARD_ONLY FOR
    SELECT Id, Name FROM SomeTable;
OPEN myCursor;
FETCH NEXT FROM myCursor INTO @myId, @myName;
WHILE @@FETCH_STATUS = 0 BEGIN
    EXECUTE dbo.myCustomProcedure @myId, @myName;
    FETCH NEXT FROM myCursor INTO @myId, @myName;
END;
CLOSE myCursor;
DEALLOCATE myCursor;

Note that @@FETCH_STATUS is a standard variable which gets updated for you. The rest of the object names here are custom.

Informant

Using cursor is a great workaround – Signalment

Answer 3

You can create a temp table matching your proc output and insert into it.

CREATE TABLE #Temp (
    Col1 INT
)

INSERT INTO #Temp
    EXEC MyProc

Simonetta

great that you don't change an existing procedure, only define table to result – Lagerkvist

this is exactly what I needed and don't know why I didn't think of it. Furthermore should have been the accepted answer. – Huggermugger

Answer 4

"Not Possible". You can use a function instead of the stored procedure.

Limnology

Answer 5

As long as you're not doing any INSERT or UPDATE statements in your stored procedure, you will probably want to make it a function.

Stored procedures are for executing by an outside program, or on a timed interval.

The answers here will explain it better than I can:

Function vs. Stored Procedure in SQL Server

Specie

A stored procedure CAN be used "for executing by an outside program, or on a timed interval.", but they are in no way limited to that, or intended only for those purposes. Scripts are written all the time to be run in a stand alone, on demand context for data manipulation. – Informant

Answer 6

"Not Possible". You can do this using this query. Initialize here

declare @sql nvarchar(4000)=''

Set Value & exec command of your sp with parameters

SET @sql += ' Exec spName @param'
EXECUTE sp_executesql @sql,  N'@param type', @param = @param

Bobbe

correct me if I'm wrong but this looks like a SQL injection just waiting to happen – Raila

Answer 7

Don't forget, if you just want to use the SP as a one-off query real quick to check something, all you have to do is pull the innards of the SP out and paste it in a new query window and do whatever you like at that point because it is no longer a SP.

Oyler

According to this advice, database integrity may be compromised! – Interbedded

Answer 8

First If it is possible to write store procedure as a new function, write a function like what @twoleggedhorse has noticed and @Joakim Applied.

CREATE FUNCTION GetAIntFromStoredProc(@parm Nvarchar(50)) RETURNS INTEGER

AS
BEGIN
   DECLARE @id INTEGER
   SET @id= (select TOP(1) id From tbl where col=@parm)
   RETURN @id
END

then we do the select query:

Select col1, col2, col3,
GetAIntFromStoredProc(T.col1) As col4
From Tbl as T
Where col2=@parm

But Many Stored Procedures Can't ReWrite by Functions (Like when you have IF Statements which use of "case when" statement or in "where" clause is not Responsible. and so must write a Stored procedure) Function vs. Stored Procedure in SQL Server has a good Review.

If Must Use Store procedure, can do like these:

1. Using table variable :

Declare @tablevar table(col1 Int, col2 Int)
         
insert into @tablevar(col1 ,col2 ) Exec GetAIntFromStoredProc @param1 ,@param2,@param3

then you can select data from that table like:

SELECT col1,
       col2,
       col3,
(select clo1 from @tablevar) AS col4 --or (select top(1) clo1 from @tablevar) AS col4
     FROM Tbl AS T
     WHERE (col2 = @parm) 

2. Using temp table:

    IF OBJECT_ID('#TmpTABLE','U')IS NOT NULL DROP TABLE #TmpTABLE 
    CREATE TABLE  #TmpTABLE (col1 INT,col2 INT)
    INSERT #TmpTABLE Exec GetAIntFromStoredProc @param1 ,@param2,@param3  
    
     SELECT col1,
               col2,
               col3,
        (select clo1 from #TmpTABLE) AS col4 --or (select top(1) clo1 from @tablevar) AS col4
             FROM Tbl AS T
             WHERE (col2 = @parm) 

3. Using Cursor: like what BuvinJ Has Respond.

     DECLARE @myId int;
        DECLARE @myName nvarchar(60);
        DECLARE myCursor CURSOR FORWARD_ONLY FOR
            SELECT Id, Name FROM SomeTable;
        OPEN myCursor;
        FETCH NEXT FROM myCursor INTO @myId, @myName;
        WHILE @@FETCH_STATUS = 0 BEGIN
            EXECUTE dbo.myCustomProcedure @myId, @myName;
            FETCH NEXT FROM myCursor INTO @myId, @myName;
        END;
        CLOSE myCursor;
        DEALLOCATE myCursor;

• Be Sure that select from Stored Procedure Query has only ONE data, not Many Fields or Rows. (Can Use select Top(1), where clause or ... in Select Statement)

Adrianneadriano

Answer 9

Create a dynamic view and get result from it.......

CREATE PROCEDURE dbo.usp_userwise_columns_value
(
    @userid BIGINT
)
AS 
BEGIN
        DECLARE @maincmd NVARCHAR(max);
        DECLARE @columnlist NVARCHAR(max);
        DECLARE @columnname VARCHAR(150);
        DECLARE @nickname VARCHAR(50);

        SET @maincmd = '';
        SET @columnname = '';
        SET @columnlist = '';
        SET @nickname = '';

        DECLARE CUR_COLUMNLIST CURSOR FAST_FORWARD
        FOR
            SELECT columnname , nickname
            FROM dbo.v_userwise_columns 
            WHERE userid = @userid

        OPEN CUR_COLUMNLIST
        IF @@ERROR <> 0
            BEGIN
                ROLLBACK
                RETURN
            END   

        FETCH NEXT FROM CUR_COLUMNLIST
        INTO @columnname, @nickname

        WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @columnlist = @columnlist + @columnname + ','

                FETCH NEXT FROM CUR_COLUMNLIST
                INTO @columnname, @nickname
            END
        CLOSE CUR_COLUMNLIST
        DEALLOCATE CUR_COLUMNLIST  

        IF NOT EXISTS (SELECT * FROM sys.views WHERE name = 'v_userwise_columns_value')
            BEGIN
                SET @maincmd = 'CREATE VIEW dbo.v_userwise_columns_value AS SELECT sjoid, CONVERT(BIGINT, ' + CONVERT(VARCHAR(10), @userid) + ') as userid , ' 
                            + CHAR(39) + @nickname + CHAR(39) + ' as nickname, ' 
                            + @columnlist + ' compcode FROM dbo.SJOTran '
            END
        ELSE
            BEGIN
                SET @maincmd = 'ALTER VIEW dbo.v_userwise_columns_value AS SELECT sjoid, CONVERT(BIGINT, ' + CONVERT(VARCHAR(10), @userid) + ') as userid , ' 
                            + CHAR(39) + @nickname + CHAR(39) + ' as nickname, ' 
                            + @columnlist + ' compcode FROM dbo.SJOTran '
            END

        EXECUTE sp_executesql @maincmd
END

-----------------------------------------------
SELECT * FROM dbo.v_userwise_columns_value

Semi

i think using cursor is not the point here, and kills the whole point of using Select – Inherence

In my case, I need cursor because I want dynamic columns in view and I get it from other user mapped table. – Semi

Answer 10

Stored procedures can't be accessed by Select/Where or Having statements. To run a stored procedure, use the Execute statement. Reference link: https://www.scholarhat.com/tutorial/sqlserver/difference-between-stored-procedure-and-function-in-sql-server#:~:text=Summary,critical%20for%20efficient%20database%20creation.

Premium

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. How to give a good answer – Ivetteivetts