Symptoms

Client notes that after service instance refresh their stored-procedure based SmartObject displays Execute method instead of expected List, which in turn breaks some functionality they want to get out of this SMO.

So SMO method changes once client adds a call to it that points to a temporary object (e.g. temp table, but they also getting the same result using table variable and table function).
So currently they use a workaround that when all stored procedure logic is moved to table function and in stored procedure body there is a select from this function.

 

Diagnoses

So this leads to the following questions:
1) When should we expect Execute method to appear for stored procedure SMOs?
2) What is necessary in order to be sure that stored procedure SMO method always be List, irrespective of stored procedure content? Is it possible?
3) If appearance of Execute method for stored procedure is expected behaviour, then what is necessary to do in order to get stored procedure execution result via Execute method as in client’s case it doesn’t return any values.

 

Resolution

We always create our stored procs as type "Execute". But if there is a return column, we change the type to "List".

---------

From the client:

Procedure 1 has type "execute". Procedure 2 has type "list". Both procedures return the same data, so the method for both should be "list".

While searching for a workaround we have found the following dependency:
if there is a call to a temporary object (temporary table or table variable) in the procedure then the type of this procedure changes to "execute" (procedure 1).

In order to achieve the desired result (method "list") the temporary table has been replaced by a table function. As a result, instead of one temporary table filling up, we have multiple calls to the table function. This behaviour prevents the use of temporary objects in stored procedures, because SmartForms can not be mapped to SmartObjects with method "execute".

Using table functions leads to an increase in the number of artefacts in the database, and multiple calls to the table function instead of filling the temporary table once.

---------

We spoke to our developers about this issue, and they informed us temp tables are not supported in the SQL Service Instance. If you use Temp tables, it results in a dynamic result set, and unfortunately SMO's are not designed for this at the moment - that is why it only allows the execute method.

For more information have a look at this: https://www.k2.com/onlinehelp/k2blackpearl/userguide/4.6.9/webframe.html_tables.html

Especially look at the part about "FMTonly".