I am trying to run following powershell script. How Can I pass parameter value for @P0= '2019-11-22' and @P1 = '123456789' during runtime?

$sqlconnection = New-Object System.Data.SqlClient.SqlConnection
$sqlconnection.ConnectionString = "server = Servername;Database=TestDB;Integrated security=true"

$sqlCmnd = New-OBJECT SYSTEM.DATA.SqlClient.SqlCommand
$sqlCmnd.Connection = $sqlconnection
$sqlCmnd.CommandText = "select distinct table1.column1, ..........  where table2.column='value' and table2.coulumn=@P0 and table2.column=@P1 order by table1.Column1"

$sqlCmnd.Parameters.ADD((New-OBJECT DATA.SQLClient.SQLParameter("@P0",[Data.SQLDBType]::varchar, 8000))) | OUT-NULL
$sqlCmnd.Parameters.ADD((New-OBJECT DATA.SQLClient.SQLParameter("@P1",[Data.SQLDBType]::varchar, 8000))) | OUT-NULL


You can use PowerShell variables or script parameters to update in your script:

$P0 = "Value 0"
$P1 = "Value 1"

$sqlCmnd.Parameters.ADD((New-OBJECT DATA.SQLClient.SQLParameter("P0",[Data.SQLDBType]::varchar, 8000))).Value = $P0
$sqlCmnd.Parameters.ADD((New-OBJECT DATA.SQLClient.SQLParameter("P1",[Data.SQLDBType]::varchar, 8000))).Value = $P1
  • Thanks. How do I pass different value instead of hard coding it? I am planning to run this script multiple times with different value.
    – SqlDBA
    Nov 26 '19 at 16:42
  • You can assign a value to a script parameter or a variable, I updated the answer.
    – Piotr
    Nov 26 '19 at 17:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.