CODEWITHSUNDEEP
mysql
Mj1992
Mj1992

Reputation: 3504

mysql Unknown column variable in the field list

I am trying to write a procedure which will accept victims and their count.The victims parameter will contain the values like this '123,321,222' and I am using a function call SPLIT_STR_FUNCTION to split the text into comma seperated values. Then I will insert each value in the database.

here is my procedure :

CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertPost`( in pmsg text, in pthumbPath text, in ppath text, in puserid bigint, in count int, in victims text) 
BEGIN 
INSERT INTO posts(path,thumbpath,msg,userid) VALUES(ppath,pthumbpath,pmsg,puserid);


SET @lastpostid = (SELECT postid FROM posts ORDER BY postid DESC LIMIT 1);

SET @startindex=1;

WHILE @startindex <= count DO 
SET @IndividualIDs=convert((select SPLIT_STR_Function(victims, ',', @startindex)),signed); 
SET @startindex=startindex+1;     
INSERT INTO victims(victimid,postid) VALUES(@IndividualIDs,@lastpostid); 

end WHILE; 
END

Error: Unknown column startindex in the field list

SPLIT_STR_FUNCTION: (from here)

CREATE DEFINER=`root`@`localhost` FUNCTION `SPLIT_STR_Function`(
x VARCHAR(255),
delim VARCHAR(12),
pos INT
 ) 

 RETURNS varchar(255) CHARSET latin1
 RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
    LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
    delim, '')

Upvotes: 2

Views: 1512

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270607

Your user variable @startindex is missing its @ when you increment it:

SET @startindex=startindex+1;
-- Should be:
SET @startindex = @startindex+1;

Upvotes: 1

Related Questions