SQR Procedure parameters/arguments

Question

I'm trying to understand more about how arguments/parameters are used when calling and executing procedures. I understand that procedures that contain an parameter with a leading colon (:) pass back values to the calling DO command, however I think what it a little confusing is it appears that the variables names from what the calling DO command issues, and what the procedure (called by DO) returns don't necessarily have to be the same name. If someone could help shed some light on the following examples and explain what values are being either passed to/from or how their referenced by the issuing DO command that would be helpful.

It looks like the call to run the Get-Recursive-Reports-To (do Get-Recursive-Reports-To($Recursive_Line, $_POSITION_NBR, #_Global_Counter) is issuing these 3 variables as parameters to the procedure Get-Recursive-Reports-To, however as I look through the Get-Recursive-Reports-To procedure, I do not see any references of the variable $Recursive_Line within this procedure, so is the procedure actually using it or what is the purpose of including it? Similar question with $_val_Position_NBR, where is this variable getting it's value assigned from?

And then within the Get-Recursive-Reports-To procedure I see a parameter - :$var_Next_EMPLID that I believe is being passed back to the calling DO in the Run-Recursion procedure, but I can not figure out how/where it uses the value passed back to it...

begin-procedure Run-Recursion

let #_Global_Counter = 0

let $Recursive_Line = ''

let $Data_Line_EMPL = ''

let $Data_Line_EMPL = $_BUSINESS_UNIT || '|' || $_BUSINESS_UNIT_DESCR || '|' || '2019' || '|' || 

$_EMPLID || '|' || $_NAME || '|' || $_DEPTID || '|' || $_DEPT_DECSR || '|' || $_JOBCODE || '|'

do Get-Recursive-Reports-To($Recursive_Line, $_POSITION_NBR, #_Global_Counter)

let $Data_Line_EMPL = $Data_Line_EMPL || $Recursive_Line

do Write-Data-Line($Data_Line_EMPL)

end-procedure

begin-procedure Get-Recursive-Reports-To(:$val_Data_Line, $val_Current_Position_Nbr, #Recursion_Counter)

let #Recursion_Counter = #Recursion_Counter + 1

do Get-the-ReportsTo-for-the-Current-Position($val_Current_Position_Nbr, $Next_Position_Nbr, $Next_EMPLID)

do Check-For-Stop($Stop_Recursion, $val_Current_Position_Nbr, $Next_Position_Nbr, #Recursion_Counter)

if $Stop_Recursion = 'N'

let $val_Data_Line = $val_Data_Line || $Next_EMPLID || '|'

do Get-Recursive-Reports-To($val_Data_Line, $Next_Position_Nbr, #Recursion_Counter)
end-if
end-procedure

begin-procedure Get-the-ReportsTo-for-the-Current-Position($_val_Position_NBR, :$var_ReportsTo, :$var_Next_EMPLID)
  let #local_counter = 0

begin-select
G.REPORTS_TO &G.Reports_to
 let $var_ReportsTo= &G.Reports_To

from PS_POSITION_DATA G
WHERE G.POSITION_NBR = $_val_Position_NBR
and G.EFF_STATUS = 'A'
AND (G.EFFDT = 
(SELECT MAX(G_ED.EFFDT) FROM PS_POSITION_DATA G_ED 
WHERE G.POSITION_NBR = G_ED.POSITION_NBR 
AND G_ED.EFFDT <= $_As_OF_Date))
end-select

begin-select
H.EMPLID &H.EMPLID
Z.NAME         &Z.NAME
 let $var_Next_EMPLID= &H.EMPLID
 let #local_counter = #local_counter + 1
from PS_JOB H !, PS_EMPLOYEES Z
WHERE H.POSITION_NBR = $var_ReportsTo
and H.EMPL_STATUS not in ('D', 'R', 'T')
and (H.EFFDT = 
        (SELECT MAX(H_ED.EFFDT) FROM PS_JOB H_ED 
          WHERE H.EMPLID = H_ED.EMPLID 
          AND H.EMPL_RCD = H_ED.EMPL_RCD 
          AND H_ED.EFFDT <= $_As_Of_Date))

end-select
  if #local_counter > 1
  let $var_Next_EMPLID = $local_counter  || ' ' || 'Employees in this Position'
  end-if

  if #local_counter = 0
  let $var_Next_EMPLID = 'Position Vacant'
  end-if

end-procedure

score 2 · Accepted Answer · answered Apr 04 '19 at 15:42

2

The FIRST call to Get-Recursive-Reports-To from the main procedure uses the $Recursive_Line variable. It doesn't have to be referenced again because it's internal to the procedure.

Once IN procedure Get-Recursive-Reports-To, the name of this variable becomes $val_Data_Line and is used to call back to the same procedure Get-Recursive-Reports-To which can change it. I guess ergo the name, Get-Recursive-Reports-To.

Recursion is a pain in the neck but it looks like it's being used to go up an organizational chain, finding the reports to employee id, then getting the name of that person.

Variable $var_Next_EMPLID is passed from Get-the-ReportsTo-for-the-Current-Position back to the caller as variable $Next_Emplid which is then used again to call Get-Recursive-Reports-To.

I can't tell when or why the Recursion stops since you didn't include the procedure "Check-For-Stop". Looks like there is some check - perhaps if the $Val_Current_Position_Nbr or $Next_Position_Nbr is blank or equal or something.

answered Apr 04 '19 at 15:42

cardmagik

1,698
19
17

1

Thanks for the quick response! I think what was difficult is that the variable being passed to a procedure (like $Recursive_Line now being referred to as $val_Data_Line inside Get-Recursive-Reports-To) can be changed to a different name and not always able to discern this. So can I assume that the variable(s) defined in the CALL statement, align with those inside the procedure? i.e. DO XYZ-procedure($Variable_1, $Variable_2) begin-procedure XYZ-procedure($Variable_1, $Variable_2) , even if the variable names inside the XYZ-procedure are different, it's more the order that matters? – Nick Apr 04 '19 at 16:10
1

Also within the procedure `Get-Recursive-Reports-To` there is a call to another procedure named `Get-the-ReportsTo-for-the-Current-Position` with the variable $Next_Position_Nbr where I am trying to determine is this variable is providing an input value to the procedure `Get-the-ReportsTo-for-the-Current-Position` or if this is just a placeholder variable to receive the value outputted from the procedure `Get-the-ReportsTo-for-the-Current-Position` as :$var_ReportsTo ? Is there a way to easily tell the difference? Thanks again! – Nick Apr 04 '19 at 20:24
@Nick Yes to your first comment - arguments and parameters are positional and not named. To your second comment, the colon (:) dictates whether a parameter will return a value (as defined in the procedure). You can pass a value and have it changed but ONLY if the procedure has a colon. Otherwise, it's one-way. Hope that helps. – cardmagik Apr 05 '19 at 14:04

SQR Procedure parameters/arguments

1 Answers1