Solved

Optional Parameters in SQL Stored Procedure SmartObject

  • 1 May 2020
  • 2 replies
  • 32 views
D
Badge +1

I have a stored procedure built with optional parameters. However, when I create the SmartObject for it, it makes all the parameters required, and I can't change it. Is there a way to make some optional?

The documentation here for "Use parameters for stored procedures" makes me think so....even if the value is set to 'true'.

https://help.k2.com/onlinehelp/k2cloud/userguide/current/default.htm#ServiceBrokers/SQLServer/SQL-Server-Service.htm

 

I already have a large number of SmOs created, and changing that value to 'false' now, breaks all those existing mappings. So I don't think that option is going to be viable. I would prefer parameters to properties in this case anyway. I'm curious if anyone has found an alternative.

 

Thanks in advance.


icon

Best answer by dknudsen 4 May 2020, 15:32

Thanks  @KagisoMasha ,

 

I'm not sure how I missed it the other day, but the first link you provided did get me to a solution.

For security reasons, we only grant our K2 Service Account "Execute" permission to our stored procedures, but for optional parameters to work as designed, "View Definition" permission needs to be granted as well.

 

GRANT VIEW DEFINITION ON [schema].[StoredProcName] TO [K2User] AS [dbo]
GO

 

Thanks again,

View original

2 replies

KagisoMasha
Userlevel 3

Hello  @dknudsen ,


 


Please kindly review these similar sceanrios and very similiar technniques of resolutions related to yours:


 


-Can't get K2 smartobject to recognize optional SQL parameters in stored procedure calls: https://community.k2.com/t5/K2-blackpearl-Articles/Can-t-get-K2-smartobject-to-recognize-optional-SQL-parameters-in/ta-p/95424


 


-Input Parameters for SmartObject method set to Required:https://community.k2.com/t5/K2-blackpearl-Articles/Input-Parameters-for-SmartObject-method-set-to-Required/ta-p/80052


 


-SMO ISSUE : SQL STORE PROC WITH 1 INPUT PARAMETER AND 3 OUTPUT PARAMETERS: https://community.k2.com/t5/K2-Five/SMO-ISSUE-SQL-STORE-PROC-WITH-1-INPUT-PARAMETER-AND-3-OUTPUT/td-p/103921


 


-SQL Server Stored Procedure Parameters with Defaults: https://community.k2.com/t5/K2-Blackpearl-Forum-Posts/SQL-Server-Stored-Procedure-Parameters-with-Defaults/td-p/78834


 


All the best.

D
Badge +1

Thanks  @KagisoMasha ,

 

I'm not sure how I missed it the other day, but the first link you provided did get me to a solution.

For security reasons, we only grant our K2 Service Account "Execute" permission to our stored procedures, but for optional parameters to work as designed, "View Definition" permission needs to be granted as well.

 

GRANT VIEW DEFINITION ON [schema].[StoredProcName] TO [K2User] AS [dbo]
GO

 

Thanks again,

Reply