How to execute a stored procedure inside a select query

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.

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

CREATE TABLE #Temp (
    Col1 INT
)

INSERT INTO #Temp
    EXEC MyProc

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

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

"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

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.

First If it is possible to write stored 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 be rewritten to be 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 stored procedure, you can do like this:

1. Use a 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 col1 FROM @tablevar) AS col4 
    -- or (SELECT TOP(1) col1 FROM @tablevar) AS col4
FROM 
    Tbl AS T
WHERE 
    (col2 = @parm) 

2. Using a temp table:

IF OBJECT_ID('#TmpTABLE', 'U') IS NOT NULL 
    DROP TABLE #TmpTABLE 

CREATE TABLE #TmpTABLE (col1 INT, col2 INT)

INSERT INTO #TmpTABLE 
    EXEC GetAIntFromStoredProc @param1, @param2, @param3  
    
SELECT 
    col1, col2, col3,
    (SELECT col1 FROM #TmpTABLE) AS col4 
    -- or (SELECT TOP(1) col1 FROM @tablevar) AS col4
FROM 
    Tbl AS T
WHERE  
    (col2 = @parm) 

3. Using a cursor - like what BuvinJ has responded:

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 row of data, not many columns or rows (can use SELECT TOP(1), WHERE clause or ... IN statement)

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

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.