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?

  • Return previous record for holiday/weekends
  • Format SQL Server 2012 Time(7) to “HH:mm”
  • error in connecting SQL server in django
  • Case sensitive on restrictions 'equals id' in Hibernate restrictions
  • Synchronize Records using Entity Framework
  • How i can cancel a ExecuteReaderAsync with CancellationToken
  • 3 Solutions collect form web for “How to execute a stored procedure inside a select query”

    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
      

    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;
    

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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.