I'm trying to call a Microsoft SQL Server 2000 stored procedure from DBI that will
return a result set as well as a value via a variable bound using bind_param_inout.
Consider the following stored procedure:
----- Begin stored procedure -----
CREATE PROCEDURE foo
(@foo_out INTEGER OUTPUT)
AS
SELECT @foo_out = 2 + 3
SELECT * FROM some_table
----- End stored procedure -----
And consider the following Perl code:
----- Begin Perl snippet -----
my $foo_out = 0;
$sth = $dbh->prepare("EXEC foo ?");
$rv = $sth->bind_param_inout(1, \$foo_out, 10);
$rv = $sth->execute;
----- End -----
If the line "SELECT * FROM some_table" is removed from the stored procedure foo, then
the Perl variable $foo_out will receive the value 5 after $sth->execute (although
there won't be a result set to fetch). But if you add the "SELECT * FROM some_table"
line back in, then you can use one of the fetch functions within Perl to get the
result set after $sth->execute, but the value of $foo_out stays zero. Am I asking for
too much, or is there a trick for making this work?
My specs:
Perl v. 5.6.1 (ActiveState build 631, under Microsoft Windows 2000)
DBI v. 1.23
DBD::ODBC v. 0.41
Thanks for any ideas you can share,
- Kennis
---- Kennis Koldewyn (koldewyn<at>cloud9.net) ----
Without computers, it would be virtually impossible
for us to accompliowur xow;gkc,mf(&( - Dave Barry
