How to execute a stored procedure inside a select query

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?

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
   

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

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

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

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.

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