Using sp_executesql with PowerShell

 The system extended stored procedure sp_executesql is recommended in general to execute dynamic SQL to protect from SQL injection attack. Using sp_execute in PowerShell is like using any other stored procedure in PowerShell. But I prefer to do it in ADO.NET instead of using dynamic CmdLets like Invoke-SqlCmd. The reason is that I have a rather tight control with the connectivity and can make the call really fast.

The example below is a test on if a database exists with the given name. I have chosen to implement the test with a COUNT() function call so that the result always is the same type (integer) no matter if the database exists or not. Also COUNT() is usually very effective in the statement execution.

First I give the database name to look for:

$DbName = 'SSISDB'

Then I prepare the call and set up the connection to the SQL Server installation:

$SqlInstanceName = '(local)\MSSQL2019'
$CnnStr = "Server=$SqlInstanceName;Integrated Security=SSPI"
$SqlStatement = 'SELECT COUNT(*) AS [db_count] FROM [master].[sys].[databases] WHERE [name]=@dbname;'
$SqlParameters = '@dbname sysname'
$CnnSql = New-Object System.Data.SqlClient.SqlConnection
$CnnSql.ConnectionString = $CnnStr
$CnnSql.Open()

Then I call sp_executesql with the query and get the result. I close the connection as soon as possible to release the session resources:

$CmdSql = New-Object System.Data.SqlClient.SqlCommand
$CmdSql.Connection = $CnnSql
$CmdSql.CommandText = '[master].[sys].[sp_executesql]'
$CmdSql.CommandType = [System.Data.CommandType]::StoredProcedure
$CmdSql.Parameters.Add('@stmt', [System.Data.SqlDbType]::NVarChar, -1).Value = $SqlStatement
$CmdSql.Parameters.Add('@params', [System.Data.SqlDbType]::NVarChar, 500).Value = $SqlParameters
$CmdSql.Parameters.Add('@dbname', [System.Data.SqlDbType]::NVarChar, 128).Value = $DbName
$Rdr = $CmdSql.ExecuteReader()
while ($Rdr.Read()) { $DbCount = $Rdr['db_count'] }
$Rdr.Close()
$CnnSql.Close()

Afterwards I can work with the result from the query:

"Db Count: $DbCount"

This example can be used for calling any stored procedure.