CODEWITHSUNDEEP
sqlsql-serverperlsql-server-2008stored-procedures
Isaac Pounder
Isaac Pounder

Reputation: 187

How to get return value from and SQL Stored Procedure Run Using Perl?

I am running a stored procedure in MS SQL using perl The return value is an integer (0 = if ok, and some negative values if it exited early at certain points) while the code runs the SP - it doesn't give me the return value at all.

my code:

use dbd::odbc;
#...
$dbh->do("use XXX"); #Name of the DB
my $sth = $dbh->prepare( "
   DECLARE \@return_value int

   EXEC \@return_value = [dbo].[test1] 
        \@BeginDate = '11/11/2011',
        \@EndDate = '11/11/2011'

   select  \@return_value 

");

$sth->execute( );

#### AFTER THIS I TRIED:

while ( @dbrow = $sth->fetchrow_array(  ) ) {
   $return_value = @dbrow[0]
}

#### i also tried:

my $return_value = $sth->fetchrow_array(  )

#### both get nothing.

When I use regular SELECT queries, both of the above work.

Upvotes: 0

Views: 2693

Answers (3)

jchips12
jchips12

Reputation: 1227

I'm not sure if this helps but can you try this one.

while ( $row_ref = $sth->fetch_arrayref() ) {
    my $return_value = $row_ref->[0];
    print $return_value;
}

Upvotes: 0

SAN
SAN

Reputation: 2247

  • Try executing the procedure in SQL server and make sure it returns atleast one record

  • Check the return value of the execute statement, print error if it is not successful

    $sth->execute() or die "Couldn't execute: " . $sth->errstr;

  • Check the number of rows from sth

    if ($sth->rows == 0) {print "Zero records"}

Upvotes: 1

ysth
ysth

Reputation: 98398

What does it return? Try this:

while ( @dbrow = $sth->fetchrow_array(  ) ) {
    use Data::Dumper;
    $Data::Dumper::Useqq = 1;
    print Dumper( { "got row" => \@dbrow } );
}

Upvotes: 1

Related Questions