Re: Table-Valued Parameters to MS SQL Server stored procedure

On 2013.12.30 5:23 AM, Jürgen Wenzel wrote:
> Using temporary tables, or any other database workaround, isn't an option for
> me, so unless there's a way to use TVPs with stored procedures I'll probably
> have to abandon Perl (which, except for being extremely sad for me and very
> satisfying for the non-Perl believers, will be extremely time consuming).

So do you not have any influence on things stored in the database?  How many 
stored procedures are we talking about with PVPs?

While a kludge, one thing you could try is having the SQL the Perl invokes not 
just invoke the stored procedure normally but also inline a TABLE value literal, 
eg I think rather than calling "foo(?)" you could say "foo(VALUES 
(?,?),(?,?)...)" though this wouldn't scale very well, it may handle your 
thousands of rows.

Does your TPV stored procedure work piecemeal?  Eg, if you were normally calling 
it with a certain 100 rows, could you call it 5 times with 20 rows each and the 
proper behavior would happen?  Or are there constraints such that it won't work 
unless the 100 are provided all at once?

If you can piecemeal, that should simplify things for you, at the Perl end you 
can call it in a loop with say 1 row at a time with a 1-row TABLE literal.

-- Darren Duncan