Since you only have one output parameter and don't appear to be processing a result set as well, you can just do this after the execute: @output = $sth->func('syb_output_params');And $output[0] will contain the value (unless the proc failed, of course).

If you're also processing a result set, it's more complicated. Look up the documentation in the module, the section called "Retrieving output parameters from stored procedures" (appropriately enough).

I have tried the @output = $sth->func('syb_output_params'); way, and program just hangs. I have also followed the example from perldoc DBD::Sybase, but I always get a value of 0 back. I have read the section in perldoc DBD::Sybase, and the only thing different I have is my actual sql statement where I had to escape  \@diskreads=\@test OUTPUT which may or may not be right. Removing the backslashes gives an sql error from SQL server. Please advise as to where I am going wrong.

Besides the good advice of VSarkiss, do some error checking. Maybe you don't have permission to run the stored proc or maybe your not in the correct database when you login from your script. I try to always follow this template when doing DBI work:

#!/usr/bin/perl -w

use strict;
use DBI;

my $dbh = DBI->connect( $dsn, $user, $pass, 
                   { PrintError => 0, RaiseError => 1 } );

eval {
   my $sql = "blah ... ";
   my $sth = $dbh->prepare( $sql );
   $sth->execute;
   do {
       ...
   } while( $sth->{syb_more_results});
   $sth->finish;
};

if( $@ ) {
   print STDERR "DB error: $@\n";
}

$dbh->disconnect if $dbh;
[download]

-derby

There are a few possibilities. First, it may seem obvious, but are you sure the stored procedure is doing what you think? Can you invoke in interactive SQL and get what you expect? Second, are you using FreeTDS to connect to SQL Server? There are some known problems with it, and you may be stepping on one of them. Try using DBD::ADO or DBD::ODBC (which have their own set or problems, I know), and see if the situation improves. Otherwise, I don't see any obvious problems.

Hi Michael,
Thanks for writing but I get the following error when I run the above code DBD::Sybase::st execute failed: Server message number=156 severity=15 state=1 line=1 server=WIN-SVR2text=Incorrect syntax near the keyword 'select'. at ./syb.pl line 52.. Here is the code -

#$sth = $dbh->prepare("declare \@ret numeric exec TEST \@results=@ret 
+select @ret");
 $sth = $dbh->prepare("declare \@ret numeric(9,4) exec TEST \@results=
+@ret select @ret");
        $sth->execute;
        do {
                while($d = $sth->fetch)
                {
                        print "@$d\n";
                }
        } while($sth->{syb_more_results});
[download]

It complains with the sql statement essentially. I have also tried escaping all the @ signs, it runs but always returns a value of 0.($sth = $dbh->prepare("declare \@ret numeric(9,4) exec TEST \@results=\@ret select \@ret");) Please advise.

  
do {
    while($d = $sth->fetch) {
        ... do something with the data
    }
} while($sth->{syb_more_results});
[download]

You can also skip over result sets you know are not going to have anything by looking at $sth->{syb_result_type}. It's all in DBD::Sybase. And with all things DBD and Sybase, if mpeppler answers, listen to him first.

-derby

Output parameters are not returned like a result set. While this is technically a correct answer, it's for a different question. ;-)

Sure they are. From the DBD::Sybase docs (which also gives the shortcut you've shown in another node):

If your stored procedure only returns OUTPUT parameters,
then you can use this shorthand:

$sth = $dbh->prepare('...');
$sth->execute;
@results = $sth->func('syb_output_params');

This will return an array for all the OUTPUT parameters in
the proc call, and will ignore any other results. The
array will be undefined if there are no OUTPUT params, or
if the stored procedure failed for some reason.

The more generic way looks like this:

$sth = $dbh->prepare(
   "declare \@id_value int, \@id_name
   exec my_proc @name = 'a string', @number = 1234, @id = @id_value
   OUTPUT, @out_name = @id_name OUTPUT"
);

$sth->execute;
do {
   while($d = $sth->fetch) {
      if($sth->{syb_result_type} == 4042) { # it's a PARAM result
         $id_value = $d->[0];
         $id_name  = $d->[1];
      }
   }
 } while($sth->{syb_more_results});
[download]

update: Unless of course this is one of those odd little areas where SQLServer and Sybase differ.

-derby

I love multiple result sets :-)

Michael