An introduction to parametrised queries with DbaTools

Many of us are likely aware of PowerShell, even if we don't use it too frequently, and I suspect that if you're reading this post you're also familar with things like sqlcmd. Hopefully, you have also heard of DbaTools, a module for PowerShell (and if you haven't, hopefully that's why you're here). Today, I wanted to discuss running parametrised queries (including table type parameters) from PowerShell, which is notorious hard/impossible with sqlcmd (or invoke-SqlCmd), using the DbaTools module.

Running a basic query

Firstly, let's get the real basics down, and look at connecting to the SQL Server, and running a basic query with no parameters. The command in DbaTools to run a query is Invoke-DbaQuery. A simple query might look something like this:

$Query = 'SELECT object_id, name AS object_name FROM sys.tables;'
Invoke-DbaQuery -SqlInstance 'YourServerName' -Database Sandbox -Query $Query

This would use your windows credentials to connect to the SQL Server instance, and return a dataset. If you wanted to pass credentials, I suggest using a PSCredential, as this enabled you to provide both SQL Authentication or a different AD account's credentials. There a few ways to do this, and I'm using Example 4 from the Documentation:

$User = "TestLogin"
$Password = Read-Host -Prompt 'Enter Password' -AsSecureString
$Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User, $Password
Invoke-DbaQuery -SqlInstance 'YourServerName\YourInstance' -SqlCredential -Query 'SELECT name FROM sys.databases;'

This works for AD accounts as well; just pass an AD accounts name (MyDomain\MyUser) to $User instead. This is very useful for when you want to test a service account's permissions.

Passing your first parameter

DbaTools has a command for defining SQL parameters, called New-DbaSqlParameter. This enables us to not only define the value, but also things like the data type (and precision/scale/length), direction (input/output/return), and several other features I'm not touching on today. So, for example, let's say we want to parameterise our query that returned the list of databases to now only return databases starting with a specific letter. We can therefore do the following:

$PrefixValue = 'S'
$PrefixParameter = New-DbaSqlParameter -ParameterName Prefix -Value $PrefixValue -SqlDbType NVarChar -Size 256

Invoke-DbaQuery -SqlInstance 'YourServerName\YourInstance' -Query "SELECT name FROM sys.databases WHERE name LIKE @Prefix + '%';" -SqlParameter $PrefixParameter

The SqlDbType parameter accepts most of the in-built datatype (it doesn't support CLI data types like geography, or alias types like sysname). It does also have support for User Defined Types (UDTs), however, considering I find alias types more of a hinderance, I'm not going to cover it more than to state you'll also need to pass the type to the UdtTypeName parameter.

Passing multiple parameters

Dealing with multiple parameters really isn't much different. Define all your parameters using New-DbaSqlParameter and then, instead, when you pass a value to the -SqlParameter parameter, you delimit each SQL parameter defined with a comma:

$SchemaIdValue = 1
$TablePrefixValue = 'S'

$SchemaIdParameter = New-DbaSqlParameter -ParameterName SchemaID -Value $SchemaIdValue -SqlDbType int
$TablePrefixParameter = New-DbaSqlParameter -ParameterName TablePrefix -Value $TablePrefixValue -SqlDbType NVarChar -Size 256

Invoke-DbaQuery -SqlInstance 'YourServerName\YourInstance' -Database YourDatabase -Query "SELECT name FROM sys.tables WHERE schema_id = @SchemaID AND name LIKE @TablePrefix + N'%';" -SqlParameter $SchemaIdParameter, $TablePrefixParameter

Table type parameters

As mentioned before, table type parameters are also supported, which is a significant advantage over tools like sqlcmd. The logic to define them is a "little" clunky in my mide, however it's not too difficult (I'm sure if you end up using it often enough you'll remember it) and the fact that the support is there is, as I mentioned, a very welcome feature.

To use a table type parameter you will (obviously) need a table type defined in the database you're connecting to. Then you'll define an array in powershell, fill with PSCustomObjects for each row converting into a data table (using ConvertTo-DbaDataTable), which you can then pass to New-DbaSqlParameter in a similar fashion to the prior scalar examples. We're going to use a TYPE of the following definition here:

CREATE TYPE dbo.SingleNChars AS table (SingleNCharacter nchar(1));

Then we can use that TYPE to perform a similar query to the prior as follows:

$Query = "
SELECT name
FROM sys.tables t
WHERE EXISTS(SELECT 1
             FROM @Prefixes P
             WHERE t.name LIKE P.SingleNCharacter + N'%');"

$Characters = @()
$Characters += [PSCustomObject]@{SingleNCharacter = 'S'}
$Characters += [PSCustomObject]@{SingleNCharacter = 'T'}
$CharactersDT = ConvertTo-DbaDataTable -InputObject $Characters

$PrefixesParameter = New-DbaSqlParameter -Parametername Prefixes -SqlDbType Structured -Value $CharactersDT -TypeName 'dbo.SingleNChars'

Invoke-DbaQuery -SqlInstance 'YourServerName\YourInstance' -Database YourDatabasex -Query $Query -SqlParameter $PrefixesParameter

If you are dealing with a table type with multiple columns, then you just need to expand your PSCustomObject appropriately. For example:

$Customers = @()
$Customers += [PSCustomObject]@{FirstName = 'John'
                                LastName = 'Smith'
                                DateOfBirth = '20010417'}
$Customers += [PSCustomObject]@{FirstName = 'Jane'
                                LastName = 'Brown'
                                DateOfBirth = '19980902'}

Hopefully this gives you a small insight into using DbaTools and parametrised queries from it. If you aren't already using DbaTools alongside PowerShell and SQL Server, I really recommend taking a look; there's a wealth of features it provides. This is just one small solution it does have, that (at least in my day to day use of PowerShell) had a significant benefit.