How to get the stored procedure result using perl?

Through the DBD::ODBC driver

Since you're using DBD::ODBC, you can use more_results provided by that driver to get the results of multiple queries in one execute.

This is the example they show in the documentation.

do {
   my @row;
   while (@row = $sth->fetchrow_array()) {
      # do stuff here
   }
} while ($sth->{odbc_more_results});

If we want to do this with your example queries, it's pretty much the same. You run your stored procedure, and then proceed with the do {} while construct (note that this is not a block, you cannot next out of it!).

my $sth = $dbh->prepare("exec TEST_ABC_DB.dbo.testprocedure2");
$sth->execute;

do {
    while (my @row = $sth->fetchrow_array()) {
        print $row[0]."\t";
        print "\n";
    }
} while ($sth->{odbc_more_results});

This should print your expected result.

one
three
five

Workaround if your driver doesn't support this

There is no way for DBI itself to return the result of multiple queries at once. You can run them, but you cannot get the results.

If you really need three separate queries in your procedure and want all of the results, the answers by Shakheer and Shahzad to use a UNION are spot on.

However, your example is probably contrived. You probably don't have the same amount of columns in each of those queries, and you need to distinguish the results of each of the queries.

We have to change SQL and Perl code for this.

To get that to work, you can insert additional rows that you can later use to map each stack of results to each query.

Let's say the procedure looks like this:

create procedure testprocedure3 as
select 'one'
select 'three', 'three', 'three'
select 'five', 'five', 'five', 'five', 'five'

This is still just one row per query, but it should do as an example. With the UNION approach, it first becomes this:

create procedure testprocedure3 as
select 'one'
union all
select 'three', 'three', 'three'
union all
select 'five', 'five', 'five', 'five', 'five'

If you run this, it might fail. In ANSI SQL a UNION needs to have the same number of columns in all its queries, so I assume SQLServer also wants this. We need to fill them up with NULLs. Add them to all the queries so they match the number of columns in the one with the largest number of columns.

create procedure testprocedure3 as
select 'one', NULL, NULL, NULL, NULL
union all
select 'three', 'three', 'three', NULL, NULL
union all
select 'five', 'five', 'five', 'five', 'five'

If we now loop over it in Perl with the following code, we'll get something back.

use Data::Dumper;
my $sth = $dbh->prepare("exec TEST_ABC_DB.dbo.testprocedure3");
$sth->execute;
while ( my $row = $sth->fetchrow_arrayref ) {
    print Dumper $row;
}

We'll see output similar to this (I didn't run the code, but wrote the output manually):

$VAR1 = [ 'one', undef, undef, undef, undef ];
$VAR1 = [ 'three', 'three', 'three', undef, undef ];
$VAR1 = [ 'five', 'five', 'five', 'five', 'five' ];

We have no way of knowing which line belongs to which part of the query. So let's insert a delimiter.

create procedure testprocedure3 as
select 'one', NULL, NULL, NULL, NULL
union all
select '-', '-', '-', '-', '-'
union all
select 'three', 'three', 'three', NULL, NULL
union all
select '-', '-', '-', '-', '-'
union all
select 'five', 'five', 'five', 'five', 'five'

Now the result of the Perl code will look as follows:

$VAR1 = [ 'one', undef, undef, undef, undef ];
$VAR1 = [ '-', '-', '-', '-', '-' ];
$VAR1 = [ 'three', 'three', 'three', undef, undef ];
$VAR1 = [ '-', '-', '-', '-', '-' ];
$VAR1 = [ 'five', 'five', 'five', 'five', 'five' ];

This might not be the best choice of delimiter, but it nicely illustrates what I am planning to do. All we have to do now is split this into separate results.

use Data::Dumper;

my @query_results;
my $query_index = 0;
my $sth = $dbh->prepare("exec TEST_ABC_DB.dbo.testprocedure3");
$sth->execute;
while ( my $row = $sth->fetchrow_arrayref ) {
     # move to the next query if we hit the delimiter
     if ( join( q{}, @$row ) eq q{-----} ) {
         $query_index++;
         next;
     }

     push @{ $query_results[$query_index] }, $row;
}

print Dumper \@query_results;

I've defined two new variables. @query_results holds all the results, sorted by query number. $query_index is the index for that array. It starts with 0.

We iterate all the resulting rows. It's important that $row is lexical here. It must be created with my in the loop head. (You are using use strict, right?) If we see the delimiter, we increment the $query_index and move on. If we don't we have a regular result line, so we stick that into our @query_results array within the current query's index.

The overall result is an array with arrays of arrays in it.

$VAR1 = [
   [
       [ 'one', undef, undef, undef, undef ]
   ], 
   [
       [ 'three', 'three', 'three', undef, undef ]
   ], 
   [
       [ 'five', 'five', 'five', 'five', 'five' ]
   ], 
];

If you have actual queries that return many rows this starts making a lot of sense.

Of course you don't have to store all the results. You can also just work with the results of each query directly in your loop.

^{Disclaimer: I've run none of the code in this answer as I don't have access to an SQLServer. It might contain syntax errors in the Perl as well as the SQL. But it does demonstrate the approach.}