I've written a stored procedure that creates a temp table, loads the temp table from another stored procedure and then performs SUM on the data in the temp table. It then sets a variable, drops the temp table, and returns the variable to the caller.
When I run the stored procedure I'm getting a number in SSMS. When I SmartObjectize the stored procedure the only method it's coming up with is Execute. I am not getting List or Read.
Any ideas how to trouble shoot this?
The broker will only ever expose a list or execute method.
List is exposed when a return value is discovered and execute if no return is found.
The behavior you describe tells me that k2 cannot discover the return value. Try adding a select statement to the end of your stored procedure to return your variable such as SELECT yourvarable AS somecolumnname. Should clear it up for you.
This reminds me one of the limitation of the SQL server broker. Please note K2 SQL server broker as follwing limiations;
SQL borker doesn't support;
As a work around i would suggest you to tryout using table varialble which is supported by the SQL broker.!
Hope it helps!
Prajwal is correct which is why you have to define what essentially amounts to an implicit cursor to return your variable so that K2 will be able to discover it. I have implemented this approach in both SQL and ORACLE and works well.
I've used a table variable with K2 and it works very well. Temp tables though do not work.
So instead of a Create #temptable ([id] bigint, text NVARCHAR(200)), you can do a Declare @ParameterTable ([id] bigint, text NVARCHAR(200)).