Capturing a value returned from a stored procedure

punkish has asked for the wisdom of the Perl Monks concerning the following question:

Hello,

I created a stored proc in SQL Server like so

  CREATE PROCEDURE sp_myproc 
    @a VARCHAR(250), 
    @b VARCHAR(250)
  AS 
  
  BEGIN TRAN
  
  ..bunch of code..
  
  DECLARE @id NUMERIC, @newid NUMERIC

  SELECT @id = id, @newid = (id + 1) 
  FROM sometable 
  WHERE (some condition)
  
  ..more bunch of code..
  
  -- Now, insert a new record in request
  INSERT INTO anothertable (id, othercols)
  VALUES (@id, othervals)
  
  -- Update the request id
  UPDATE yetanothertable 
  SET id = @newid 
  WHERE (someothercondition)
  
  COMMIT TRAN
  
  RETURN @id
[download]

Which I call via DBD::OBDC like so

my $sth = $dbh->prepare("EXEC sp_myproc ?, ?");
my $id = $sth->execute('foo', 'bar');
[download]

Obviously my code above is wrong because the $id captured by the Perl code is not the same as the @id created in the stored proc. My $id is always 1, which is, I am guessing, indicating success at running the stored proc. I want to get back that @id that I created in the stored proc.

In general, how do I get one or more values back from a complicated (or not so) stored procedure back in my Perl code so I can work with it?

Many thanks in advance.

Comment on Capturing a value returned from a stored procedure Select or Download Code

Replies are listed 'Best First'.
Re: Capturing a value returned from a stored procedure by runrig (Abbot) on Dec 21, 2004 at 00:03 UTC
I think your RETURN should be a SELECT. Then you can fetch from it. In SQL Server, you can have a SELECT clause without a FROM clause to return one row of non-table data. (for the benefit of others, it's like selecting 'FROM duel' in Oracle to get one row of non-table data). If there are intermediate select statements with no INTO clause, then read the DBD::ODBC docs to see how to get multiple result sets. If you can not rewrite your stored procedure, I believe you could execute something like "`SELECT sp_myproc(...)`" and then fetch from that.	[reply] [d/l]
Re^2: Capturing a value returned from a stored procedure by punkish (Priest) on Dec 21, 2004 at 00:11 UTC
;-) Yes. in my stored proc `..bunch of code.. COMMIT TRAN SELECT @id` [download] and then in my script `my @id = $sth->fetchrow_array; return $id[0];` [download] does the trick. Many thanks.	[reply] [d/l] [select]
Re: Capturing a value returned from a stored procedure by jZed (Prior) on Dec 20, 2004 at 23:53 UTC
Fetch the value from the $sth the same way you'd fetch any results from a statement handle, e.g. $sth->fetch and friends.	[reply]
Re^2: Capturing a value returned from a stored procedure by punkish (Priest) on Dec 21, 2004 at 00:01 UTC
Thanks for you quick response... Fetch the value from the $sth the same way you'd fetch any results from a statement handle, e.g. $sth->fetch and friends. Unfortunately, that gives me the error `DBD::ODBC::st fetch failed: (DBD: no select statement currently execut +ing err=-1) at ...` [download] is that because SELECT is not my last statement? The entire stored proc is a number of statements (40 or so). The stored proc is ended with a COMMIT TRAN. Seems like RETURN @id is not the way to do this.	[reply] [d/l]