sqlsql-serversql-server-2008stored-procedures

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?


Solution

  • 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