Professional Web Applications Themes

Prepared Statements within stored procedures - MySQL

I have a stored proceduren with a prepared statement inside, I would like to put the result fields into variables but I donīt know how, Should I use some kind of cursor? How can I put the COUNT(*) field into a variable at stored procedure scope? CREATE PROCEDURE `GetPortability`(p_table VARCHAR(50), p_number VARCHAR(50)) NOT DETERMINISTIC SQL SECURITY DEFINER COMMENT '' BEGIN SET l_sql = CONCAT('SELECT COUNT(*) FROM ',p_table,' WHERE Prefix LIKE \'',p_number , '\';'); PREPARE stmt1 FROM l_sql; EXECUTE stmt1 ; DEALLOCATE PREPARE stmt1; END; Thank you.......

  1. #1

    Default Prepared Statements within stored procedures

    I have a stored proceduren with a prepared statement inside, I would
    like to put the result fields into variables but I donīt know how,
    Should I use some kind of cursor? How can I put the COUNT(*) field into
    a variable at stored procedure scope?

    CREATE PROCEDURE `GetPortability`(p_table VARCHAR(50), p_number
    VARCHAR(50))
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
    BEGIN
    SET l_sql = CONCAT('SELECT COUNT(*) FROM ',p_table,' WHERE Prefix
    LIKE \'',p_number , '\';');

    PREPARE stmt1 FROM l_sql;
    EXECUTE stmt1 ;

    DEALLOCATE PREPARE stmt1;
    END;
    Thank you....

    martinacevedo@gmail.com Guest

  2. #2

    Default Re: Prepared Statements within stored procedures

    [email]martinacevedo[/email] wrote:
    > I have a stored proceduren with a prepared statement inside, I would
    > like to put the result fields into variables but I donīt know how,
    > Should I use some kind of cursor? How can I put the COUNT(*) field into
    > a variable at stored procedure scope?
    >
    > CREATE PROCEDURE `GetPortability`(p_table VARCHAR(50), p_number
    > VARCHAR(50))
    > NOT DETERMINISTIC
    > SQL SECURITY DEFINER
    > COMMENT ''
    > BEGIN
    > SET l_sql = CONCAT('SELECT COUNT(*) FROM ',p_table,' WHERE Prefix
    > LIKE \'',p_number , '\';');
    >
    > PREPARE stmt1 FROM l_sql;
    > EXECUTE stmt1 ;
    >
    > DEALLOCATE PREPARE stmt1;
    > END;
    > Thank you....
    >
    You can do it through a user variable.
    For example:

    delimiter //

    drop procedure if exists GetPortability //

    CREATE PROCEDURE `GetPortability`(p_table VARCHAR(50))
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
    BEGIN
    declare myvar int;
    SET l_sql = CONCAT('set result = (SELECT COUNT(*) FROM ',p_table, ')' );

    PREPARE stmt1 FROM l_sql;
    EXECUTE stmt1 ; -- here result gets the value of COUNT(*)

    DEALLOCATE PREPARE stmt1;
    set myvar = result;
    -- do something with myvar
    END//

    delimiter ;


    An alternative could be:

    SET l_sql = CONCAT('SELECT result := COUNT(*) FROM ',p_table );

    But it would also produce a dataset, which may not be what you want.

    ciao
    gmax

    --
    _ _ _ _
    (_|| | |(_|>< The Data Charmer
    _|
    [url]http://datacharmer.org/[/url]
    Giuseppe Maxia Guest

  3. #3

    Default Re: Prepared Statements within stored procedures

    Thank you Giusseppe, it works perfectly ...
    Bye

    martinacevedo@gmail.com Guest

  4. #4

    Default Re: Prepared Statements within stored procedures

    Uh, what happened to the "WHERE Prefix..."?
    Unregistered Guest

Similar Threads

  1. Replies: 3
    Last Post: September 5th, 03:11 AM
  2. #40277 [NEW]: BLOB objects are fetched badly via prepared statements
    By zakirov at rain dot ifmo dot ru in forum PHP Bugs
    Replies: 1
    Last Post: January 29th, 08:35 PM
  3. Replies: 4
    Last Post: November 11th, 11:40 AM
  4. Prepared statements in ASP
    By No one in forum ASP Components
    Replies: 8
    Last Post: June 6th, 11:48 AM
  5. Replies: 0
    Last Post: August 10th, 04:41 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