使用用户定义类型参数的Powershell存储过程

Question

我的SQL Server存储过程如下：

CREATE PROCEDURE ProcessAssessments 
    @assessmentIds AssessmentList READONLY,
    @failed BIT
AS
    UPDATE dbo.table
    SET IsProcessed = 1,
        dbo.ProcessFailed = @failed,
        dbo.ProcessDate = GETDATE()
    WHERE
        dbo.ID IN (SELECT AssessmentId FROM @assessmentIds)
GO

在 Powershell 中：在全局定义下，我正在声明 $connection 和 ArrayList。

$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$connection.Open()
$processedAssessments.Add("1")
$processedAssessments.Add("2")
$processedAssessments.Add("3")
UpdateAssessments($processedAssessments, 0)

在方法内部，我将 ArrayList 转换为 DataTable，并将其作为参数提供给我的存储过程。

function UpdateAssessments([System.Collections.ArrayList] $assessments,[bool] $failed) {
    $table = New-Object System.Data.DataTable
    $table.Columns.Add("AssessmentId", "System.String") | Out-Null

    foreach($assessmentId in $assessments) {
        $row = $table.NewRow()
        $row.AssessmentId = $assessmentId
        $table.Rows.Add($row)
    }

    $command = New-Object System.Data.SqlClient.SqlCommand
    $command.CommandType = [System.Data.CommandType]::StoredProcedure
    $command.CommandText = "ProcessKySpecialAssessments"
    $command.Connection = $connection

    $command.Parameters.Add("@assessmentIds", $table)
    $command.Parameters.Add("@failed", $failed)

    $Command.ExecuteNonQuery()
}

然而，当我执行此操作时，我会收到以下异常：

使用“0”个参数调用“ExecuteNonQuery”时出现异常：“将nvarchar值'System.Collections.ArrayList'转换为数据类型int失败。
表值参数“@assessmentIds”的数据不符合参数的表类型。
SQL Server错误为：245，状态为：1

我真的不知道我做错了什么。

- Ali Khakpouri

---

你有这个代码：$table.Columns.Add("AssessmentId", "System.String")，但是你的SQL表类型似乎是int？也许需要将其与SQL数据类型匹配。 - Brad

我认为您需要给参数一个明确的类型（结构化），但我承认我是从C＃学到这一点的，我从未尝试过在Powershell中实现它。 - Aaron Bertrand

顺便提一下：请避免伪方法语法：不要使用 New-Object SomeType(arg1, ...)，而是使用 New-Object SomeType [-ArgumentList] arg1, ... - PowerShell cmdlet 和函数的调用类似于 _shell 命令_（没有括号，空格分隔的参数），而不是 _方法_。 - mklement0

Answer 1

阅读完这篇文章之后，我修改了SqlParameters，最终成功实现。

$command.Connection = $connection
    $command.CommandType = [System.Data.CommandType]::StoredProcedure
    $command.CommandText = "ProcessKySpecialAssessments"

    $assessmentIdsParam = New-Object('system.data.sqlclient.sqlparameter')
    $failedParam = New-Object('system.data.sqlclient.sqlparameter')

    $assessmentIdsParam.ParameterName = "assessmentIds"
    $assessmentIdsParam.SqlDBtype = [System.Data.SqlDbType]::Structured
    $assessmentIdsParam.Direction = [System.Data.ParameterDirection]::Input
    $assessmentIdsParam.value = $table

    $failedParam.ParameterName = "failed"
    $failedParam.SqlDBtype = [System.Data.SqlDbType]::Bit
    $failedParam.Direction = [System.Data.ParameterDirection]::Input
    $failedParam.value = $failed

    $command.parameters.add($assessmentIdsParam);
    $command.parameters.add($failedParam);

    $command.ExecuteNonQuery()

- Ali Khakpouri