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

Author: Vijay

I'm a K2 Certified Developer, helping teams to automate, design, develop & maintain business process applications connecting across multiple platforms with multiple technologies.

One thought on “Configuration setting to deal with Optional Parameters while creating SQL Service Instance”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s