Calling Stored Procedures

[Anthony Robinson]

I'm executing a stored procedure and getting the
SQL0440N No authorized routine named
"AMROBI2.CREATE AIMCONNECTION" of type "PROCEDURE
" having compatible arguments was found.

The schema exists, the stored procedure exists, the user executing the
procedure has execute permissions on the procedure and also has
insert, update, delete rights on the table being accessed in the
stored procedure. I'm sending in the correct number of input
parameters.

I'm running Version 8, FixPak 3 on Windows.

Here's the stored procedure body (pretty basic):
CREATE PROCEDURE AMROBI2.CreateA IMConnection (IN decCHANNELSESSI ONID
DECIMAL(12,0),
IN strBUSINESSLINE VARCHAR(24),
IN strBRANCHID VARCHAR(24),
OUT decAIMCONNECTIO NID DECIMAL(12,0))
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
LANGUAGE SQL
RESULT SETS 1
P1: BEGIN
INSERT INTO AMROBI2.AIMCONN ECTION (ChannelSession ID, BusinessLine,
BranchID, StartDateTime)
VALUES
(decCHANNELSESS IONID, strBUSINESSLINE , strBRANCHID, CURRENT
TIMESTAMP);
SET decAIMCONNECTIO NID = IDENTITY_VAL_LO CAL();
END P1

Here's how I'm calling it:

CALL AMROBI2.CREATEA IMCONNECTION (5, 'TEST', 'TEST');

What gives????

....a quick aside:

The proc runs fine if I run it through the Development Center.

[Knut Stolze]

Re: Calling Stored Procedures

Anthony Robinson <ansonee@yahoo. com> wrote:
[color=blue]
> I'm executing a stored procedure and getting the
> SQL0440N No authorized routine named
> "AMROBI2.CREATE AIMCONNECTION" of type "PROCEDURE
> " having compatible arguments was found.
>
> Here's the stored procedure body (pretty basic):
> CREATE PROCEDURE AMROBI2.CreateA IMConnection (IN decCHANNELSESSI ONID
> DECIMAL(12,0),
> IN strBUSINESSLINE VARCHAR(24),
> IN strBRANCHID VARCHAR(24),
> OUT decAIMCONNECTIO NID DECIMAL(12,0))[/color]
[...][color=blue]
>
> CALL AMROBI2.CREATEA IMCONNECTION (5, 'TEST', 'TEST');[/color]

Your procedure expects 4 parameters and you only provided 3. But there is
no procedure with 3 parameters, so the error is correctly raised.

You should call the procedure like this:

CALL AMROBI2.CREATEA IMCONNECTION (5, 'TEST', 'TEST', ?);

--
Knut Stolze
Information Integration
IBM Germany / University of Jena