1

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.

Abdulquadir Shaikh
  • 105
  • 1
  • 3
  • 9
  • 1
    Nobody is going to go download your code from github. But you can't do what you are trying to do in a function. You could do it in a stored procedure but functions do not allow calling procedures and executing dynamic sql. – Sean Lange Mar 15 '17 at 13:45
  • What @SeanLange said. Also, take a look at the already asked (and answered) question: http://stackoverflow.com/questions/6344880/execute-stored-procedure-from-a-function – MK_ Mar 15 '17 at 13:46
  • Well I am not asking anyone to download the code. Its just that its a very big 100 lines code for procedure and function each so I kept it there. – Abdulquadir Shaikh Mar 15 '17 at 13:46
  • Do you really need a user-defined function? Can you do what you want in a stored procedure? – Gordon Linoff Mar 15 '17 at 13:50
  • Since the workarounds are self-evident, can you focus your question to explain which of the obvious work arounds you have considered, and why you rejected them? – Tab Alleman Mar 15 '17 at 13:50
  • Well I am quite new to working with SP and UDF. So I had this procedure and function in Informix database which will reduce my further reporting work in SQL Server. So I just converted those in SQL Server and I am having issues. – Abdulquadir Shaikh Mar 15 '17 at 13:53
  • I have not tried any work around yet. – Abdulquadir Shaikh Mar 15 '17 at 13:54
  • Why are you converting it as is? What do you need that function for? Just exec your proc. – Ivan Starostin Mar 15 '17 at 14:04
  • I cannot to do that. Because function has table variable which are used in stored procedure. And stored procedure manipulates the data in the way I need. And function also calls that procedure. To answer your first question I am creating reports in Power BI and we use SQL Serve for reporting so I want that to be converted from Informix to SQL Sever. – Abdulquadir Shaikh Mar 15 '17 at 14:08
  • Make your function an sp. As I understood inner SP is just a part of code performed in FN. Convert this FN into SP. Or your BI cannot call sps? (don't know how it works) – Ivan Starostin Mar 15 '17 at 14:13

1 Answers1

2

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.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • So if I understand correctly a "Calculate Bank account Check Digit" is better as a Procedure that has return parameters than a function that return the VARCHAR(2) ? – tsmets Jan 21 '21 at 15:01