[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4539
  • Last Modified:

MySQL Stored Procedure Calling MySQL Stored Procedure

I have two MySQL Stored Procedure,

CREATE Procedure proc1(
  IN alpha VARCHAR(10)
  , OUT Beta VARCHAR(55)
 ) --blah blah blah code for proc

CREATE Procedure proc2()
  BEGIN
      DECLARE l_alpha VARCHAR(10);
      DECLARE l_beta VARCHAR(55);
      CALL proc1(l_alpha, l_beta);
  END

Why am I getting

ERROR 1414 (42000): OUT or INOUT argument 2 for routine schema.proc1 is not a variable or NEW pseudo-variable in BEFORE trigger

??

Is there any other method around this? best practice?

0
SamsonChung
Asked:
SamsonChung
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the error message seems to indicate that you try to use that inside a trigger?
can you clarify about that?
0
 
SamsonChungAuthor Commented:
No triggers what so ever...

What i was trying at the time when I got that message was to have one stored Proc call another stored Proc.

so, proc 2 calls proc 1. proc 1 does it thing, then return the result using INOUT variables of MySQL and return that to proc 2.

I was trying to get proc 2 to read this returned variable and use it. Then this problem showed up when I tried to create th proc.
0
 
SamsonChungAuthor Commented:
I've found out what I did wrong,

It was a silly mistake.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you post here, then the question can be PAQed with points refunded.
angelIII, zone advisor
0
 
SamsonChungAuthor Commented:
Sure, Shouldn't I get more points for finding my own answer? :p

neways, let's take my sample procs list above again...

CREATE Procedure proc1(
  IN alpha VARCHAR(10)
  , OUT Beta VARCHAR(55)
 ) --blah blah blah code for proc

------------------------------------------------------------

CREATE Procedure proc2()
  BEGIN
      DECLARE l_alpha VARCHAR(10);
      DECLARE l_beta VARCHAR(55);
      CALL proc1(l_alpha, l_beta);
      <-- At this point, l_beta is exactly what was returned from proc1. The problem I had was, on some of the MySQL documentation, they were using @l_beta ..... you cannot legally declare @l_beta, it runs fine, but would choke when you attempt to call it from another proc with a similar variable. That's what I did.. I have a proc calling a proc, calling another proc.. so in my case, I really have a proc 3, in which it generated the problem mentioned above.
  END

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now