Question

SQL Server Stored Procedure Parameters with Defaults

  • 29 April 2015
  • 6 replies
  • 18 views

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


6 replies

Hi,

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

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

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.

 

 

 

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

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

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

Reply