#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    7
    Rep Power
    0

    Prepare, Execute , Insert Into


    Hi,
    i use this syntax in stored procedure (mysql) and work fine:
    INSERT INTO strategy_key_weeek_corr SELECT V_SYS, V_SYS_1,V_SYS_2,V_SYS_3;
    But if i use this syntax equivalent (i want change the name of column dynamicaly):
    SET @INSERT_INTO = 'INSERT INTO trategy_key_weeek_corr SELECT V_SYS, V_SYS_1,V_SYS_2,V_SYS_3;';
    PREPARE stmt from @INSERT_INTO;
    EXECUTE stmt;
    I have this error:
    Procedure execution failed
    1054 - Unknown column 'V_SYS' in 'field list'
    I have search in google but nothing.

    Help, please!

    Than's so much.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,140
    Rep Power
    4274
    Originally Posted by cello23
    Hi,
    i use this syntax in stored procedure (mysql) and work fine:
    i'm gonna guess no, it doesn't work fine

    in order to reference a column in the SELECT clause, you need a FROM clause to specifiy the table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    7
    Rep Power
    0
    [QUOTE=r937]i'm gonna guess no, it doesn't work fine

    I already tried wiht column name work fine:
    Code:
    INSERT INTO strategy_key_weeek_corr (SYS, SYS_1,SYS_2,SYS_3) VALUES ( V_SYS, V_SYS_1,V_SYS_2,V_SYS_3)
    But with PREPARE/EXECUTE I have error:
    Code:
    SET @INSERT_INTO = ' INSERT INTO strategy_key_weeek_corr (SYS, SYS_1,SYS_2,SYS_3) VALUES ( V_SYS, V_SYS_1,V_SYS_2,V_SYS_3);'
    PREPARE stmt from @INSERT_INTO;
    EXECUTE stmt;
    This error:
    Code:
    Procedure execution failed
    1054 - Unknown column 'V_SYS' in 'field list'
  6. #4
  7. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Why do you want to change column names dynamically?
    Usually the reason behind this an odd database design which forces you to do odd things in SQL.

    Prepared statements doesn't support variables instead of column names because the whole point is that the query is evaluated in advance and then you just replace the data that is sent in with the query.

    And btw using variables instead of column names is not really the rule but more of a bonus that you can do it in some places and I wouldn't recommend using it.
    /Stefan
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    7
    Rep Power
    0
    Originally Posted by sr
    Why do you want to change column names dynamically?
    Usually the reason behind this an odd database design which forces you to do odd things in SQL.

    Prepared statements doesn't support variables instead of column names because the whole point is that the query is evaluated in advance and then you just replace the data that is sent in with the query.

    And btw using variables instead of column names is not really the rule but more of a bonus that you can do it in some places and I wouldn't recommend using it.
    With Prepared statements i create a table with the column names dynamically: work fine.

    But for insert the data in this table i need to insert colunm names dynamically: but i have error, only in the insert and not in create table.
  10. #6
  11. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Originally Posted by cello23
    But for insert the data in this table i need to insert colunm names dynamically: but i have error, only in the insert and not in create table.
    The only reason create table works is that technically it's not a statement that you can prepare so it is sent as a one time execution to the database.

    As I said, you shouldn't expect it to work.

    So my question remains: why do you want to use variables instead of column names. If you tell us why, we might help you to find a better solution.
    /Stefan

IMN logo majestic logo threadwatch logo seochat tools logo