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?
3 Solutions collect form web for “How to execute a stored procedure inside a select query”
Here is the solution.
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
then we do the select query
Select col1, col2, col3, GetAIntFromStoredProc(T.col1) As col4 From Tbl as T Where col2=@parm
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
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;
@@FETCH_STATUS is a standard variable which gets updated for you. The rest of the object names here are custom.