CODEWITHSUNDEEP
phpmysqlstored-procedures
Lipsa
Lipsa

Reputation: 417

How to insert data to table by store procedure with auto-incremented column

I have a stored procedure as below

DROP PROCEDURE IF EXISTS maintain//    
CREATE PROCEDURE maintain 
(    
    IN inMaintainType   CHAR(1),    -- 'i' = Insert, 'u'= Update/Edit, 'd'= Delete 
    IN inEntityId       INT,            -- 0 for Insert Case
    IN inEntityName     VARCHAR(100),
    IN inEntityDescription  VARCHAR(100),
    IN inEntityPrefix   CHAR(1),
    IN inStatus     CHAR(1),    -- 'a' = Active, 'i' = Not active
    IN inEmpId      INT,
    OUT outReturnStatus     INT,
    OUT outReturnRemarks    VARCHAR(100)
)

BEGIN    
    IF inMaintainType= 'i'
    THEN
        INSERT INTO Entity
        (
            EntityId,
            EntityName,
            EntityDescription,
            EntityPrefix,
            Status,
            CreatedBy,
            CreatedDate,
            ModifiedBy,
            ModifiedDate
        ) 
        VALUES
        (
            li_EntityId,
            inEntityName,
            inEntityDescription,
            inEntityPrefix,  
            'a',
            inEmpId,
            now(),
            inEmpId,
            now()
        );

        if row_count() != 0
            THEN SET outReturnStatus =0 ,
                outReturnRemarks = 'Insert Successful';
            ELSE SET outReturnStatus = 1,
                outReturnRemarks = 'Insert Not Successful';  
            END IF;                     
        END IF ;

I want to call the procedure to insert data with variables

mysql_query("CALL maintain('i','$EntityId','$EntityName','$EntityDescription','$EntityPrefix','$Status','$EmpId',@outvari1,@outvari2)")or die(mysql_error());

But it's showing me the error

Unknown column 'li_EntityId' in 'field list'

EntityId is the auto incremented field.

Upvotes: 0

Views: 154

Answers (1)

Jonnus
Jonnus

Reputation: 3038

To clarify the comments above into an actual answer...

You have set up your table so that the field EntityId will Auto-Increment.

Because of this when you insert a record into the table you don't need to explicitly add a value for the ID field - it will do it for you.

The solution is therefore to remove the EntityId field from the INSERT INTO ... statement, and to remove the li_EntityId value from the inserted values, so only 8 arguments are passed into the remaining 8 fields.

Upvotes: 1

Related Questions