Using selects within MySQL Stored Procedures

Question

I have stored procedure in my database and i need to look up a table and cross reference an id, then using the returned row i need to insert information into another table, but i cant seem to use the infomation from the lookup into the insert. This is what i have..

BEGIN
#Routine body goes here...
SET @UID = uid;
SET @UIDTOFB = uid_to;

SET @SQLTEST = CONCAT('SELECT users.user_auto_id FROM users WHERE users.user_fb_uid=     @UIDTOFB LIMIT 1');
PREPARE sqlcmd from @SQLTEST;
EXECUTE sqlcmd;

INSERT INTO challenges(challenge_from_uid, challenge_to_uid, challenge_dateadded) VALUES(@UID, @SQLTEST.users.user_auto_id, now());

SET @LASTID = LAST_INSERT_ID();
SELECT @LASTID as id;

END

any help would be much appreciated!

fancyPants · Accepted Answer · 2012-06-14 09:06:12Z

1

This won't insert the value of @UIDTOFB since you missed some '. It takes this whole statement as one string and therefore the statement fails.

SET @SQLTEST = CONCAT('SELECT users.user_auto_id FROM users WHERE users.user_fb_uid=     @UIDTOFB LIMIT 1');
PREPARE sqlcmd from @SQLTEST;
EXECUTE sqlcmd;

Anyway I'd recommend you use parameters like this:

PREPARE sqlcmd from 'SELECT users.user_auto_id FROM users WHERE users.user_fb_uid= ? LIMIT 1';
EXECUTE sqlcmd USING @UIDTOFB;

You can read more about it here in the manual.

UPDATE: Now I get, what you want to do. Do it simply like this:

SELECT @anyVariable:=users.user_auto_id FROM users WHERE users.user_fb_uid= @UIDTOFB LIMIT 1;
INSERT INTO challenges(challenge_from_uid, challenge_to_uid, challenge_dateadded) VALUES(@UID, @anyVariable, now());

edited Jun 14, 2012 at 9:06

answered Jun 14, 2012 at 8:48

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rorypicko Over a year ago

How would i then use users.user_auto_id that is returned in the same procedure?

rorypicko Over a year ago

Thanks tombom! much appreciated, saved me hours of head aches ;) haha

Collectives™ on Stack Overflow

Using selects within MySQL Stored Procedures

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related