Use result of stored procedure inline in SELECT statement

Question

I would like to execute a stored procedure inline, in the column definition of a SELECT statement, something similar to this:

SELECT n.Name,
       n.Key,
       (EXEC sp_GetNickname @nameKey = n.NameKey) AS Nickname
FROM Names n

I do not have access to modify the stored procedure (it comes from a third party system), but I know for a fact that it always selects only one row and one column (it's a scalar result).

I need to be able to call it inline because this select statement is inside a recursive CTE, something like this:

WITH Nicknames (Name, Key)
AS
(
    SELECT ... -- Base
    UNION ALL
    SELECT ... -- Recursive
)

There is also an "OUT" version of the stored procedure if that would be more useful, it would be executed as such:

EXEC sp_GetNicknameOut @nameKey = n.NameKey,
                       @outNickname = @theNickname OUTPUT

I can't add any stored procedures or functions to the database because this query is being executed from a C# application on the fly.

How can I achieve this?

Answer 1

This can't be done as requested. You might be able to execute a recursive CTE without calling the procedure and save results into a temp table, then open a cursor over it, and then execute a procedure in the cursor. Not nice, but I don't see any other way.

Answer 2

You cannot Select from a procedure but what you can do since you know the returned number of rows and column from this procedure you can create a temp/variable table and insert into that and then use that temp table or variable table inside you other statement,

Something like this...

CREATE TABLE #Temp(ColName DataType)

INSERT INTO  #Temp(ColName)
EXEC sp_GetNickname @nameKey = 'value'

Now you can use this value inside you SELECT statement

SELECT n.Name,
       n.Key,
       (SELECT ColName FROM #Temp) AS Nickname
FROM Names n