PICK FROM selections to parameters of a single EXEC_SP call

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
Stetson
Posts: 54
Joined: Tue Dec 04, 2018 11:00 pm

PICK FROM selections to parameters of a single EXEC_SP call

Post by Stetson »

Looking for high level direction. I have a PICK FROM query. I have a 10-parameter (SQL Server) stored procedure called by EXEC_SP. The 10 parameters are to hold (up to) 10 instances of column 1 of the PICK FROM query results. How do I reference the picked rows (instance prefixes?) in order to use them in the EXEC_SP call?

The EXEC_SP call works fine. The PICK FROM query works fine. I'm just unclear how best to grab the PICK FROM values to pass. Is there a better way to do this rather than by using a PICK FROM query, since there's only ONE EXEC_SP call?

Thanks for any suggestions.
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: PICK FROM selections to parameters of a single EXEC_SP c

Post by Jaymer »

whatever instances are selected from PICK ONE OR MORE will be in context.

Lets say you show a list of 50 States. You tell the user "Pick up to 10 States".
( PICK ONE OR MORE FROM States )

immediately after that statement, the selected States will be in context.
How about:

Code: Select all

LISU.tmp_States = UNDEFINED ............. LISU = LoggedInSystemUser
BuildStateString ....... this calls a process of this name
LISU.tmp_States = ..... some code to strip off trailing , or leave it and handle in SP
EXEC_SP .... and send it 1 string with the 10 IDs instead of 10 fields
Process: BuildStateString (input BO: States)

Code: Select all

LISU.tmp_States = LISU.tmp_States + States.ID + ','

There's prolly a way to do it with a WHILE loop also.
Either way you have to loop thru 10 in Aware, or parse the string to extract the 10 in SQL (whichever is easiest based on available functions)

You can't use LIST_LINE (or any LISTxxx functions) because you don't have 10 instances in a list, like LISU.pm_SelectedStates
Seems overkill to insert the States into a list, just to use LIST_LINE to build a single list out of them.
But you could if you wanted to:
In addition to creating tmp_States in the RegUser table, create: pm_States, type States (or whatever that PickFrom is coming from), Multiple
Then right after the PICK ONE OR MORE,

Code: Select all

INSERT States in LoggedInSystemUser.pm_States
Now you'll have all those instances available under the User Object. List_Line will work.
(Better read this on LIST_LINE. Its quirky and the examples are bad in Docs)

Code: Select all

LISU.tmp_States = LIST_LINE.....
thats just a couple of ways - i'm sure there are more, but not much activity on the Forum anymore, esp. on the weekend.
Last edited by Jaymer on Fri Nov 15, 2019 10:03 pm, edited 1 time in total.
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: PICK FROM selections to parameters of a single EXEC_SP c

Post by Jaymer »

And if you wanted to do it with the 10 fields, you'll need some loop & a counter, to use in an IF statement...

assume you're looping through the list of n values.
ex: If counter=1 then field1 = theID
if counter=2, etc etc

hardcoded and not elegant.
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
Stetson
Posts: 54
Joined: Tue Dec 04, 2018 11:00 pm

Re: PICK FROM selections to parameters of a single EXEC_SP c

Post by Stetson »

Thank you, Jaymer! Perfect! That's the piece I was missing. Really appreciate it!
Post Reply