K2 SmartForm Filter Stored Procedure Search in SQL Server

Because the default search filter provided by K2 Smartform is not preferred, after searching for other alternatives, finally found the use of stored procedure(SP) as an alternative to search filters.

Here are the steps to create a custom search filter with a stored procedure

1. Create a Student smart object with the following table:

id int,
codemhs string,
string name,
date of birth date,
String major

2. Create a stored procedure with the following code and run it in a sql query:

CREATE PROCEDURE [dbo].[StudentGetList]
@pkodemhs nvarchar(100) = ”,
@pnama nvarchar(100) = ”,
@ptgl_born date = ‘1900-01-01’,
@pjurusan nvarchar(100) = ”,
@SOP nvarchar(100) = ‘=-=-=-=’
US
SELECT TOP 1 ID, code, name, date of birth, major
FROM [dbo].[Student]
where 1 = 1

3. Do Refresh Service Instance

4. Then Create Smartobjects

5. Perform an Alter Stored Procedure with the following code:

CREATE PROCEDURE [dbo].[StudentGetList]
@pkodemhs nvarchar(100) = ”,
@pnama nvarchar(100) = ”,
@ptgl_born date = ‘1900-01-01’,
@pjurusan nvarchar(100) = ”,
@SOP nvarchar(100) = ‘=-=-=-=’
US

set @pkodemhs = LTRIM(RTRIM(@pkodemhs))
set @name = LTRIM(RTRIM(@name))
set @pjurusan = LTRIM(RTRIM(@pjurusan))

set @sql = ‘SELECT ID, code number, name, date of birth, major
FROM [dbo].[Student]
where 1 = 1’

if (LEN(@pkodemhs) > 0) begin
set @OP = REVERSE(PARSENAME(REPLACE(REVERSE(@SOP), ‘-‘, ‘.’), 1))

if @OP = ‘=’
set @sql = @sql + ‘ and codemhs ‘ + @OP + ‘ ”’ + @pkodemhs + ””

if @OP = ‘Start With’
set @sql = @sql + ‘ and codemhs Like ”’ + @pkodemhs + ‘%”’

if @OP = ‘Like’
set @sql = @sql + ‘ andkodemhs ‘ + @OP + ‘ ”%’ + @pkodemhs + ‘%”’
end
if (LEN(@name) > 0) begin
set @OP = REVERSE(PARSENAME(REPLACE(REVERSE(@SOP), ‘-‘, ‘.’), 2))

if @OP = ‘=’
set @sql = @sql + ‘ and name ‘ + @OP + ‘ ”’ + @name + ””
if @OP = ‘Start With’
set @sql = @sql + ‘ and name Like ”’ + @name + ‘%”’
if @OP = ‘Like’
set @sql = @sql + ‘ and name ‘ + @OP + ‘ ”%’ + @name + ‘%”’
end
if (LEN(@ptgl_born ) > 0) and (@ptgl_born <> ‘1900-01-01’) begin
set @OP = REVERSE(PARSENAME(REPLACE(REVERSE(@SOP), ‘-‘, ‘.’), 3))

if @OP = ‘=’
set @sql = @sql + ‘ and date of birth ‘ + @OP + ”” + convert(varchar(10), @ptgl_birth ) + ””
if @OP = ‘Start With’
set @sql = @sql + ‘ and date of birth Like ”’ + convert(varchar(10), @ptgl_birth ) + ‘%”’
if @OP = ‘Like’
set @sql = @sql + ‘ and date of birth ‘ + @OP + ‘ ”%’ + convert(varchar(10), @ptgl_birth ) + ‘%”’
end

if (LEN(@pjurusan) > 0) begin
set @OP = REVERSE(PARSENAME(REPLACE(REVERSE(@SOP), ‘-‘, ‘.’), 4))

if @OP = ‘=’
set @sql = @sql + ‘ and major ‘ + @OP + ”” + @department + ””
if @OP = ‘Start With’
set @sql = @sql + ‘ and majors Like ”’ + @pjurusan + ‘%”’
if @OP = ‘Like’
set @sql = @sql + ‘ and majors ‘ + @OP + ‘ ”%’ + @department + ‘%”’
end

execute (@sql)

6.Then create a form with the following view:

7. On the Search button add a List method with input parameters as shown

K2 by default will detect whether the stored procedure has a List method or an Execute method, but there are problems in recognizing the Stored Procedure which is a little advanced in number 5, causing K2 to provide an Execute method even though what we want is a List method.

Therefore, to get the List Method, the first time we outsmart K2 with simple sql coding at the beginning, namely number 2, after Refreshing Service Instances on Service Objects in SmartObjects – Services – Tester and getting List Method then do alter SP again at number The 5th without doing a Refresh Service Instance, so K2 will assume that the SP uses the List Method.

The weakness in this method is that it enforces the List method if you do a Refresh service Instance, then K2 will give the Execute method to the SP, and we have to outsmart the SP with a simple SP again.

The sample file above can be downloaded here

Newest way to display List Method

After contacting K2, here are the steps to display the List Method for complex SP

Create Parent SP that accesses SP StudentGetList

CREATE PROCEDURE [dbo].[StudentGetListParent]
@pkodemhs nvarchar(100) = ”,
@pnama nvarchar(100) = ”,
@ptgl_born date = ‘1900-01-01’,
@pjurusan nvarchar(100) = ”,
@SOP nvarchar(100) = ‘=-=-=-=’
US

IF 1=0 BEGIN
SET FMTONLY OFF
END
exec [StudentGetList] @pkodemhs,@pnama,@ptgl_lahir,@pjurusan,@SOP;
–SET FMTONLY OFF

Testing environment using

K2 version 11
SQL Server
SQL Server Management Studio