Professional Web Applications Themes

Stored Procedures: INSERT VALUES string as a parameter - MySQL

How can I get this stored procedure to work? I want to pass all the INSERT parameters as one string. /*I want this to insert 2 records*/ DROP PROCEDURE IF EXISTS spTest$$ CREATE PROCEDURE spTest() BEGIN DECLARE pVal TEXT; SET pVal = "('a','b','c','d','e','f'), ('g','h','i','j','k','l')"; INSERT INTO test (fld1,fld2,fld3,fld4,fld5,fld6) VALUES pVal; END$$ /*I've tried prepared statements as well without success*/ DROP PROCEDURE IF EXISTS glTest$$ CREATE PROCEDURE glTest() BEGIN PREPARE ps FROM "INSERT INTO test (fld1,fld2,fld3,fld4,fld5,fld6) VALUES ?"; SET p = "('a','b','c','d','e','f'),('g','h','i','j','k','l ')"; EXECUTE ps USING p; END$$...

  1. #1

    Default Stored Procedures: INSERT VALUES string as a parameter

    How can I get this stored procedure to work?
    I want to pass all the INSERT parameters as one string.

    /*I want this to insert 2 records*/
    DROP PROCEDURE IF EXISTS spTest$$
    CREATE PROCEDURE spTest()
    BEGIN
    DECLARE pVal TEXT;
    SET pVal = "('a','b','c','d','e','f'),
    ('g','h','i','j','k','l')";
    INSERT INTO test (fld1,fld2,fld3,fld4,fld5,fld6)
    VALUES pVal;
    END$$

    /*I've tried prepared statements as well without success*/
    DROP PROCEDURE IF EXISTS glTest$$
    CREATE PROCEDURE glTest()
    BEGIN
    PREPARE ps FROM "INSERT INTO test
    (fld1,fld2,fld3,fld4,fld5,fld6) VALUES ?";
    SET p = "('a','b','c','d','e','f'),('g','h','i','j','k','l ')";
    EXECUTE ps USING p;
    END$$

    mouac01@yahoo.com Guest

  2. #2

    Default Re: Stored Procedures: INSERT VALUES string as a parameter

    On 25 May 2007 23:31:00 -0700, com wrote:
     

    BEGIN

    DECLARE _insert TEXT;
    DECLARE _vars TEXT;

    SET _insert = 'INSERT INTO Languages
    (`LangID`,`LangCode`,`LangStatus`,`TxtlID`) VALUES ';
    SET _vars = "(NULL,'CN','10','1'),(NULL,'EL','10','10')";

    SET insert = CONCAT(_insert,_vars);
    PREPARE ps FROM insert;
    EXECUTE ps;

    END$$
    subtenante Guest

  3. #3

    Default Re: Stored Procedures: INSERT VALUES string as a parameter

    Thanks, subtenante..
    Sorry for being such an idiot...

    mouac01@yahoo.com Guest

  4. #4

    Default Re: Stored Procedures: INSERT VALUES string as a parameter

    On 28 May 2007 14:55:38 -0700, com wrote:
     

    Well I don't think you are. At first I tried to fix your USING problem
    but I couldn't, so I picked the good old 'CONCAT everything and run'
    strategy.
    subtenante Guest

Similar Threads

  1. PHP and returned values from Informix stored procedures
    By Steve Weet in forum PHP Development
    Replies: 4
    Last Post: November 6th, 04:09 PM
  2. DDL values with DataReader and stored procedures
    By Rob Wire in forum ASP.NET General
    Replies: 5
    Last Post: August 14th, 09:50 AM
  3. String Concatenation in Stored Procedures
    By Peter in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: August 12th, 09:37 PM
  4. Q: retrieving default values for parameters in stored procedures ?
    By Fridthjof-G in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: August 5th, 08:47 AM
  5. Maximum Length of String For Dynamic Stored Procedures
    By Lawrence in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 31st, 05:46 AM

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