Reading a Stored Procedure from Table Value Item

Question

I have a stored procedure called "GetSPTst" which I am calling from a table value function. In this function I am returning a table, the following is my SQL Code. I am getting an error "

Msg 443, Level 16, State 14, Procedure TstFunction Line 15. Invalid use of a side-effecting operator 'INSERT EXEC' within a function."

Could you please help?

IF EXISTS (SELECT *
       FROM   sys.objects
       WHERE  object_id = OBJECT_ID(N'[dbo].[TstFunction]
               AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
DROP FUNCTION [dbo].[TstFunction]
GO 

CREATE FUNCTION [dbo].[TstFunction]( @AsAtDate datetime, @DateFrom datetime, @DateTo datetime, @Ledger char(1), @CodeFrom varchar(6),
                                           @CodeTo varchar(6), @CurrencyFrom char(3), @CurrencyTo char(3))

RETURNS  @FunctionResultTableVariable TABLE (Currency char(3), Code varchar(6), BalOutst int)
AS
BEGIN

            insert @FunctionResultTableVariable
             EXECUTE  [dbo].[GetSPTst]                          
               @AsAtDate                                    
              ,@DateFrom                                    
              ,@DateTo                                  
              ,@Ledger                                  
              ,@CodeFrom
              ,@CodeTo
              ,@CurrencyFrom
              ,@CurrencyTo;


   return;
END
GO

Answer 1

It's not possible to execute a stored procedure from within the table valued function you've created, you could however change the stored procedure to be table valued function.