How Call a SQL Stored Procedure from Powershell

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 

    $result = $SqlCmd.ExecuteNonQuery() 
    Write "result=$result" 

$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)

   /*  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 
      SET @ErrMessage = 'No customer license found for key=' + @License 
      RAISERROR (@ErrMessage, 11,1)

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

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


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


And here are the two underlying tables:


See Part 2 here – “How to catch SQL Stored Proc Errors in PowerShell“.


Leave a Reply