Professional Web Applications Themes

HELP: FUNCTION and PROCEDURE best practices.... - MySQL

Hi All, Got a best practices question.... I often have to write PROCEDURES that return a value (because it needs to have a transaction in it)...and I can't do that in a FUNCTION. eg CREATE PROCEDURE `sp_test01`() BEGIN START TRANSACTION; -- Do some insert COMMIT; SELECT 1; END$$ This makes it very easy to get a return value from the CALL sp_test01() from, for example, C#....set it up.....ExecuteScalar. However, it doesn't allow me to call this and get a return value from within another PROCEDURE. eg. CALL sp_test01() INTO var_X; now I know I can use an OUT parameter for ...

  1. #1

    Default HELP: FUNCTION and PROCEDURE best practices....

    Hi All,

    Got a best practices question....

    I often have to write PROCEDURES that return a value (because it needs
    to have a transaction in it)...and I can't do that in a FUNCTION.

    eg

    CREATE PROCEDURE `sp_test01`()
    BEGIN

    START TRANSACTION;

    -- Do some insert

    COMMIT;

    SELECT 1;

    END$$

    This makes it very easy to get a return value from the CALL sp_test01()
    from, for example, C#....set it up.....ExecuteScalar.

    However, it doesn't allow me to call this and get a return value from
    within another PROCEDURE.

    eg.

    CALL sp_test01() INTO var_X;

    now I know I can use an OUT parameter for the procedure, but coding for
    that in C# is a pain. Most specifically its that in order to get the
    OUT parameter ALL the parameters HAVE to be defined EXACTLY as they are
    in the stored procedure.

    So is there another way I can do this?

    Regards

    D.

    daniel@mfaconsulting.com Guest

  2. #2

    Default Re: HELP: FUNCTION and PROCEDURE best practices....

    com wrote: 

    Standard SQL indicates you must specify the IN and OUT parameters. You
    may think it's a pain - but that's how it works.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  3. #3

    Default HELP: FUNCTION and PROCEDURE best practices....

    Its not a question of marking them as IN or OUT or INOUT....

    CREATE PROCEDURE `sp_test01`(IN _var1 INT, IN _var2 INT, OUT _Ret INT)
    BEGIN

    START TRANSACTION;

    -- Do some insert

    COMMIT;

    SET _Ret = 1;

    END$$

    ....its that in C#

    I have to call this with variables called _var1,_var2 and _Ret.....if
    you get that wrong it doesn't work. Specifically WRT getting the _Ret
    variable!

    Anyway, thanks for the reply.

    D.

    daniel@mfaconsulting.com Guest

  4. #4

    Default Re: HELP: FUNCTION and PROCEDURE best practices....

    com wrote: 

    Yep, that's true. That's the way SP's work.

    But the same is true with C# functions, is it not? If you don't call
    them with the correct parameters it doesn't work?

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  5. #5

    Default HELP: FUNCTION and PROCEDURE best practices....

    No not really the same.

    If you have C# Function foo(int x, int y)

    you can call it as foo(var1,var2)

    With the .NET libraries for MySQL. If something is an OUT parameter
    e.g. foo(IN x INT, OUT ret INT) you can no call it like this
    foo(var1,var2), you HAVE TO CALL it like this foo(x,ret), where x and
    ret are you local variables. Anyway we digress.

    Thanks for the response.

    D.

    daniel@mfaconsulting.com Guest

Similar Threads

  1. query for function/procedure metadata
    By Laurenz Albe in forum MySQL
    Replies: 4
    Last Post: April 18th, 01:50 AM
  2. Problems with Oracle Stored Function (Procedure)
    By Rob@PSA in forum Coldfusion Database Access
    Replies: 11
    Last Post: March 18th, 01:20 AM
  3. Questions on stored-procedure best practices
    By Eric Brown in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: December 11th, 08:42 PM
  4. Best practices
    By ring in forum ASP.NET Web Services
    Replies: 2
    Last Post: February 17th, 01:01 PM
  5. Some PHP best practices
    By Rutger Claes in forum PHP Development
    Replies: 0
    Last Post: July 4th, 08:55 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