Stored procedures IN, OUT, INOUT parameters

12,539

Solution 1

1. IN

    mysql> CREATE PROCEDURE in_2(IN value INT )BEGIN SELECT value; SET value =100;SE
    LECT value;END//
    Query OK, 0 rows affected (0.00 sec)

     mysql> SET @s =9//
    Query OK, 0 rows affected (0.00 sec)

    mysql> CALL in_2(@s)//
    +-------+
    | value |
    +-------+
    |     9 |
    +-------+
    1 row in set (0.00 sec)

    +-------+
    | value |
    +-------+
    |   100 |
    +-------+
    1 row in set (0.00 sec)

mysql> SELECT @s;
    -> //
+------+
| @s   |
+------+
|    9 |
+------+
1 row in set (0.00 sec) 

2.OUT

mysql> CREATE PROCEDURE in_3(OUT value INT)
    -> SET value=100//
Query OK, 0 rows affected (0.00 sec)

mysql> SET @x=56//
Query OK, 0 rows affected (0.00 sec)

mysql> CALL in_3(@x)//
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x//
+------+
| @x   |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

Solution 2

IN parameters are passed in to the SP by value. OUT parameters are returned from the SP by value. INOUT parameters are passed by reference, since they contain one value going in and another coming out.

Share:
12,539

Related videos on Youtube

xdevel2000
Author by

xdevel2000

:)

Updated on May 27, 2022

Comments

  • xdevel2000
    xdevel2000 almost 2 years

    Can anyone give me a detailed explanation of the difference between IN, OUT, and INOUT parameters?

    Thanks.

    P.S. I'm using MySQL 5.5

  • Martin Smith
    Martin Smith almost 13 years
    The last part is not true in SQL Server.
  • Elad Lachmi
    Elad Lachmi almost 13 years
    Well that's the way it's done in LINQ2SQL, anyway. You may be right. I thought it was more general.
  • Elad Lachmi
    Elad Lachmi almost 13 years
    Just read your link, it referes to TSQL. Since the asker does not specify a language, my answer is more general, I think.
  • Artem
    Artem almost 13 years
    Then tell us which database you are using.
  • Kasun Siyambalapitiya
    Kasun Siyambalapitiya almost 8 years
    @EladLachmi what is mentioned by SP in your answer
  • Kasun Siyambalapitiya
    Kasun Siyambalapitiya almost 8 years
    what about INOUT?
  • Elad Lachmi
    Elad Lachmi almost 8 years
    @KasunSiyambalapitiya - Stored Procedure