Powershell Stored Procedure with User-defined type parameter

Question

My SQL Server stored procedure is as follows:

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

In Powershell: under global definition, I am declaring the $connection, and ArrayList.

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

Inside of the Method, I'm converting the ArrayList to a DataTable, and supplying it as a parameter to my stored procedure.

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()
}

However, when I execute this, I get the following exception:

Exception calling "ExecuteNonQuery" with "0" argument(s): "Conversion failed when converting the nvarchar value 'System.Collections.ArrayList' to data type int.
The data for table-valued parameter "@assessmentIds" doesn't conform to the table type of the parameter.
SQL Server error is: 245, state: 1

I'm really not sure what I'm doing wrong.

You have this: $table.Columns.Add("AssessmentId", "System.String") But your SQL table type appears to be int? Maybe this needs to match the SQL data type — Brad
– Brad, Commented Mar 4, 2019 at 18:12
Also I think you need to give the parameter an explicit type (structured), but I'll confess I know that from C#, I've never tried it from Powershell. — Aaron Bertrand
– Aaron Bertrand, Commented Mar 4, 2019 at 18:24
As an aside:Please avoid pseudo method syntax: instead of New-Object SomeType(arg1, ...), use New-Object SomeType [-ArgumentList] arg1, ... - PowerShell cmdlets and functions are invoked like shell commands (no parentheses, whitespace-separated arguments), not like methods. — mklement0
– mklement0, Commented Mar 4, 2019 at 19:14

Ali Khakpouri · Accepted Answer · 2019-03-04 19:11:27Z

After reading this post, I altered the SqlParameters, and I was able to get it to work.

$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()

Collectives™ on Stack Overflow

Powershell Stored Procedure with User-defined type parameter

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related