PowerShell execute SQL Server SP wtih Parameters

Powershell can easily communicate with SQL Server and can execute Stored Procedures.

Following sample code can execute a Stored Procedures with Parameter.
if you stored Procedure is returning some data please see the function “getDATAwithSP” described above,

function getConnectionString(){
		return "Data Source=localhost;Initial Catalog=MYDATABASE;integrated security=false;user id=myUSER;Password=myPASSwd";
	}
function execSQLsp {param([int] $param)
	try{
		$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
		$SqlConnection.ConnectionString = getConnectionString
		$SqlConnection.open()
		$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
		$SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
		$SqlCmd.CommandText = "mySQLspName"
		$param1=$sqlcmd.Parameters.Add("@param" , [System.Data.SqlDbType]::Int)
		$param1.Value = $param
		$SqlCmd.Connection = $SqlConnection
		$tmp=$SqlCmd.ExecuteNonQuery()
		$SqlConnection.Close()
	}
	catch{
		if($SqlConnection.State -eq "Open"){
			$SqlConnection.Close()
		}
		return -1
	}
}
##########################
function getDATAwithSP {
	try{
		$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
		$SqlConnection.ConnectionString = getConnectionString;
		$SqlConnection.open()
		$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
		$SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
		$SqlCmd.CommandText = "mySQLspNameWithDATA"
		$SqlCmd.Connection = $SqlConnection
		$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
		$SqlAdapter.SelectCommand = $SqlCmd
		$DataSet = New-Object System.Data.DataSet
		$TMP=$SqlAdapter.Fill($DataSet)
		$DataTable = New-Object System.Data.DataTable
		$DataTable = $DataSet.Tables[0]
		if ($DataTable.Rows.Count -gt 0){
			for($i=0;$i -lt $DataTable.Rows.Count;$i++)
			{
				$varx=$DataTable.Rows[$i]["COLUMN_NAME"].ToString()
			}
		}
		$SqlConnection.Close()
	}
	catch{
		if($SqlConnection.State -eq "Open"){
			$SqlConnection.Close()
		}
		return -1;
	}
}

execSQLsp 10

Please note above highlighted lines, If you do not store the values returned on above lines functions start behaving abnormally and returned values are appended with “1” and returned becomes Array;