Configuration setting to deal with Optional Parameters while creating SQL Service Instance

Have you ever faced this situation while creating a SmartObject method for a SQL stored procedure, if the SQL stored procedure has some optional parameters like below, but in SmartObject it still shown as a required parameters?

Sample stored procedure with optional parameters (parameters with default values)

CREATE PROCEDURE MyProcName
    @Parameter1 VARCHAR (100) = 'User'
AS
BEGIN
  SELECT 'HELLO ' + @Parameter1 AS [Message]
END

 

Here @parameter1 is an optional parameter which is when passed then Stored Procedure will return ‘Hello Parameter value’ if not passed then ‘Hello User’

2
SQL Execution

Now without changing the configuration setting if we create SmartObject then it will throw error as below  when we are not passing the parameter value.

1

Now when we pass parameter value then it will give the result

3.jpg

Now to make this parameter as optional we need to modify the service instance and set this attribute Use parameters for stored procedures to False (by Default it is True) and save.

ServiceInst2
Service Instance Setting

Now recreate the SmartObject and execute the method without passing the Parameter Value which will give you the result.

5.jpg

A simple yet very useful setting right 🙂 !!!!

 

Advertisements

Creating SQL Server Service Instance

In K2 black pearl service instance is nothing but a service broker used to connect to multiple LOB’s (Line of Business) systems or data bases to get or post the data.  Using these Service Instances we will be creating smart objects which will be used in development of workflows and smart forms.

By default, K2 have provided the Service Brokers to connect below LOB’s which we use mostly

SQL Server

To connect to SQL Server Tables & Stored Procedures

Oracle

Web Services

WCF Services

Endpoint Assembly

To connect to .Net Assemblies/ DLL’s

AD Service

To connect to Active Directory

SharePoint Service

To connect to share point lists and libraries.

For the list of all available Service brokers open the SmartObjects – Services – Tester utility which comes by default with K2 installation at path C:\Program Files\K2 Blackpearl\bin

List of service brokers

soservicetester

Now let’s create a SQL Server Instance and go through options available

Step 1: Right Click on SQL Server Service and Select Register ServiceInstance

serviceinst1

Step 2: Enter the details as shown in below screen shot

Authentication mode need to be selected as service account to run this instance under K2 service account credentials.

serviceinst2

There is an attribute Use parameters for stored procedures for which by default value is true but I have set it as false for knowing the reason please read this Post.

Once provided all Database details and Database Server Details click Next

Step 3:  Now provide the service Instance name, display name & description and click Add

serviceinst4

That’s it we have completed creating a Service instance for a data base in SQL Server. Now you can expand and see the service instance that we have created under SQL Server Service, you can see all the tables and stored procedure available in the data base.

Now we can use this service instance for creating smart objects.