Only functions and some extended stored procedures can be executed from within a function

Question

I have Stored Procedure and User Defined Table Valued function created in SQL Server 2016. I am able to compile both procedure and function successfully.

Procedure Name: getAgentLogActivity
Function Name: sp_agent_log_activity

Function is calling the procedure getAgentLogActivity as well as Exec sp_executesql. So when I try to run below select query on the function I get an error.

Error: Only functions and some extended stored procedures can be executed from within a function.

SELECT * FROM [dbo].[sp_agent_log_activity] 
('2017-02-01 00:00:00','2017-02-01 23:59:59',0,'','','','')

The complete code for stored procedure and function can be found at below location.

Stored Procedure and User Define Table Valued Function

To give background on the code, it is a code from Cisco UCCX database which is in Informix. I have converted it into SQL Server.

Please let me know if there is any work around for the error.

Answer 1

The easiest workaround is to turn your Table-Valued Function into a Stored Procedure. Stored Procedures do not have the limitation of not being able to execute another stored procedure.