SQL Server Stored Procedure Parameters with Defaults

  • 29 April 2015
  • 7 replies
  • 114 views
B
Badge +2

Hi,

 

I am trying to interface with one of our legacy systems and call a pre-existing SQL Server stored procedure which is rather generic and has many parameters (most unused) with default values assigned (I.e. assigned as part of the SP definition in SQL Server) to make it more useful.  Anyway the stored procedure shows up under our K2 SQL service objects with all parameters being "Required".  How can I change the service object so the parameters with defaults aren't showing up as "Required"?  Or is there a way in a smart object's service object method parameter bindings to just say use the default (i.e. I noted there is a SCNULL value I can pass, is there something similar to SCDEFAULT?).

 

Thanks in advance

Peter

7 replies

J
Badge +1

Hi,

Can you modify your stored procedure? put input parameter with default value NULL.

S
Badge +10

The service instance should pick up required or not required input parameters if you set the input paramteres n the stored procedure to NULL when you initialise them. EG:

 

declare @test [nvarchar] (400) = NULL

B
Badge +2

I already am setting several of the parameters to NULL defaults but the ServiceObject seems to list all parameters as required.   I can work around it by setting the appropriate default in the SmartObject's method's parameter mapping, but it would be nice to know how to control the "Required" information.

 

Thanks guys.

 

 

 

A
Userlevel 1
Badge +8

Hi Bugmeister

 

Try changing the setting for your SQL Service instance to "User Parameters for Stored Procedures" to false and update your service instance. Have a look at the following thread which addresses a similar issue:

 

http://community.k2.com/t5/K2-blackpearl/Required-Stored-Procedure-Parameter/td-p/53439/page/2

B
Badge +2

Thanks Andrew,

 

Changing that setting actually went to the other extreme where all parameters are now not required, even those without defaults assigned.

 

Cheers

Peter

G

I had the same issue. I changed permission of user, who were used to connect DB in Service Instance Configuration to db owner. Worked for me, but I am still looking for solution, where not such high rights would be required...

 

Best Regards,

 

Grzegorz

Nicolaas

I have confirmed and ran into this issue recently.


This has to do with using the K2 service account VS impersonation on the service instance.


If you do not set the K2 Service account with DB_Owner rights this will occur.


Once you have set the permissions your properties set as NULL will no longer be required.


Just ensure to refresh your Service instance in the smartobject tester tool.


 


 

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings