Executing a sql procedure with parameters from Powershell

Question

I'm completely new to Powershell so i'm a little confused about how to call a SQL procedure that takes parameters. I have opened a connection to my database successfully and i've managed to get a procedure that doesn't take parameters to work so I know that the connection is fine.

The code to add a parameter and run the query is below:

$dateToUse = Get-Date -f yyyy/MM/dd
$MysqlQuery.CommandText = "GetJourneyByDepartureDate"
$MysqlQuery.Parameters.AddWithValue("_departureDate", $dateToUse)
$queryOutput = $MysqlQuery.ExecuteReader()

Whenever I try and run my script I get an error saying

Incorrect number of arguments for PROCEDURE dbo.GetJourneyByDepartureDate; expected 1, got 0

I've had a look around trying to find a solution but I don't understand enough about Powershell to know what solutions might be correct.

Also I am unable to post the SQL query but I have managed to run the procedure many times by just running the query in HeidiSQL passing the arguement manually

EDIT:

I've now changed my code slightly, it now looks like this:

$MysqlQuery.CommandText = "GetJourneyByDepartureDate"
$MysqlQuery.Parameters.Add("@_departureDate", [System.Data.SqlDbType]::Date) | out-Null
$MysqlQuery.Parameters['@_departureDate'].Value = $dateToUse
$parameterValue = $MysqlQuery.Parameters['@_departureDate'].value

Write-Host -ForegroundColor Cyan -Object "$parameterValue";
$queryOutput = $MysqlQuery.ExecuteReader()

I'm getting the $dateToUse value output on the console in the Write-Host line but i'm still getting the same Incorrect number of arguments error as before. SP is declared as below:

CREATE PROCEDURE `GetJourneyByDepartureDate`(IN `_departureDate` DATE) READS SQL DATA

Try: $MysqlQuery.Parameters.AddWithValue("@departureDate", $dateToUse). — wchiquito
– wchiquito, Commented Dec 10, 2015 at 18:30

RyuAkamatsu · Accepted Answer · 2016-01-19 14:19:39Z

In the end I found that I needed to set the CommandType to be StoredProcedure and also I needed to add the parameter but I was missing the direction and I apparently had to add a space after the '@' but i'm not sure why. My solution is below:

    $MysqlCommand = New-Object MySql.Data.MySqlClient.MySqlCommand.Connection = $connMySQL               #Create SQL command
    $MysqlCommand.CommandType = [System.Data.CommandType]::StoredProcedure;                              #Set the command to be a stored procedure

    $MysqlCommand.CommandText = "GetJourneyByDepartureDate";                                         #Set the name of the Stored Procedure to use
    $MysqlCommand.Parameters.Add("@ _departureDate", [System.Data.SqlDbType]::Date) | out-Null;          #Set the input and output parameters
    $MysqlCommand.Parameters['@ _departureDate'].Direction = [system.data.ParameterDirection]::Input;    #Set the _departureDate parameter to be an input parameter
    $MysqlCommand.Parameters['@ _departureDate'].Value = $dateToUse;                                     #Set the _departureDate parameter value to be dateToUse

Collectives™ on Stack Overflow

Executing a sql procedure with parameters from Powershell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related