Question 20628 in Mysql Question posted in
The official MySQL documentation can be found here.

Stored Procedure WITH SINGLE QUOTES – Mysql

Hi i have the next SP:

CREATE DEFINER=`admin`@`%` PROCEDURE `get_products_id`(IN column_name VARCHAR(50))
BEGIN
    SET @column_name = column_name;

    SET @query = CONCAT('SELECT 
        `p`.`id` AS `id`,
        `p`.`image` AS `image`,
        `p`.`globalClasification` AS `globalClasification`,
        `p`.`itemId` AS `itemId`,
        `p`.`code` AS `code`,
        `p`.`providerCode` AS `providerCode`,
        `p`.`description` AS `description`,
        `p`.`unspscCode` AS `unspscCode`,
        `p`.`productServiceKey` AS `productServiceKey`,
        `p`.`unit` AS `unit`,
        `p`.`keywords` AS `keywords`,
        `p`.`commonName` AS `commonName`,
        `p`.`barCode` AS `barCode`,
        `p`.`promo` AS `promo`,
        `p`.`pdi` AS `pdi`,
        `p`.`deleted` AS `deleted`,
        `p`.`distribution` AS `distribution`,
        `p`.`productTypeId` AS `productTypeId`,
        `pt`.`name` AS `productTypeName`,
        `p`.`brandId` AS `brandId`,
        `p`.`existencia` AS `existencia`,
        `p`.`existenciaUpdatedAt` AS `stockUpdatedAt`,'
        '`p`.`', @column_name, '` AS `column`, '
        '`b`.`name` AS `brandName`,
        `c`.`id` AS `categoryId`,
        `c`.`name` AS `categoryName`,
        `c`.`parentId` AS `parentId`,
        `c`.`pdi` AS `categoryPdi`,
        CONCAT(`cv`.`name`, ' ', `cv`.`value`) AS `characteristic`,
    `cv`.`name` AS `characteristicName`,
    `cv`.`value` AS `characteristicValue`,
    `cv`.`filter` AS `cvfilter`,
        `p`.`obsoleto` AS `obsoleto`
    FROM
        (((((`risoul_test`.`products` `p`
        JOIN `risoul_test`.`categoryproduct` `cp` ON ((`cp`.`productId` = `p`.`id`)))
        JOIN `risoul_test`.`categories` `c` ON ((`c`.`id` = `cp`.`categoryId`)))
        JOIN `risoul_test`.`characteristicValues` `cv` ON ((`p`.`id` = `cv`.`productId`)))
        JOIN `risoul_test`.`brands` `b` ON ((`b`.`id` = `p`.`brandId`)))
        JOIN `risoul_test`.`productTypes` `pt` ON ((`pt`.`id` = `p`.`productTypeId`)))
        WHERE `p`.`deleted` = 0');
        
         SELECT @query;

    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

I get an error on the following line

CONCAT(`cv`.`name`, ' ', `cv`.`value`) AS `characteristic`,

And the response is the next: Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘, cv.value) AS characteristic, cv.name AS characteristicName, `c’ at line 28

I do a log and the result i get is the next:

'SELECT 
        `p`.`id` AS `id`,
        `p`.`image` AS `image`,
        `p`.`globalClasification` AS `globalClasification`,
        `p`.`itemId` AS `itemId`,
        `p`.`code` AS `code`,
        `p`.`providerCode` AS `providerCode`,
        `p`.`description` AS `description`,
        `p`.`unspscCode` AS `unspscCode`,
        `p`.`productServiceKey` AS `productServiceKey`,
        `p`.`unit` AS `unit`,
        `p`.`keywords` AS `keywords`,
        `p`.`commonName` AS `commonName`,
        `p`.`barCode` AS `barCode`,
        `p`.`promo` AS `promo`,
        `p`.`pdi` AS `pdi`,
        `p`.`deleted` AS `deleted`,
        `p`.`distribution` AS `distribution`,
        `p`.`productTypeId` AS `productTypeId`,
        `pt`.`name` AS `productTypeName`,
        `p`.`brandId` AS `brandId`,
        `p`.`existencia` AS `existencia`,
        `p`.`existenciaUpdatedAt` AS `stockUpdatedAt`,`p`.`poPrueba` AS `column`, `b`.`name` AS `brandName`,
        `c`.`id` AS `categoryId`,
        `c`.`name` AS `categoryName`,
        `c`.`parentId` AS `parentId`,
        `c`.`pdi` AS `categoryPdi`,
        CONCAT(`cv`.`name`, , `cv`.`value`) AS `characteristic`,
        `cv`.`name` AS `characteristicName`,
        `cv`.`value` AS `characteristicValue`,
        `cv`.`filter` AS `cvfilter`,
        `p`.`obsoleto` AS `obsoleto`
    FROM
        (((((`risoul_test`.`products` `p`
        JOIN `risoul_test`.`categoryproduct` `cp` ON ((`cp`.`productId` = `p`.`id`)))
        JOIN `risoul_test`.`categories` `c` ON ((`c`.`id` = `cp`.`categoryId`)))
        JOIN `risoul_test`.`characteristicValues` `cv` ON ((`p`.`id` = `cv`.`productId`)))
        JOIN `risoul_test`.`brands` `b` ON ((`b`.`id` = `p`.`brandId`)))
        JOIN `risoul_test`.`productTypes` `pt` ON ((`pt`.`id` = `p`.`productTypeId`)))
        WHERE `p`.`deleted` = 0'

It doesn’t seem to add the single quotes.

What do i have to change to make it work?

Tags:

2

Answers


  1. 0

    The problem is that you are making a single-quoted string, which itself contains single-quotes that you expect to be literal single-quote characters. But the single-quote you want to be literal end up terminating the single-quoted string.

    Read about string literals in MySQL:

    https://dev.mysql.com/doc/refman/8.0/en/string-literals.html

    Quoted strings placed next to each other are concatenated to a single string. The following lines are equivalent:

    'a string'
'a' ' ' 'string'

    So in your case, the single-quotes you want to be part of your CONCAT() expression end up turning the whole query string into parts, but that’s okay because MySQL’s string literal syntax allows it.

    SET @query = CONCAT('SELECT ...
    CONCAT(`cv`.`name`, ' ', `cv`.`value`) AS `characteristic`,
                        ^ ^
          end first part   start second part

    What you need to do if you want literal quote characters is also documented in the same manual page:

    There are several ways to include quote characters within a string:

    • ‘ inside a string quoted with ‘ may be written as ”.

    • " inside a string quoted with " may be written as "".

    • Precede the quote character by an escape character ().

    • A ‘ inside a string quoted with " needs no special treatment and need not be doubled or escaped. In the same way, " inside a string quoted with ‘ needs no special treatment.

    You could therefore write it in one of these ways:

    SET @query = CONCAT('SELECT ...
    CONCAT(`cv`.`name`, '' '', `cv`.`value`) AS `characteristic`,

SET @query = CONCAT('SELECT ...
    CONCAT(`cv`.`name`, ' ', `cv`.`value`) AS `characteristic`,

SET @query = CONCAT('SELECT ...
    CONCAT(`cv`.`name`, " ", `cv`.`value`) AS `characteristic`,

    I would recommend avoiding the last solution because the meaning of " can change to an identifier delimiter depending on the sql_mode configuration.

    Login or Signup to reply.
  2. 0

    Bill already addressed the issue in the procedure.

    You can make the code easier to read and maintain by removing unnecessary backticks and parentheses. Backticks are only required if you have special characters in your column names (which you should not have anyways).

    You can also use the parameter in your CONCAT directly, no need to assign it to a user defined variable.

    CREATE DEFINER=`admin`@`%` PROCEDURE get_products_id(
in_column_name VARCHAR(50)
)
BEGIN
    SET @query = CONCAT('SELECT 
        p.id AS id,
        p.image AS image,
        p.globalClasification AS globalClasification,
        p.itemId AS itemId,
        p.code AS code,
        p.providerCode AS providerCode,
        p.description AS description,
        p.unspscCode AS unspscCode,
        p.productServiceKey AS productServiceKey,
        p.unit AS unit,
        p.keywords AS keywords,
        p.commonName AS commonName,
        p.barCode AS barCode,
        p.promo AS promo,
        p.pdi AS pdi,
        p.deleted AS deleted,
        p.distribution AS distribution,
        p.productTypeId AS productTypeId,
        pt.name AS productTypeName,
        p.brandId AS brandId,
        p.existencia AS existencia,
        p.existenciaUpdatedAt AS stockUpdatedAt,
        p.', in_column_name, ' AS ''colum'',
        b.name AS brandName,
        c.id AS categoryId,
        c.name AS categoryName,
        c.parentId AS parentId,
        c.pdi AS categoryPdi,
        CONCAT(cv.name, '' '', cv.value) AS characteristic,
        cv.name AS characteristicName,
        cv.value AS characteristicValue,
        cv.filter AS cvfilter,
        p.obsoleto AS obsoleto
    FROM
        risoul_test.products p
        JOIN risoul_test.categoryproduct cp ON cp.productId = p.id
        JOIN risoul_test.categories c ON c.id = cp.categoryId
        JOIN risoul_test.characteristicValues cv ON p.id = cv.productId
        JOIN risoul_test.brands b ON b.id = p.brandId
        JOIN risoul_test.productTypes pt ON pt.id = p.productTypeId
    WHERE p.deleted = 0');
        
    SELECT @query;

    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search