+5
Hello Community,
I've created a stored procedure to get some information out of my Database.
The procedure will be created with dynamic variables and at the end, I use the execute sp_executesql command.
The SMO provides me only the Execute command.
Is there a way I can change the command from execute to list?
Attached you can find my script.
Thank you for your help.
Alexander
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.
+5
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
+10
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
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.