Assign the result of a query to a variable in powershell in Azure-Runbooks

I have a runbook using automation in Azure. It is getting a single integer result from a table and it can return the correct value. The code I am using is below and it works.

 $SQLServerCred = Get-AutomationPSCredential -Name "SqlCredential"
 #Import the SQL Server Name from the Automation variable.
 $SQL_Server_Name = Get-AutomationVariable -Name "SqlServer"
 #Import the SQL DB from the Automation variable.
 $SQL_DB_Name = Get-AutomationVariable -Name "Database"


 $Query = "select max(je.ExecutionOrder) as LastStepExecuted
from PPoint.JobExecutionHistory je
where je.EventType = 'Start'
and je.JobRunId = PPoint.fnGetJobRunID()"

 invoke-sqlcmd -ServerInstance "$SQL_Server_Name" -Database "$SQL_DB_Name" -Credential $SQLServerCred -Query "$Query" -Encrypt

The next step for me is to assign the result from the query to a variable and then evaluate it to see if it should call another runbook. So I want to have a variable named LastStep and assign it the integer result of LastStepExecuted from the query below. I then want to do something along this line (this is pseudocode)

if LastStep = 2147483647
 call another runbook
else
 do nothing - end the runbook

I have tried several ways to capture the LastStepExecuted in a variable but I can't figure it out. Can anyone help?

Any help or advice much appreciated.

Solution

You can use the Start-AzAutomationRunbook cmdlet to trigger a child runbook from the another run book inside the automation account.

$SQLServerCred = Get-AutomationPSCredential -Name "SqlCredential"
 #Import the SQL Server Name from the Automation variable.
 $SQL_Server_Name = Get-AutomationVariable -Name "SqlServer"
 #Import the SQL DB from the Automation variable.
 $SQL_DB_Name = Get-AutomationVariable -Name "Database"


 $Query = "select max(je.ExecutionOrder) as LastStepExecuted
from PPoint.JobExecutionHistory je
where je.EventType = 'Start'
and je.JobRunId = PPoint.fnGetJobRunID()"

$LastStep=invoke-sqlcmd -ServerInstance "$SQL_Server_Name" -Database "$SQL_DB_Name" -Credential $SQLServerCred -Query "$Query" -Encrypt

if($LastStep -eq 2147483647)
{
    Start-AzAutomationRunbook -AutomationAccountName "MyAutomationAccount" -Name "Test-ChildRunbook" -ResourceGroupName "LabRG" -DefaultProfile $AzureContext  -Parameters $params -Wait
}
else
{
    Write-Output "conditionfailed the value of $LastStep"
}

You can refer this documentation, about modular runbooks in azure automation.