Mysql with parameters not working

Question

I have a mysql stored procedure like this:

CREATE DEFINER=`root`@`localhost` PROCEDURE `accounts_summary_by`(
    IN **created_by** int(10)
)
BEGIN
    SET group_concat_max_len=2048;
    SET @sql = NULL;
    SELECT
    GROUP_CONCAT(
    DISTINCT CONCAT(
    'MAX(IF(fiscal_year = ''',
    fiscal_year,
    ''', amount, 0)) AS ',
    CONCAT("'",fiscal_year,"'")
    )
    ) INTO @sql
    FROM
    net_savings;
    SET @sql = CONCAT('SELECT   accounts.id,
                                accounts.account, 
                                accounts.region, 
                                accounts.cluster, 
                                accounts.industry, ,'
                                ,@sql,
                                'FROM net_savings join accounts 
                                on  accounts.id = net_savings.account_id
                                Where accounts.user_id = **created_by**
                                GROUP BY accounts.account,net_savings.account_id 
                                ORDER BY accounts.id');

    PREPARE statement FROM @sql;
    EXECUTE statement;
    DEALLOCATE PREPARE statement;
    END

But upon calling the procedure like these:

CALL accounts_summary_by(2)

2 is a user_id reference to another table called users.

It gave me an error. Please help as I can't find any fixed to my problem.

0   72  23:41:12    CALL `buckets`.`accounts_summary_by`(1) Error Code: 1054. Unknown column 'created_by' in 'where clause' 0.000 sec

created_by is a field/table identifier, @created_by would be your parameter. — Marc B
– Marc B, Commented Aug 22, 2016 at 15:47
' is not a symbol used to denote field identifiers (such as aliases), use ` (the one with the ~ key.) — Uueerdo
– Uueerdo, Commented Aug 22, 2016 at 17:25

Shadow · Accepted Answer · 2016-08-22 15:58:59Z

MySQL's internal programming language is not php, it is not going to resolve variables within a text, so you need to concat it properly to the middle of the prepared statement:

SET @sql = CONCAT('SELECT   accounts.id,
                            accounts.account, 
                            accounts.region, 
                            accounts.cluster, 
                            accounts.industry,'
                            ,@sql,
                            'FROM net_savings join accounts 
                            on  accounts.id = net_savings.account_id
                            Where accounts.user_id ='
                            ,created_by
                            ,'GROUP BY accounts.account,net_savings.account_id 
                            ORDER BY accounts.id');

Since created_by is a parameter of the procedure, you do not need to preposition it with @.

Collectives™ on Stack Overflow

Mysql with parameters not working

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related