All Forums Database
20 Sep 2012
Stored Procedure Error

Hello All,
Doing some hands on stored procedure. I have created below procedure (relatively simple) but it is giving me weird error.
Below are the details:
Environment: Teradata 13.10 using SQL assistant
Sales Table DDL:
CREATE SET TABLE MOTORS.sales ,NO FALLBACK ,
     CHECKSUM = DEFAUL(
      STOREID SMALLINT NOT NULL,
      SMONTH SMALLINT,
      PRODID CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
      SALE DECIMAL(12,2)
      )
PRIMARY INDEX ( STOREID );
Stored Procedure:
Create PROCEDURE MyFirstProc (IN store_id smallint,
                                            OUT saleamt decimal(8),
                                            INOUT errstr VARCHAR(30))
BEGIN
       SELECT sale INTO saleamt FROM Sales
       whERE storeid = store_id;
END;
When I call this procedure using call MyFirstProc(1077) it gives me error as :”CALL Failed 3812: Positional assignment list has too few values”
This happens for any storeid that I call….
Can you please guide me if I have missed anything in stored proc defination?
Regards,
Chinmay
 

Qaisar Kiani 337 posts Joined 11/05
22 Sep 2012

You can call the stored procedure using the statement
CALL MyFirstProc(1077, saleamt, 0);
The Rule is
- IN parameters, you have to pass the value
- OUT parameters, you have to specify a variable name
- INOUT parameters, you have to pass a value, but the stored procedure also returns a value as well.

24 Sep 2012

Thank You QAKiani....it worked for me
Regards,
Chinmay
 

You must sign in to leave a comment.