output param & multiple recordests from stored procedures

[Cliff]

here's my code:

my $sth = $dbhSQL->prepare('{ca ll proc(?,?,?)}');
$sth->bind_param(1," asd");
$sth->bind_param(2," klm");
$sth->bind_param_ino ut(3,\$no_go, 1000);
$sth->execute;
print "no go = $no_go\n";
while(
my @row=$sth->fetchrow_array ){
print "@row\n";
}
$sth->finish;

Here's my stored procedure:

CREATE PROCEDURE proc
@id varchar(50),@iy t varchar(20),@no _go int OUTPUT
AS
SET NOCOUNT ON
DECLARE @id_err int,@ans_glue_e rr int

BEGIN TRANSACTION
SELECT user_id FROM myTable
WHERE user_id=@id AND iyt=@iyt
SET @id_err = @@ERROR
IF @@ROWCOUNT <> 0
BEGIN
SELECT date,date_mod FROM ans_glue
WHERE user_id=@id
SET @no_go = 0
SET @ans_glue_err=@ @ERROR
END
ELSE
BEGIN
SET @no_go = 1
END
IF @id_err = 0 AND @ans_glue_err = 0
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
END

the procedure runs perfectly in Cold Fusion, returning both recordsets
and output param, but in perl, it won't print the output param and I
don't know how to access the second recordset
HELP!

[Erland Sommarskog]

Re: output param &amp; multiple recordests from stored procedures

Cliff (cliff@walkacro ssfire.com) writes:[color=blue]
> here's my code:
>
> my $sth = $dbhSQL->prepare('{ca ll proc(?,?,?)}');
> $sth->bind_param(1," asd");
> $sth->bind_param(2," klm");
> $sth->bind_param_ino ut(3,\$no_go, 1000);
> $sth->execute;
> print "no go = $no_go\n";
> while(
> my @row=$sth->fetchrow_array ){
> print "@row\n";
> }
> $sth->finish;
>...
> the procedure runs perfectly in Cold Fusion, returning both recordsets
> and output param, but in perl, it won't print the output param and I
> don't know how to access the second recordset
> HELP![/color]

You are probably better off to ask this in a forum devoted to DBI.
ActiveState hosts a mailing list, see
http://aspn.activestate.com/ASPN/Mai...eaded/perl-DBI.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp