Table Value Parameter for SQL server

Hi All,

I am attempting to bulk merge a lot of rows quickly to an mssql server and
I understand that table value parameters are a possible option for doing so.

I set up the following test objects on the sql server (modeled after a
python example of sorts from
https://github.com/mkleehammer/pyodbc/issues/595#issuecomment-584761512):

create table testtable (
  id int not null,
  primary key clustered (id asc)
);
GO

create type dbo.testtype as table (
  id int not null,
  primary key clustered (id asc)
);
GO

create procedure dbo.testproc(@tvp dbo.testtype READONLY)
as begin
  set nocount on;
  insert into testtable (id)
  select id from @tvp
end;
GO

And am attempting to use the following script (name tvf):

#!/usr/bin/perl

use DBI;

our $userid;
our $password;
our $dsn;

do( './tvf.conf' );

my $dbh = DBI->connect( $dsn, $userid, $password, { RaiseError => 1 });

my $sth = $dbh->prepare( qq{ exec dbo.testproc ? } );

my $table_values = [
                     [1],
                     [2],
                   ];

$sth->execute( $table_values );

And I receive the following error:
Cannot bind a plain reference at ./tvf line 20.

I believe that error comes from the odbc driver and am uncertain what, if
anything, can be done about it.

My DSN starts with "dbi:ODBC:DRIVER=tds;database=" which I believe means
that this driver is used:
/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

Has anyone had success in passing a tvf from perl via DBI?  Could anyone
share an example, please?

-- 
Thank you.

Regards,
Rich

• Table Value Parameter for SQL server by Rich Duzenbury
• Re: Table Value Parameter for SQL server by Keith Carangelo