Add single value from stored procedure to Select query

Question

We have a stored procedure Taxable that returns one row with 5 entries. It takes two parameters, ID and type, and I am trying to use one entry only ([MonthlyFeeTax]) in a select statement.

All of the other values are being selected from a table UDF, the stored procedure is from a vendor and I can't change it.

Exec Taxable (51, 'Client') 

returns the following for this client:

Income	Expenses	Payments	MonthlyFee	MonthlyFeeTax
3000	2200	200	25	1.25

I want to use the monthly tax calculated for a specific creditor as below but can't get this in and am looking for the best approach. I know the case statement is wrong but it indicates the outcome I am looking for. How can I get the single value from the Taxable stored procedure into this query? Any suggestions would be appreciated.

SELECT
    id, accountnumber,  [Balance], payment,            
    CASE
        WHEN CreditorID = 1 
            THEN Exec Taxable (51, 'Client')
            ELSE NULL
    END AS [Taxes]
FROM
    [dbo].[AccountBalances] (51, 'Clients')

Answer 1

Use insert... exec... syntax to get the result from the procedure into a table variable you can use. Since you know only a single row is returned you can simply embed a subquery into your query:

declare @Output table (
  Expenses decimal,
  Payments decimal,
  MonthlyFee decimal,
  MonthlyFeeTax decimal
);

insert into @Output exec Taxable(51, 'Client');

Select id, accountnumber,  Balance, payment,            
  Case CreditorID when 1 Then (select MonthlyFeeTax from @Output) End Taxes
from dbo.AccountBalances(51,'Clients');