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:

HowToCallSPROCFromPowershell_Example_Table_Rows

And here are the two underlying tables:
HowToCallSPROCFromPowershell_SQL_Tables

 

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

Filed under: Powershell