Hello A_Chris,

This seems to be a known issue. Please follow this link (https://community.k2.com/t5/K2-blackpearl-Articles/Stored-Procedure-Update-List-vs-Execute/ta-p/81691) about stored procedure Execution Vs List and see if it can help you resolve the issue. 

Should this document help you resolve your issue, please mark it as such by "Accept as solution" or "Kudo" as it will help other community members with a similar issue.

Thank you,

Katleho

K2 will not accept any liability for any issues arising from actions taken in respect of the information provided by any forum member.

Hey Alex,

If you refer back to how you created your stored procedure, your script must be something like the below script:

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

Create Procedure demonstration

AS

BEGIN

--------- make use of the @ character (not # character) to make the table a table variable instead of temporary table-----------

DECLARE @PersonalInfo TABLE

(

--------- variable declararions-------------------------

)

END

GO

----- Execute the stored procedure----------

EXEC demonstration

GO

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

If you dont mind, create a small stored procedure in the above manner and test it on your end. Otherwise, you have to declare your table just like you declare your variables.

Please provide feedback on the results of the test.

Regards,

Katleho

Hi,

The "execute" method is generally seen on stored procedures that do not return values.  "List" is seen when the stored procedure does return values.  This is determined when the service instance is refreshed.  The SQL broker executes the stored procedure with SET FMTONLY ON in order to determine what input/output properties to expect from the stored procedure, while also not taking any actions that could impact the data in the database unintentionally.

I'd hazard a guess, but when you execute your stored procedure with SET FMTONLY ON as below...

SET FMTONLY ON

EXEC your_storedProc
SET FMTONLY OFF

Your stored procedure is likley not returning any column names in this circumstance.  It should be noted that FMTONLY ON will not execute any conditional logic, insert, update or create statements, and a few other caveats.  Your stored procedure will need to return the same properties under this condition as when normally executed to properly expose it's return values to K2.

https://help.k2.com/kb001451 describes this behavior in more detail

Regards,

Mike

This procedure does not return any values when FMTONLY is set on and the smo only has the Execute method. How can I change it to return the required values?

CREATE PROCEDURE dbo]..Get_Labour_Broker_Week_Dates] 
(
    @YearMonthNumber    INT,
    @WeekNumber            INT
)
AS
BEGIN
    
    DECLARE    @StartDate    DATETIME,
            @EndDate    DATETIME;

    DECLARE @WeekDates    TABLE    (StartDate    DATETIME,
                                 EndDate    DATETIME )

    SELECT    @StartDate = DATEADD(DAY,-8,DATEADD(WEEK, @WeekNumber, DATEADD(YEAR, CAST(LEFT(@YearMonthNumber,4) AS INT)-1900, 0))),
            @EndDate = DATEADD(DAY,-1,DATEADD(WEEK, @WeekNumber, DATEADD(YEAR, CAST(LEFT(@YearMonthNumber,4) AS INT)-1900, 0)));

    INSERT INTO @WeekDates
        (
            StartDate,
            EndDate
        )
    VALUES
        (
            ISNULL(@StartDate,GETDATE()), 
            ISNULL(@EndDate,GETDATE())
        );

    SELECT    StartDate,
            EndDate
    FROM    @WeekDates;

END