PICK FROM selections to parameters of a single EXEC_SP call

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

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:

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)

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,

INSERT States in LoggedInSystemUser.pm_States

Now you'll have all those instances available under the User Object. List_Line will work.
(Better read

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.

Stetson

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