Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    Unanswered: Check column existance

    Hello,

    I want to add a column only if it not exists. Is this possible?

    -----------------------------
    DROP PROCEDURE IF EXISTS mycheckColumn;

    DELIMITER $$

    CREATE PROCEDURE mycheckColumn( sTableName VARCHAR(255), sColumnName VARCHAR(255) )
    BEGIN
    DECLARE sDummy VARCHAR(100);
    SET @sSQL = CONCAT( 'SELECT table_name FROM INFORMATION_SCHEMA.tables WHERE table_name = \'', sTableName, '\' LIMIT 0, 1 ' );
    PREPARE stmt FROM @sSQL;
    EXECUTE stmt;
    DROP PREPARE stmt;
    END$$

    DELIMITER ;
    ---------------------------------------------
    So when I do : CALL mycheckColumn('voe2dsel','Id'); It will display table-info.
    I want to put the result in an output parameter. How do I do this?
    Is this possible?

    thanx

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why would you want to?
    what circumstances do you think that you will need to add columns to a table whilst the application is live?
    what circumstances do you think will exist that may make a column name 'go away' or be deleted?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    reply

    Does it really matter why I want this? But for you; It is an update script. This updatescript will run everytime there is an update. So when you create a column the first time it is ok, but when you create the same column the next time the script will give an error. That is the reason.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by thepercival
    Does it really matter why I want this?
    yes, knowing the reason why the person asking the question wants to do something will usually allow the person answering the question to understand the problem better

    in this case, i'm having a hard time with the concept of wanting to add a column to a table more than once, such that you need to detect this in application logic before executing it

    what's wrong with just allowing the database to return an error message?

    granted, the error will not say "dude, that table already has that column" but translating the database error message into a user-friendly message is the job of your application, right?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149
    I didn't say the script run's in an application, but if you run the script from the command line than you still don't want so see errors. Right?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, i disagree

    if the script is trying to do something wrong, i don't mind seeing errors
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •