Execute TSQL from Powershell

I find this function useful when I need to execute simple query from Powershell script. Since I rarely remember exact syntax I am going to leave here small snippet of code for future reference.

function f_ExecuteSQLQuery ([string]$ServerInstance, [string] $dbName, [string] $sqlQuery)

{

 

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

$SqlConnection.ConnectionString="Server=$ServerInstance;Database=$dbName;Integrated Security=True"

$SqlCmd=New-Object System.Data.SqlClient.SqlCommand

$SqlCmd.CommandText=$sqlQuery

$SqlCmd.Connection=$SqlConnection

$SqlCmd.CommandType= [System.Data.CommandType]'Text';

$SqlConnection.Open();

 

$results=$SqlCmd.ExecuteReader();;

 

return$results

$SqlConnection.Close();

}

 

and similar for Stored Procedure call:

 

 function f_ExecuteSQLSP ([string]$ServerInstance, [string] $dbName, [string] $sqlQuery){
    

        
            $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
            $SqlConnection.ConnectionString = "Server=$ServerInstance;Database=$dbName;Integrated Security=True"
                
            
            $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
            $SqlCmd.CommandText = "exec dbcc_opentran"
            $SqlCmd.Connection = $SqlConnection
            $SqlCmd.CommandType = [System.Data.CommandType]'StoredProcedure';
            
            $ip = new-object System.Data.SqlClient.SqlParameter;
            $ip.ParameterName = "@trnpath";
            $ip.Direction = [System.Data.ParameterDirection]'Input';
            $ip.DbType = [System.Data.DbType]'String';
            $ip.Value = "$rf";
            $ip.Size = 2500
            
            $SqlCmd.Parameters.Add($ip)>> $null;
            
            $iip = new-object System.Data.SqlClient.SqlParameter;
            $iip.ParameterName = "@dbanme";
            $iip.Direction = [System.Data.ParameterDirection]'Input';
            $iip.DbType = [System.Data.DbType]'String';
            $iip.Value = "$dbnameparam";
            $iip.Size = 2500
            
            $SqlCmd.Parameters.Add($iip)>> $null;
            
            $SqlConnection.Open();
            
            $results = $SqlCmd.ExecuteNonQuery();;
            
            
            }

            $SqlConnection.Close();
            }