Optional Parameters in SQL Stored Procedure SmartObject

Question

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

View original

dknudsen · Accepted Answer

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,