Call a SQL Stored Procedure from Powershell with a parameter

Recently I had to write a Powershell script that iterates a text file of user names and calls a stored procedure  to delete users from a database. The text file just contains account names on each line and must be in the same directory as the Powershell script.

$conn = new-Object System.Data.SqlClient.SqlConnection("Data Source=databaseservername;User ID=sa; Password=password; Initial Catalog=databasename")
foreach ($user in Get-Content “UserList.txt”)

$conn.Open() | out-null
$cmd = new-Object System.Data.SqlClient.SqlCommand("SP_DeleteUser_By_AccountName", $conn)

$cmd.CommandType = [System.Data.CommandType]::StoredProcedure
$cmd.Parameters.Add("@AccountName",[system.data.SqlDbType]::VarChar) | out-Null

$cmd.Parameters['@AccountName'].Direction = [system.data.ParameterDirection]::Input

$cmd.Parameters['@AccountName'].value = $user



No comments:

Post a Comment