Stored Procedure Support for Placeholders

The Adaptive Server Enterprise database driver for Perl supports stored procedures that include both input and output parameters.

Stored procedures are handled in the same way as any other Transact-SQL statement. However, Sybase stored procedures return an extra result set that includes the return status that corresponds to the return statement in the stored procedure code. This extra result set, named CS_STATUS_RESULT with numeric value 4043, is a single row and is always returned last.

The driver can process the stored procedure using a special attribute, $sth->{syb_do_proc_status}. If this attribute is set, the driver processes the extra result set, and places the return status value in $sth->{syb_proc_status}. An error is generated if the result set is a value other than 0.

Examples

$sth = $dbh->prepare("exec my_proc \@p1 = ?, \@p2 = ?"); 
    $sth->execute('one', 'two');

This example illustrates the use of positional parameters:

$sth = $dbh->prepare("exec my_proc ?, ?"); 
  $sth->execute('one', 'two');

You cannot mix positional and named parameters in the same prepare statement; for example, this statement fails on the first parameter:

$sth = $dbh->prepare("exec my_proc \@p1 = 1, \@p2 = ?");

If the stored procedure returns data using output parameters, you must declare them first:

$sth = $dbh->prepare(qq[declare @name varchar(50) exec getname abcd, @name output]);

You cannot call stored procedures with bound parameters, as in:

$sth = $dbh->prepare("exec my_proc ?"); 
  $sth->execute('foo');

This works as follows:

$sth = $dbh->prepare("exec my_proc 'foo'"); 
  $sth->execute('foo');

Because stored procedures almost always return more than one result set, use a loop until syb_more_results is 0:

do {
	while($data = $sth->fetch) {
		do something useful... 
	}
 } while($sth->{syb_more_results});

Parameter examples

declare @id_value int, @id_name char(10)
	exec my_proc @name = 'a_string', @number = 1234,
		@id = @id_value OUTPUT, @out_name = @id_name OUTPUT

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

$sth = $dbh->prepare('select * .......');
	$sth->execute();
@results = $sth->syb_output_params();  #  this method is available in SybaseASE.pm

This returns an array for all the OUTPUT parameters in the procedure call and ignores any other results. The array is undefined if there are no OUTPUT parameters or if the stored procedure fails.

Generic examples

$sth = $dbh->prepare("declare \@id_value int, \@id_name
		OUTPUT, @out_name = @id_name OUTPUT");
	$sth->execute();
	{
		while($d = $sth->fetch) {
			# 4042 is CS_PARAMS_RESULT
		if ($sth->{syb_result_type} == 4042) {
			$id_value  = $d->[0];
			$id_name  = $d->[1];
		}
	}
	redo if $sth->{syb_more_results};
}

The OUTPUT parameters are returned as a single row in a special result set.