Professional Web Applications Themes

Dynamic if exists? - MySQL

Hi, I'm trying to create a stored procedure to check if a certain row exists, and if so to update it - else insert it. Both table and id- column are dynamic. I'm familiar in MS SQL and would use dynamic sql to perform this task there, however, I can't seem to get MySQL working. Here's what I've got now: ******************************* DELIMITER $$ DROP PROCEDURE IF EXISTS `test1`.`spParamTest` $$ CREATE PROCEDURE `test1`.`spParamTest` (tablename varchar(20), idCol varchar(20), updateCol varchaR(20), idValue varchaR(20), updateValue varchar(20)) BEGIN set sql_text:=concat('if exists (select * from ', tablename, ' where ''', idCol, ''' = ''', idValue , ...

  1. #1

    Default Dynamic if exists?

    Hi,

    I'm trying to create a stored procedure to check if a certain row
    exists, and if so to update it - else insert it. Both table and id-
    column are dynamic.
    I'm familiar in MS SQL and would use dynamic sql to perform this task
    there, however, I can't seem to get MySQL working.

    Here's what I've got now:

    *******************************

    DELIMITER $$

    DROP PROCEDURE IF EXISTS `test1`.`spParamTest` $$
    CREATE PROCEDURE `test1`.`spParamTest` (tablename varchar(20),
    idCol varchar(20),
    updateCol varchaR(20),
    idValue varchaR(20),
    updateValue varchar(20))
    BEGIN

    set sql_text:=concat('if exists (select * from ', tablename, '
    where ''', idCol, ''' = ''', idValue , ''') then '
    , 'select ''need to update'';'
    , ' else select ''need to insert''; end if;');

    prepare stmt from sql_text;
    execute stmt;
    deallocate prepare stmt;


    END $$

    DELIMITER ;

    *******************************

    The sql_text gotten from the concat seems to be valid but when I try
    to execute it from the prepared statement it fails. Anyone able to
    point me into the correct direction?

    Any advice would be appreciated.

    Regards,
    Sjur

    sjur@randh.org Guest

  2. #2

    Default Re: Dynamic if exists?

    On 1 Mar, 15:05, org wrote: 

    You're making this far too complicated.
    Look at the syntax for
    INSERT ... ON DUPLICATE KEY UPDATE

    Captain Guest

  3. #3

    Default Re: Dynamic if exists?

    On 1 Mar, 16:31, "Captain Paralytic" <com> wrote: 

    Thanks, that worked out nicely. The original idea was to make the sql
    work in both mssql and mysql and guess I got kinda stuck with that
    idea even though abandoning it to make sp's.
    Or is it possible to do any kind of "if exists (select...)"-syntax
    outside of sp's in mysql 5.x?

    Regards,
    Sjur

    sjur@randh.org Guest

Similar Threads

  1. DELETE where NOT EXISTS
    By Paul Lautman in forum MySQL
    Replies: 6
    Last Post: April 2nd, 03:05 PM
  2. Duplicate Name Exists...
    By S|MuLaTOR webforumsuser@macromedia.com in forum Macromedia Director 3D
    Replies: 7
    Last Post: December 15th, 09:54 PM
  3. Testing if a value exists.
    By Andy Levy in forum PHP Development
    Replies: 3
    Last Post: October 30th, 11:03 AM
  4. method name exists, property value exists, calling method fails
    By Phil Powell in forum PHP Development
    Replies: 0
    Last Post: October 27th, 07:34 PM
  5. File Exists
    By Gram in forum ASP
    Replies: 4
    Last Post: August 29th, 03:46 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139