How Call a SQL Stored Procedure from Powershell

This blog discusses how to call a stored procedure that takes several input parms, but doesn’t return any data. For instance, you are calling a stored proc to insert data into one or more database tables.

When you are not expecting data back, you don’t need the DataAdapter nor the DataTables classes. Use the “ExecuteNonQuery” method of the command object. (Note: It can return one value such as an integer, count, or success flag, but it doesn’t return any rows of data, i.e. no result set.)

function GetConnectionString()
{
  return "Server=.;Database=VideoGenerator;Integrated Security=False;User ID=myusername;Password=mypassword"
}

function LogVideoTrackingToSQL($license, $keyword, $computerName, $videoFilename, $templateName, $createdDateTimeUTC, $uploadedDateTimeUTC) 
{
    Write-Host "license=$license"
    Write-Host "keyword=$keyword"
    Write-Host "computerName=$computerName"
    Write-Host "videoFilename=$videoFilename"
    Write-Host "templateName=$templateName"
    Write-Host "createdDateTimeUTC=$createdDateTimeUTC"
    Write-Host "uploadedDateTimeUTC=$uploadedDateTimeUTC"

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = GetConnectionString

    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = "dbo.LogVideo"  ## this is the stored proc name 
    $SqlCmd.Connection = $SqlConnection  
    $SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure  ## enum that specifies we are calling a SPROC 

      #set each of the 7 parameters 

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

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

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

      $param4=$SqlCmd.Parameters.Add("@VideoFilename" , [System.Data.SqlDbType]::VarChar)
      $param4.Value = $videoFilename 
      
      $param5=$SqlCmd.Parameters.Add("@TemplateName" , [System.Data.SqlDbType]::VarChar)
      $param5.Value = $templateName 

      $param6=$SqlCmd.Parameters.Add("@CreatedDateTimeUTC" , [System.Data.SqlDbType]::DateTime2)
      $param6.Value = $createdDateTimeUTC 

      $param7=$SqlCmd.Parameters.Add("@UploadedDateTimeUTC" , [System.Data.SqlDbType]::DateTime2)
      $param7.Value = $uploadedDateTimeUTC 

    $SqlConnection.Open()
    $result = $SqlCmd.ExecuteNonQuery() 
    Write "result=$result" 
    $SqlConnection.Close()
}

CLS 
$license = "4F926ADB-8193-4824-9881-DF147B721889"
$keyword = "test"
$computerName = "PC45"
$videoFilename = "c:\videos\abcdefg.mp4" 
$templateName = "Customer_Appliance4.prj"
$getUTCdate = $(get-date).ToUniversalTime()
LogVideoTrackingToSQL $license $keyword $computerName $videoFilename $templateName $getUTCdate $getUTCdate

To pass a Null (DBNULL) in Powershell, check out this post (use the [System.DBNull]::Value).

So you have the entire end–to-end picture, here is the stored proc being called:

ALTER PROCEDURE [dbo].[LogVideo] 
    @License varchar(36),
  @Keyword varchar(120), 
  @ComputerName varchar(50), 
  @TemplateName varchar(120), 
  @VideoFileName varchar(255), 
  @CreatedDateTimeUTC dateTime2(7), 
  @UploadedDateTimeUTC dateTime2(7)
AS
BEGIN

   /*  Unit Test 

      Declare @utcDate datetime2 = getUTCDate() 
      Exec LogVideo '4F926ADB-8193-4824-9881-DF147B721889', 'Sql-Test', 'Machine-A', 'Customer-abc.prj', 
                   'c:\Videos\SQL-Test.mp4', @utcDate, @utcDate

   */ 

  declare @CustomerID int 
  declare @ErrMessage varchar(500) 

  select @CustomerID = ID from Customer C where C.LicenseGuid = @License 

  if @CustomerID is null 
    BEGIN  
      SET @ErrMessage = 'No customer license found for key=' + @License 
      RAISERROR (@ErrMessage, 11,1)
    END

    insert into VideoTracking 
     (CustomerID, CreatedDateTimeUTC, UploadedDateTimeUTC, Keyword, ComputerName, TemplateName, VideoFilename) 
  values 
     (@CustomerID, @CreatedDateTimeUTC, @UploadedDateTimeUTC, @Keyword, @ComputerName, @TemplateName, @VideoFilename) 

/* History 
01/12/2015 - Neal Walters - Initial Version 
*/ 

END

Below show the results of running a query against the stored data: