Question

Use different stored procedure for Smart Object schema

  • 25 January 2016
  • 3 replies
  • 4 views

Hi all.

 

I am (very) new to K2. As in - we still have consultants doing our K2 development for us for the time being. We want our current system to integrate with a brand new K2 solution.

 

I have a question about the source stored procedures used for Smart Objects. A lot of our stored procedures are very complex, using conditional statements and temporary tables. However, our consultants are telling us these stored procedures is a problem because K2 tries to automatically get the schema for the smart object from the stored procedure. This would mean that we'll need to write a lot of duplicate code, and I'm not sure that we'll be able to get away from conditional statements and temporary tables in all cases, as we have some very complex stored procedures.

 

I was wondering - isn't there some way arround it? I find it a bit weird that a product as powerful and versatile as K2 would have such a terribly restrictive limitation build into it?

 

Isn't there a way to define the schema manually - rather than to derive it from the stored procedure? Or is it perhaps possible to give it a different stored procedure that would just define the result set table, and then I can still use my original complex stored procedure to populate the data? 

 

We would prefer not to add an unnecesary webservice layer in between - as this would introduce a compiled code layer which would limit agility. Surely, there must be a way for K2 to interact with very complicated stored procedures directly.

 

Thanks.


3 replies

Hi there


 


I have personally had issues with having complicated stored procedures that need to be turned into SmartObjects but can't because they use temp tables. What you can do is use table variables, as they are not dynamic and therefore do not cause an issue with finding the results. My solution, rather than rebuilding the stored procedure, was to create another stored procedure that called the first one and stored the results in a table variable which could then be returned to the SmartObject. This allowed me to keep the original stored procedure without too much extra work.


 


Ian

Thanks, I guess the answer is "no" then. Your solutions is good for temporary tables, but I don't think it will work for conditional statements. Some of our stored procedures return more than one table, and I wanted to use a conditional statement that would've concatenated them into a single table for K2 (As K2 don't like multiple result tables either) However, in those cases, it seems that I have no choice but to duplicate the stored procedure for K2.

 

 

Your post was from 2016. It's 2020 and I'm still having this problem. If I have a conditional in a stored procedure the SMOs are unpredictable.

 

If anyone sees this and can point me to the documentation that will help me understand what's going on I would be grateful. This is one of many traps that are souring my use of this product.

Reply