Миша Блохин Миша Блохин - 7 months ago 13
SQL Question

Some problems with MariaDB syntax


Good evening, here is my code sample. After execution i getting syntax error. Please help me resolve this issue.
error screenshot


CREATE PROCEDURE `catalog_get_options_in_model`(
IN inModelId INT,
IN inShortOptionDescriptionLenght INT,
IN inOptionsPerPage INT,
IN inStartItem INT
)

BEGIN

PREPARE
`stmt` AS

SELECT
`option`.opion_id,
`option`.name,

IF(
LENGHT(`option`.`description`) <= @p1,
`option`.`description` CONCAT(
LEFT(`option`.`description`,
@p2),
'...'
)
)

AS `description`,
`option`.price,
`option`.`discounted_price`,
`option`.`thumbnail`


FROM
`option`.`option_id`


INNER JOIN
`option_model`.`option_id` ON `option`.`option_id` = `option_model`.`option_id`


WHERE
`option_model`.`model_id` = @p3


ORDER BY
`option`.`display` DESC
LIMIT @p4, @p5


SET
@p1 = inShortOptionDescriptionLength


SET
@p2 = inShortOptionDescriptionLength


SET
@p3 = inModelId


SET
@p4 = inStartItem


SET
@p5 = inOptionsPerPage


EXECUTE
`stmt` USING @p1,
@p2,
@p3,
@p4,
@p5
END $$

Answer

IF( LENGHT(option.description) <= @p1

but @p1 is not defined yet.

Same for all other vars you are trying to use in query.

My guess you should use ? as placeholder.

http://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html

Comments