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
