Haywhy21
Apprentice

SQL Procedure

Jump to solution

Hello People,

 

I need help with an sql procedure. I am trying to make the procedure accept multiple entries in a list view, which it will in turn post. The script I have can only cater for a single entry per parameter in an item view, and list view.

Any help as regards this will be appreciated.

 

Find attached the sample script.

The Guarantor, and DirectorsInfo are the places where I want the script to accept multiple values in a list view.

Labels: (3)
0 Kudos
Reply
4 Replies
kimh
Nintex Professional Services Expert

Re: SQL Procedure

Jump to solution

Without knowing too much about how the data is to be consumed, is it possible to use a STRING_AGG to list the comments in a string such as: 

@DirectorsInfo nvarchar(100) = (Select STRING_AGG([Comments], ',') from [K2].[SmartBoxData].[FOS_Corp_BoardofDirectorsOwnershipStructure_SMO] where HeaderID = @HeaderId),

 

That should return the JSON value as "DirectorsInfo":"Comment 1,Comment 2,Comment 3" in the output.  There are potentially several ways to solve this depending on how you need the data to display in your output. 

0 Kudos
Reply
Haywhy21
Apprentice

Re: SQL Procedure

Jump to solution
@kimh thanks for your reply.
DirectorsInfo":"BVN-12345678901-xyz@abcd.com|BVN-12345678901-xyz@werty.com",
"Guarantor":"NON-INDIVIDUAL_TIN_12133453_100000000|INDIVIDUAL_BVN_12133453_200000000"
}

Considering that the above are the sample payload for the 2 parameters.
Nevertheless, I will try the string_agg method when I am at my desk.

0 Kudos
Reply
paulk
Scout

Re: SQL Procedure

Jump to solution

Be careful here - I'm fairly confident that you are not allowed to add your own stored procedures to the K2 database, or to call into the K2 database directly as this will leave you in an unsupported state.

 

Cheers

0 Kudos
Reply
Haywhy21
Apprentice

Re: SQL Procedure

Jump to solution

Thanks to everyone I found my way around the script, and I am good now.

0 Kudos
Reply