TVP not being supplied in stored procedure call from Powershell

Question

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.

How is your stored procedure declared? You're not actually passing any table here. — Jeroen Mostert, Aug 14 '20 at 09:24
Table-Valued Parameters are passed in as DataTables with SqlDbType.Structured. — AlwaysLearning, Aug 14 '20 at 09:31
Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Aug 14 '20 at 09:43

score 1 · Answer 1 · answered Aug 14 '20 at 13:00

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

TVP not being supplied in stored procedure call from Powershell

1 Answers1