New-DbaSqlParameter - Method to check whether parameter is populated results in unexpected behavior

[chadbaldwin]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

Bug does not result in an error.

Steps to Reproduce

PS> New-DbaSqlParameter -ParameterName 'MyParam' -Value 0

CompareInfo                     : None
XmlSchemaCollectionDatabase     :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName         :
ForceColumnEncryption           : False
DbType                          : String
ParameterName                   : MyParam
LocaleId                        : 0
Precision                       : 0
Scale                           : 0
SqlDbType                       : NVarChar
SqlValue                        :
UdtTypeName                     :
TypeName                        :
Value                           :           <<< Issue here, should have been set to `0`
Direction                       : Input
IsNullable                      : False
Offset                          : 0
Size                            : 0
SourceColumn                    :
SourceColumnNullMapping         : False
SourceVersion                   : Current

Please confirm that you are running the most recent version of dbatools

2.1.6

Other details or mentions

The cmdlet is using this method for each parameter to check if it is being provided:

if ($PSBoundParameters.Value) {
    $param.Value = $Value
}

However, if the parameter value is 0, $null or $false, then it will fail the test and not get set.

Instead, $PSBoundParameters.ContainsKey('Value') should be used. $PSBoundParameters will only include a key for paramters which were populated by the caller. It ignores skipped parameters, even if they have a default.

Another option might be something like this, to make the code simpler:

function New-DbaSqlParameter {
    [CmdletBinding()]
    param(
        <# Params #>
    )

    try {
        $params = @{}
        $PSBoundParameters.Keys |
            Where-Object { $_ -NotIn ('EnableException') } | # Or use -In to be more strict and prevent future errors due to added parameters
            ForEach-Object { $params.Add($_, $PSBoundParameters[$_]) }

        New-Object Microsoft.Data.SqlClient.SqlParameter -Property $params
    } catch {
        Stop-Function -Message "Failure" -ErrorRecord $_
        return
    }
}

What PowerShell host was used when producing this error

PowerShell Core (pwsh.exe)

PowerShell Host Version

Name                           Value
----                           -----
PSVersion                      7.3.10
PSEdition                      Core
GitCommitId                    7.3.10
OS                             Microsoft Windows 10.0.19045
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

SQL Server Edition and Build number

N/A

.NET Framework Version

.NET 7.0.14