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.
Related videos on Youtube
Comments
-
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 almost 13 yearsThe last part is not true in SQL Server.
-
Elad Lachmi almost 13 yearsWell that's the way it's done in LINQ2SQL, anyway. You may be right. I thought it was more general.
-
Elad Lachmi almost 13 yearsJust read your link, it referes to TSQL. Since the asker does not specify a language, my answer is more general, I think.
-
Artem almost 13 yearsThen tell us which database you are using.
-
Kasun Siyambalapitiya almost 8 years@EladLachmi what is mentioned by SP in your answer
-
Kasun Siyambalapitiya almost 8 yearswhat about
INOUT
? -
Elad Lachmi almost 8 years@KasunSiyambalapitiya - Stored Procedure