How to get a single return value from an ExecuteNonQuery SQL stored procedure in Powershell

Sometimes you call SQL just to store data, but even then, sometimes you need the key of the row just stored. This code will show you how to get back that single return value when calling a stored proc, even when it is an “ExecuteNonQuery” stored proc, that returns no row sets. It is typical to return either a key value or a status/result code in such as scenario.

This is a follow-up to an early blog How Call a SQL Stored Procedure from Powershell.

Powershell SQL Functions

This example also illustrates how to convert a Powershell boolean field from $true/$false to SQL bit value of 1 or 0.

function GetConnectionString()
{
  return "Server=myServer;Database=myDatabase;Integrated Security=False;User ID=myuser;Password=mypass"
}

function LogVideoComputerToSQL($license, $computerName, $CPUProcessorName, $GraphicsProcessorName, $IsLaptop, $NumLogicalProcessors, $MaxClockSpeed) 
{
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = GetConnectionString #call little function above to get connection string 
 
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = "dbo.LogComputer"  #name of the stored proc here 
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure 

      #set each of the 7 parameters 

      $param1=$SqlCmd.Parameters.Add("@License" , [System.Data.SqlDbType]::VarChar)
      $param1.Value = $license 

      $param2=$SqlCmd.Parameters.Add("@computerName" , [System.Data.SqlDbType]::VarChar)
      $param2.Value = $computerName 

      $param3=$SqlCmd.Parameters.Add("@CPUProcessorName" , [System.Data.SqlDbType]::VarChar)
      $param3.Value = $CPUProcessorName 

      $param4=$SqlCmd.Parameters.Add("@GraphicsProcessorName" , [System.Data.SqlDbType]::VarChar)
      $param4.Value = [String] $GraphicsProcessorName 
      
      $param5=$SqlCmd.Parameters.Add("@IsLaptop" , [System.Data.SqlDbType]::bit)
      if ($IsLaptop) 
        {
           $param5.Value = 1
        }
      else 
        {
           $param5.Value = 0
        }

      $param6=$SqlCmd.Parameters.Add("@NumLogicalProcessors" , [System.Data.SqlDbType]::int)
      $param6.Value = [string] $NumLogicalProcessors 

      $param7=$SqlCmd.Parameters.Add("@MaxClockSpeed" , [System.Data.SqlDbType]::int)
      $param7.Value = [string] $MaxClockSpeed 

      #TWO LINES BELOW ARE WHAT YOU ARE LOOKING FOR 
      $paramReturn = $sqlCmd.Parameters.Add("@ReturnValue", [System.Data.SqlDbType]"Int")
      $paramReturn.Direction = [System.Data.ParameterDirection]"ReturnValue"

    $SqlConnection.Open()
    $result = $SqlCmd.ExecuteNonQuery()   #call the stored proc here 
    #### LINE BELOW IS THE RETURN VALUE #### 
    $returnValue = [int]$sqlCmd.Parameters["@ReturnValue"].Value    #####<------ Here is the return value moved to var. 
    Write-Host "result=$result returnValue=$returnValue" 
    $SqlConnection.Close()
    return $returnValue
}

Code to call the above function

There is some interesting code here as well. It shows how to retrieve some information from WMI about the CPU and graphics card; that is what we are passing to the subroutine. $computerID is the variable being returned.

    $license = "my-license-key"
    #Get-WmiObject Win32_Processor | Select-Object * 
    $Win32Processor =Get-wmiobject Win32_Processor  
    $ProcessorName        = $Win32Processor | select-object -expand Name
    $NumLogicalProcessors = $Win32Processor | select-object -expand NumberOfLogicalProcessors
    $MaxClockSpeed        = $Win32Processor | select-object -expand MaxClockSpeed 
    $ComputerName         = $Win32Processor | select-object -expand PSComputerName

    $isLaptop = isLaptop ## call function 

    $VideoController = get-wmiobject -class CIM_VideoController
    $GraphicsProcName = $VideoController | select-object -expand Name
    $VideoProcessor   = $VideoController | select-object -expand VideoProcessor
    $VideoProcessor2  = $VideoController | select-object VideoProcessor

    Write-Host "`n`nCalling SQL Function"
    $computerID = LogVideoComputerToSQL</strong> $license $ComputerName $ProcessorName $GraphicsProcName $IsLaptop $NumLogicalProcessors $MaxClockSpeed 
    Write-Host "computerID=$computerID"

The T-SQL Stored Proc code

I’m not going to bore you with the whole stored proc here, just the return statement. I’m basically doing an insert into a table with a primary key that is an integer and an identify field. The function “SCOPE_IDENTITY()” returns the identity key of the row just stored it. I can return it now to Powershell, so it can be used in other SQL calls to associate the other data that I’m storing back with the computer information.

   declare @ComputerID int 
   insert ...   
   set @ComputerID = SCOPE_IDENTITY()  
   return @ComputerID