I was rolling some queries into a stored procedure and I hit the #1327 - Undeclared variable error ... the odd thing though is that the variable it claims is undeclared is actually a table name.
Working through the problem
So I extracted the bit of the procedure where it was falling over and tried to run it as a normal SQL query directly on the database through PHPMyAdmin... same thing. After much tinkering it seems to be where I'm joining another table.
If I run the query on a single table, it's fine, like this:
SET @i_channel_id = 3;
SET @i_product_id = 90;
SELECT
`product_status_to_channel`.`status_code` INTO @s_status_code
FROM `product_status_to_channel`
WHERE `product_status_to_channel`.`channel_id` = @i_channel_id
AND `product_status_to_channel`.`product_id` = @i_product_id
ORDER BY IF(`product_status_to_channel`.`date` IS NULL, 1, 0) ASC,
`product_status_to_channel`.`date` DESC
LIMIT 0, 1;
SELECT @s_status_code AS status_code;
Which outputs 'LIVE' as the status_code in PHPMyAdmin - which is fine.
However, when I try and JOIN to the message table to find the associated status message, I get the error: #1327 - Undeclared variable: product_status_to_channel_lang ... but product_status_to_channel_lang is a table?!
SET @i_channel_id = 3;
SET @i_language_id = 3;
SET @i_product_id = 90;
SELECT
`product_status_to_channel`.`status_code` INTO @s_status_code,
`product_status_to_channel_lang`.`string` INTO @s_status_message
FROM `product_status_to_channel`
LEFT JOIN `product_status_to_channel_lang`
ON `product_status_to_channel`.`product_status_to_channel_id` = `product_status_to_channel_lang`.`product_status_to_channel_id`
AND `product_status_to_channel_lang`.`language_id` = @i_language_id
WHERE `product_status_to_channel`.`channel_id` = @i_channel_id
AND `product_status_to_channel`.`product_id` = @i_product_id
ORDER BY IF(`product_status_to_channel`.`date` IS NULL, 1, 0) ASC, `product_status_to_channel`.`date` DESC
LIMIT 0, 1;
SELECT @s_status_code AS status_code, @s_status_message AS status_message;
Is it trying to evaluate product_status_to_channel_lang.product_status_to_channel_id as a variable on the JOIN?
LEFT JOIN `product_status_to_channel_lang`
ON `product_status_to_channel`.`product_status_to_channel_id` = `product_status_to_channel_lang`.`product_status_to_channel_id`
I assume I'm overlooking something obvious?
I've tried this on both:
Never mind - it was something obvious:
SELECT
`product_status_to_channel`.`status_code` INTO @s_status_code,
`product_status_to_channel_lang`.`string` INTO @s_status_message
Should be:
SELECT
`product_status_to_channel`.`status_code`,
`product_status_to_channel_lang`.`string`
INTO
@s_status_code,
@s_status_message
... it must be Friday, it took literally a couple of hours to see that.
... INTO @s_status_code,
`product_status_to_channel_lang`.`string` ...
^That's where it's trying to assign product_status_to_channel_lang to being a variable into which to put data.