CODEWITHSUNDEEP
sql-serverpowershellstored-procedurestable-valued-parameters
llanato
llanato

Reputation: 2491

TVP not being supplied in stored procedure call from Powershell

Can't figure out where I'm going wrong here, the TVP in the stored procedure references @asn but I keep getting the below error/warning, I've tried everything, there is no further details in the sql server logs, any help appreciated.

PowerShell function:

Function Execute-Procedure {
    Param(
        [Parameter(Mandatory=$true)][array]$p
    )
    Process {
        $connectionString = "Server=;Database=;User ID=;Password=;Trusted_Connection=True"
        $conn = New-Object System.Data.SqlClient.SqlConnection $connectionString

        $cmd = New-Object System.Data.SqlClient.SqlCommand
        $cmd.Connection = $conn
        $cmd.CommandTimeout = 0
        $pvar = ("V-" + $p.ToString())
        $sqlParam = New-Object System.Data.SqlClient.SqlParameter("@asn", $pvar)
        $null = $cmd.Parameters.Add($sqlParam, [System.Data.SqlDbType]::NVarChar)
        $cmd.CommandText = "EXEC tsp_insert_asn @asn"
        
        try {
            $conn.Open()
            $cmd.ExecuteNonQuery() | Out-Null
        } catch [Exception] {
            Write-Warning $_.Exception.Message
        } finally {
            $conn.Dispose()
            $cmd.Dispose()
        }
    }
}

Stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  PROCEDURE [dbo].[tsp_insert_asn]
    @asn AS [dbo].[tvp_asn] READONLY
AS
BEGIN   
    INSERT INTO [dbo].[asn]
        SELECT  asn_id
            , name
            , size
            , location
            ,GETDATE()
        FROM @asn;
END

Error:

WARNING: The parameterized query '(@asn nvarchar(4000))EXEC tsp_insert_asn @asn' expects the parameter '@asn', which was not supplied.

Upvotes: 0

Views: 358

Answers (1)

Bacon Bits
Bacon Bits

Reputation: 32170

First create a datatable with the same schema as the user-defined type (UDT):

$DataTable = New-Object -TypeName System.Data.DataTable
$DataColumn = New-Object -TypeName System.Data.DataColumn -ArgumentList 'asn_id'  , ([String])
$DataTable.Columns.Add($DataColumn)
$DataColumn = New-Object -TypeName System.Data.DataColumn -ArgumentList 'name'    , ([String])
$DataTable.Columns.Add($DataColumn)
$DataColumn = New-Object -TypeName System.Data.DataColumn -ArgumentList 'size'    , ([String])
$DataTable.Columns.Add($DataColumn)
$DataColumn = New-Object -TypeName System.Data.DataColumn -ArgumentList 'location', ([String])
$DataTable.Columns.Add($DataColumn)

Note that I created ever column as a string. Don't do that! Your columns should match the data type of the UDT columns. At this point I can't remember if you're supposed to use SQL data types or .Net data types, but I'm sure it will be obvious if it doesn't work.

Now populate the data table from your array.

foreach ($i in $p) {
    $DataRow = $DataTable.NewRow()
    $DataRow.'asn_id'   = $i.'asn_id'  
    $DataRow.'name'     = $i.'name'    
    $DataRow.'size'     = $i.'size'    
    $DataRow.'location' = $i.'location'
    $DataTable.Rows.Add($DataRow)
}

Now connect and execution your stored procedure with the datatable as the value of your parameter with the Structured data type.

$conn = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList $connectionString
$cmd = New-Object -TypeName System.Data.SqlClient.SqlCommand -ArgumentList $conn
$cmd.CommandType = [System.Data.CommandType]::StoredProcedure
$cmd.CommandText = 'tsp_insert_asn'
$cmd.Parameters.Add("@asn",[System.Data.SqlDbType]::Structured).Value = $DataTable

try {
    $conn.Open()
    $null = $cmd.ExecuteNonQuery()
}
finally {
    $conn.Close()
}

Upvotes: 1

Related Questions