New-DbaSqlParameter does not handle 0 value integer

[mKarous]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

The parameterized query '(@zeroint int)SELECT @zeroint' expects the parameter '@zeroint', which was not supplied.
At line:98163 char:9
throw $records[0]
CategoryInfo : NotSpecified: (HI-INT-SQL01.cis.local:String) [], Exception
FullyQualifiedErrorId : dbatools_Invoke-DbaQuery

Steps to Reproduce

[int]$ZeroInt = 0

$ZeroSqlParam = New-DbaSqlParameter -ParameterName ZeroInt -Value $ZeroInt -SqlDbType int

Invoke-DbaQuery -SqlInstance myInstance -Database master -Query 'SELECT @zeroint' -SqlParameter $ZeroSqlParam -EnableException

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

2.1.26

Other details or mentions

Works fine for any $ZeroInt <> 0 value.

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe)

PowerShell Host Version

Name Value

---

PSVersion 5.1.20348.2110
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.20348.2110
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

SQL Server Edition and Build number

Microsoft SQL Server 2022 (RTM-CU12-GDR) (KB5036343) - 16.0.4120.1 (X64) Mar 18 2024 12:02:14 Copyright (C) 2022 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Datacenter 10.0 (Build 17763: ) (Hypervisor)

.NET Framework Version

.NET Framework 4.8.4645.0

[mattcargile]

Interesting. So the below does work where you set .Value after defining the object. Otherwise .Value is "empty". Still working to figure out why this happens. It could be a combination of the way the parameter is defined on New-DbaSqlParameter and how it handles 0?

Additionally, using -SqlValue works without the extra assignment instead of -Value.

[int]$ZeroInt = 0
$ZeroSqlParam = New-DbaSqlParameter -ParameterName ZeroInt -Value $ZeroInt -SqlDbType int
$ZeroSqlParam.Value = 0
Invoke-DbaQuery -SqlInstance myInstance -Database master -Query 'SELECT @zeroint' -SqlParameter $ZeroSqlParam -EnableException

EDIT 1: So the below logic block doesn't fire. I assume this is because of the interplay with [object] and $PSBoundParameters or one or the other. Maybe [string] would work better here or some type of default assignment for $null [object]'s?

dbatools/public/New-DbaSqlParameter.ps1

Lines 203 to 205 in f5e587f

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