{"id":997,"date":"2013-01-14T17:21:25","date_gmt":"2013-01-15T01:21:25","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=997"},"modified":"2023-11-30T00:20:38","modified_gmt":"2023-11-30T07:20:38","slug":"mysql-if-statement","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/mysql-if-statement\/","title":{"rendered":"MySQL IF Statement"},"content":{"rendered":"\n<p><strong>Summary<\/strong><em>: <\/em>in this tutorial, you will learn how to use <strong>MySQL IF statement<\/strong> to execute a block of SQL code based on a specified condition.<\/p>\n\n\n\n<p class=\"note\">Note that MySQL has an <a href=\"https:\/\/www.mysqltutorial.org\/mysql-control-flow-functions\/mysql-if-function\/\">IF()<\/a> function that differs from the <code>IF<\/code> statement described in this tutorial.<\/p>\n\n\n\n<p>The <code>IF<\/code> statement allows you to evaluate one or more conditions and execute the corresponding code block if the condition is true.<\/p>\n\n\n\n<p>The <code>IF<\/code> statement has three forms: <\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>IF...THEN<\/code>\u00a0statement: Evaluate one condition and execute a code block if the condition is true.<\/li>\n\n\n\n<li><code>IF...THEN...ELSE<\/code> statement: Evaluate one condition and execute a code block if the condition is true; otherwise, execute another code block.<\/li>\n\n\n\n<li><code>IF...THEN...ELSEIF...ELSE<\/code> statement: Evaluate multiple conditions and execute a code block if a condition is true. If all conditions are false, execute the code block in the <code>ELSE<\/code> branch.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">IF-THEN statement<\/h2>\n\n\n\n<p>The <code>IF...THEN<\/code> statement allows you to execute a set of SQL statements based on a specified condition. <\/p>\n\n\n\n<p>The following illustrates the syntax of the <code>IF-THEN<\/code> statement:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">IF condition THEN \n   statements;\n<span class=\"hljs-keyword\">END<\/span> <span class=\"hljs-keyword\">IF<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this syntax:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>First, define a condition to execute the code between the <code>IF...THEN<\/code> and <code>END IF<\/code> . If the <code>condition<\/code> is true, the statements between <code>IF-THEN<\/code> and <code>END IF<\/code> will execute. Otherwise, control is passed to the next statement following the <code>END IF<\/code>.<\/li>\n\n\n\n<li>Second, specify the code that will execute if the <code>condition<\/code> evaluates to <code>TRUE<\/code>.<\/li>\n<\/ul>\n\n\n\n<p>We&#8217;ll use the <code>customers<\/code> table from the sample database for the demonstration:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" src=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2023\/10\/customers.svg\" alt=\"\" class=\"wp-image-10765\"\/><\/figure>\n\n\n\n<p>The following creates a new stored procedure named <code>GetCustomerLevel()<\/code> in the <a href=\"https:\/\/www.mysqltutorial.org\/getting-started-with-mysql\/mysql-sample-database\/\">sample database<\/a>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">DELIMITER $$\n\n<span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">PROCEDURE<\/span> GetCustomerLevel(\n    <span class=\"hljs-keyword\">IN<\/span>  pCustomerNumber <span class=\"hljs-built_in\">INT<\/span>, \n    <span class=\"hljs-keyword\">OUT<\/span> pCustomerLevel  <span class=\"hljs-built_in\">VARCHAR<\/span>(<span class=\"hljs-number\">20<\/span>))\n<span class=\"hljs-keyword\">BEGIN<\/span>\n    <span class=\"hljs-keyword\">DECLARE<\/span> credit <span class=\"hljs-built_in\">DECIMAL<\/span>(<span class=\"hljs-number\">10<\/span>,<span class=\"hljs-number\">2<\/span>) <span class=\"hljs-keyword\">DEFAULT<\/span> <span class=\"hljs-number\">0<\/span>;\n\n    <span class=\"hljs-keyword\">SELECT<\/span> creditLimit \n    <span class=\"hljs-keyword\">INTO<\/span> credit\n    <span class=\"hljs-keyword\">FROM<\/span> customers\n    <span class=\"hljs-keyword\">WHERE<\/span> customerNumber = pCustomerNumber;\n\n    IF credit &gt; 50000 THEN\n        <span class=\"hljs-keyword\">SET<\/span> pCustomerLevel = <span class=\"hljs-string\">'PLATINUM'<\/span>;\n    <span class=\"hljs-keyword\">END<\/span> <span class=\"hljs-keyword\">IF<\/span>;\n<span class=\"hljs-keyword\">END<\/span>$$\n\nDELIMITER ;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The stored procedure <code>GetCustomerLevel()<\/code> accepts two parameters: <code>pCustomerNumber<\/code> and <code>pCustomerLevel<\/code>.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>First, select <code>creditLimit<\/code> of the customer specified by the <code>pCustomerNumber<\/code> from the <code>customers<\/code> table and store it in the local variable <code>credit<\/code>.<\/li>\n\n\n\n<li>Then, set the value for the <code>OUT<\/code> parameter <code>pCustomerLevel<\/code> to <code>PLATINUM<\/code> if the credit limit of the customer is greater than <code>50,000<\/code>.<\/li>\n<\/ul>\n\n\n\n<p>This statement finds all customers that have a credit limit greater than <code>50,000<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> \n    customerNumber, \n    creditLimit\n<span class=\"hljs-keyword\">FROM<\/span> \n    customers\n<span class=\"hljs-keyword\">WHERE<\/span> \n    creditLimit &gt; <span class=\"hljs-number\">50000<\/span>\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> \n    creditLimit <span class=\"hljs-keyword\">DESC<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Here is the partial output:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"187\" height=\"219\" src=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/09\/mysql-if-statement-customers-data.png\" alt=\"\" class=\"wp-image-8228\" srcset=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/09\/mysql-if-statement-customers-data.png 187w, https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/09\/mysql-if-statement-customers-data-171x200.png 171w\" sizes=\"auto, (max-width: 187px) 100vw, 187px\" \/><\/figure>\n\n\n\n<p>These statements call the <code>GetCustomerLevel()<\/code> stored procedure for customer 141 and show the value of the <code>OUT<\/code> parameter <code>pCustomerLevel<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CALL<\/span> GetCustomerLevel(<span class=\"hljs-number\">141<\/span>, @<span class=\"hljs-keyword\">level<\/span>);\n<span class=\"hljs-keyword\">SELECT<\/span> @<span class=\"hljs-keyword\">level<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-5\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">+----------+\r\n| @level   |\r\n+----------+\r\n| PLATINUM |\r\n+----------+\r\n<span class=\"hljs-number\">1<\/span> row <span class=\"hljs-keyword\">in<\/span> <span class=\"hljs-keyword\">set<\/span> (0.00 sec)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-5\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Because the customer 141 has a credit limit greater than <code>50,000<\/code>, its level is set to <code>PLATINUM<\/code> as expected.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">IF-THEN-ELSE statement<\/h2>\n\n\n\n<p>In case you want to execute other statements when the <code>condition<\/code> in the <code>IF<\/code> branch does not evaluate to <code>TRUE<\/code>, you can use the <code>IF-THEN-ELSE<\/code> statement as follows:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-6\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">IF condition THEN\n   statements;\nELSE\n   else-statements;\n<span class=\"hljs-keyword\">END<\/span> <span class=\"hljs-keyword\">IF<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-6\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this syntax, if the <code>condition<\/code> evaluates to true, the <code>statements<\/code> between <code>IF-THEN<\/code> and <code>ELSE<\/code> execute. Otherwise, the <code>else-statements<\/code> between the <code>ELSE<\/code> and <code>END IF<\/code> execute.<\/p>\n\n\n\n<p>Let&#8217;s modify the <code>GetCustomerLevel()<\/code> stored procedure.<\/p>\n\n\n\n<p>First, drop the <code>GetCustomerLevel<\/code> stored procedure:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-7\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">DROP<\/span> <span class=\"hljs-keyword\">PROCEDURE<\/span> GetCustomerLevel;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-7\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Then, create the <code>GetCustomerLevel<\/code> stored procedure with the new code:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-8\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">DELIMITER $$\n\n<span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">PROCEDURE<\/span> GetCustomerLevel(\n    <span class=\"hljs-keyword\">IN<\/span>  pCustomerNumber <span class=\"hljs-built_in\">INT<\/span>, \n    <span class=\"hljs-keyword\">OUT<\/span> pCustomerLevel  <span class=\"hljs-built_in\">VARCHAR<\/span>(<span class=\"hljs-number\">20<\/span>))\n<span class=\"hljs-keyword\">BEGIN<\/span>\n    <span class=\"hljs-keyword\">DECLARE<\/span> credit <span class=\"hljs-built_in\">DECIMAL<\/span> <span class=\"hljs-keyword\">DEFAULT<\/span> <span class=\"hljs-number\">0<\/span>;\n\n    <span class=\"hljs-keyword\">SELECT<\/span> creditLimit \n    <span class=\"hljs-keyword\">INTO<\/span> credit\n    <span class=\"hljs-keyword\">FROM<\/span> customers\n    <span class=\"hljs-keyword\">WHERE<\/span> customerNumber = pCustomerNumber;\n\n    IF credit &gt; 50000 THEN\n        <span class=\"hljs-keyword\">SET<\/span> pCustomerLevel = <span class=\"hljs-string\">'PLATINUM'<\/span>;\n    ELSE\n        <span class=\"hljs-keyword\">SET<\/span> pCustomerLevel = <span class=\"hljs-string\">'NOT PLATINUM'<\/span>;\n    <span class=\"hljs-keyword\">END<\/span> <span class=\"hljs-keyword\">IF<\/span>;\n<span class=\"hljs-keyword\">END<\/span>$$\n\nDELIMITER ;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-8\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In the updated stored procedure, we include the <code>ELSE<\/code> branch. If the <code>credit<\/code> is not greater than <code>50,000<\/code>, we set the customer level to <code>NOT PLATINUM<\/code> in the block between <code>ELSE<\/code> and <code>END IF<\/code>.<\/p>\n\n\n\n<p>This query finds customers that have credit limits less than or equal <code>50,000<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-9\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> \n    customerNumber, \n    creditLimit\n<span class=\"hljs-keyword\">FROM<\/span> \n    customers\n<span class=\"hljs-keyword\">WHERE<\/span> \n    creditLimit &lt;= <span class=\"hljs-number\">50000<\/span>\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> \n    creditLimit <span class=\"hljs-keyword\">DESC<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-9\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>This picture shows the partial output:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"176\" height=\"175\" src=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/09\/mysql-if-else-statement-customers-data.png\" alt=\"\" class=\"wp-image-8231\"\/><\/figure>\n\n\n\n<p>The following statements call the stored procedure for customer number <code>447<\/code> and show the value of the <code>OUT<\/code> parameter <code>pCustomerLevel<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-10\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CALL<\/span> GetCustomerLevel(<span class=\"hljs-number\">447<\/span>, @<span class=\"hljs-keyword\">level<\/span>);\n<span class=\"hljs-keyword\">SELECT<\/span> @<span class=\"hljs-keyword\">level<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-10\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-11\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">+--------------+\r\n| @level       |\r\n+--------------+\r\n| NOT PLATINUM |\r\n+--------------+\r\n<span class=\"hljs-number\">1<\/span> row <span class=\"hljs-keyword\">in<\/span> <span class=\"hljs-keyword\">set<\/span> (0.00 sec)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-11\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The credit limit of the customer <code>447<\/code> is less than <code>50,000<\/code>, therefore, the statement in the <code>ELSE<\/code> branch executes and sets the value of the <code>OUT<\/code> parameter <code>pCustomerLevel<\/code> to <code>NOT PLATINUM<\/code>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">IF-THEN-ELSEIF-ELSE statement<\/h2>\n\n\n\n<p>The <code>IF-THEN-ELSEIF-ELSE<\/code> statement allows you to check multiple conditions sequentially. Here&#8217;s the basic syntax of the <code>IF-THEN-ELSEIF-ELSE<\/code> statement:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-12\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">IF condition THEN\n   statements;\nELSEIF elseif-condition THEN\n   statements;\n...\nELSE\n   statements;\n<span class=\"hljs-keyword\">END<\/span> <span class=\"hljs-keyword\">IF<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-12\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this syntax:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>If the initial condition is true, its associated statements are executed. If it&#8217;s false, the program checks the next condition (<code>ELSEIF<\/code>). <\/li>\n\n\n\n<li>If any of the <code>ELSEIF<\/code> conditions are true, the corresponding statements are executed.<\/li>\n\n\n\n<li>If none of the conditions is true, the statements in the <code>ELSE<\/code> block are executed.<\/li>\n<\/ul>\n\n\n\n<p>We will modify the <code>GetCustomerLevel()<\/code>&nbsp;stored procedure to use the <code>IF-THEN-ELSEIF-ELSE<\/code> statement.<\/p>\n\n\n\n<p>First, drop the <code>GetCustomerLevel()<\/code> stored procedure:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-13\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">DROP<\/span> <span class=\"hljs-keyword\">PROCEDURE<\/span> GetCustomerLevel;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-13\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Then, recreate the new <code>GetCustomerLevel()<\/code>\u00a0stored procedure that uses the <code>IF-THEN-ELSEIF-ELSE<\/code> statement.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-14\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">DELIMITER $$\n\n<span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">PROCEDURE<\/span> GetCustomerLevel(\n    <span class=\"hljs-keyword\">IN<\/span>  pCustomerNumber <span class=\"hljs-built_in\">INT<\/span>, \n    <span class=\"hljs-keyword\">OUT<\/span> pCustomerLevel  <span class=\"hljs-built_in\">VARCHAR<\/span>(<span class=\"hljs-number\">20<\/span>))\n<span class=\"hljs-keyword\">BEGIN<\/span>\n    <span class=\"hljs-keyword\">DECLARE<\/span> credit <span class=\"hljs-built_in\">DECIMAL<\/span> <span class=\"hljs-keyword\">DEFAULT<\/span> <span class=\"hljs-number\">0<\/span>;\n\n    <span class=\"hljs-keyword\">SELECT<\/span> creditLimit \n    <span class=\"hljs-keyword\">INTO<\/span> credit\n    <span class=\"hljs-keyword\">FROM<\/span> customers\n    <span class=\"hljs-keyword\">WHERE<\/span> customerNumber = pCustomerNumber;\n\n    IF credit &gt; 50000 THEN\n        <span class=\"hljs-keyword\">SET<\/span> pCustomerLevel = <span class=\"hljs-string\">'PLATINUM'<\/span>;\n    ELSEIF credit &lt;= 50000 AND credit &gt; 10000 THEN\n        <span class=\"hljs-keyword\">SET<\/span> pCustomerLevel = <span class=\"hljs-string\">'GOLD'<\/span>;\n    ELSE\n        <span class=\"hljs-keyword\">SET<\/span> pCustomerLevel = <span class=\"hljs-string\">'SILVER'<\/span>;\n    <span class=\"hljs-keyword\">END<\/span> <span class=\"hljs-keyword\">IF<\/span>;\n<span class=\"hljs-keyword\">END<\/span> $$\n\nDELIMITER ;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-14\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this stored procedure:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>If the credit is greater than <code>50,000<\/code>, the level of the customer is <code>PLATINUM<\/code>.<\/li>\n\n\n\n<li>If the credit is less than or equal <code>50,000<\/code> and greater than <code>10,000<\/code>, then the level of customer is <code>GOLD<\/code>.<\/li>\n\n\n\n<li>Otherwise, the level of the customer is <code>SILVER<\/code>.<\/li>\n<\/ul>\n\n\n\n<p>These statements call the stored procedure <code>GetCustomerLevel()<\/code> and show the level of the customer <code>447<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-15\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CALL<\/span> GetCustomerLevel(<span class=\"hljs-number\">447<\/span>, @<span class=\"hljs-keyword\">level<\/span>); \n<span class=\"hljs-keyword\">SELECT<\/span> @<span class=\"hljs-keyword\">level<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-15\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-16\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">+--------+\n| @level |\n+--------+\n| GOLD   |\n+--------+\n<span class=\"hljs-number\">1<\/span> row <span class=\"hljs-keyword\">in<\/span> <span class=\"hljs-keyword\">set<\/span> (0.00 sec)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-16\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\">Summary<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use <code>IF...THEN<\/code> statement to conditionally execute a block of statements based on the evaluation of a specified condition.<\/li>\n\n\n\n<li>Use <code>IF...THEN...ELSE<\/code> statement to execute a block of statements if a specified condition is true and an alternative block of statements if the condition is false.<\/li>\n\n\n\n<li>Use <code>IF...THEN...ELSEIF...ELSE<\/code> statement to evaluate multiple conditions sequentially and execute corresponding blocks of statements based on the first true condition, with an optional block of statements to execute if none of the conditions is true.<\/li>\n<\/ul>\n<div class=\"helpful-block-content\" data-title=\"\">\n\t<header>\n\t\t<div class=\"wth-question\">Was this tutorial helpful? <\/div>\n\t\t<div class=\"wth-thumbs\">\n\t\t\t<button\n\t\t\t\tdata-post=\"997\"\n\t\t\t\tdata-post-url=\"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/mysql-if-statement\/\"\n\t\t\t\tdata-post-title=\"MySQL IF Statement\"\n\t\t\t\tdata-response=\"1\"\n\t\t\t\tclass=\"wth-btn-rounded wth-yes-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t\tclass=\"feather feather-thumbs-up block w-full h-full\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M14 9V5a3 3 0 0 0-3-3l-4 9v11h11.28a2 2 0 0 0 2-1.7l1.38-9a2 2 0 0 0-2-2.3zM7 22H4a2 2 0 0 1-2-2v-7a2 2 0 0 1 2-2h3\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t<button\n\t\t\t\tdata-response=\"0\"\n\t\t\t\tdata-post=\"997\"\n\t\t\t\tdata-post-url=\"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/mysql-if-statement\/\"\n\t\t\t\tdata-post-title=\"MySQL IF Statement\"\n\t\t\t\tclass=\"wth-btn-rounded wth-no-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M10 15v4a3 3 0 0 0 3 3l4-9V2H5.72a2 2 0 0 0-2 1.7l-1.38 9a2 2 0 0 0 2 2.3zm7-13h2.67A2.31 2.31 0 0 1 22 4v7a2.31 2.31 0 0 1-2.33 2H17\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t<div class=\"wth-form hidden\">\n\t\t<div class=\"wth-form-wrapper\">\n\t\t\t<div class=\"wth-title\"><\/div>\n\t\t\t\n\t\t\t<textarea class=\"wth-message\"><\/textarea>\n\n\t\t\t<button class=\"btn btn-primary wth-btn-submit\">Send<\/button>\n\t\t\t<button class=\"btn wth-btn-cancel\">Cancel<\/button>\n\t\t\n\t\t<\/div>\n\t<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>This tutorial shows you how to use MySQL IF statement to execute a block of SQL code based on a specified condition.<\/p>\n","protected":false},"author":2,"featured_media":3365,"parent":518,"menu_order":8,"comment_status":"closed","ping_status":"open","template":"","meta":{"footnotes":""},"class_list":["post-997","page","type-page","status-publish","has-post-thumbnail","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>MySQL IF Statement<\/title>\n<meta name=\"description\" content=\"This tutorial shows you how to use MySQL IF statement to execute a block of SQL code based on a specified condition.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/mysql-if-statement\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MySQL IF Statement\" \/>\n<meta property=\"og:description\" content=\"This tutorial shows you how to use MySQL IF statement to execute a block of SQL code based on a specified condition.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/mysql-if-statement\/\" \/>\n<meta property=\"og:site_name\" content=\"MySQL Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2023-11-30T07:20:38+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2013\/01\/mysql-if-statement.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"150\" \/>\n\t<meta property=\"og:image:height\" content=\"150\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-stored-procedure\\\/mysql-if-statement\\\/\",\"url\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-stored-procedure\\\/mysql-if-statement\\\/\",\"name\":\"MySQL IF Statement\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-stored-procedure\\\/mysql-if-statement\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-stored-procedure\\\/mysql-if-statement\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.mysqltutorial.org\\\/wp-content\\\/uploads\\\/2013\\\/01\\\/mysql-if-statement.jpg\",\"datePublished\":\"2013-01-15T01:21:25+00:00\",\"dateModified\":\"2023-11-30T07:20:38+00:00\",\"description\":\"This tutorial shows you how to use MySQL IF statement to execute a block of SQL code based on a specified condition.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-stored-procedure\\\/mysql-if-statement\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-stored-procedure\\\/mysql-if-statement\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-stored-procedure\\\/mysql-if-statement\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.mysqltutorial.org\\\/wp-content\\\/uploads\\\/2013\\\/01\\\/mysql-if-statement.jpg\",\"contentUrl\":\"https:\\\/\\\/www.mysqltutorial.org\\\/wp-content\\\/uploads\\\/2013\\\/01\\\/mysql-if-statement.jpg\",\"width\":150,\"height\":150},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-stored-procedure\\\/mysql-if-statement\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.mysqltutorial.org\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MySQL Stored Procedures\",\"item\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-stored-procedure\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"MySQL IF Statement\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/#website\",\"url\":\"https:\\\/\\\/www.mysqltutorial.org\\\/\",\"name\":\"MySQL Tutorial\",\"description\":\"A comprehensive MySQL Tutorial\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.mysqltutorial.org\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"MySQL IF Statement","description":"This tutorial shows you how to use MySQL IF statement to execute a block of SQL code based on a specified condition.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/mysql-if-statement\/","og_locale":"en_US","og_type":"article","og_title":"MySQL IF Statement","og_description":"This tutorial shows you how to use MySQL IF statement to execute a block of SQL code based on a specified condition.","og_url":"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/mysql-if-statement\/","og_site_name":"MySQL Tutorial","article_modified_time":"2023-11-30T07:20:38+00:00","og_image":[{"width":150,"height":150,"url":"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2013\/01\/mysql-if-statement.jpg","type":"image\/jpeg"}],"twitter_misc":{"Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/mysql-if-statement\/","url":"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/mysql-if-statement\/","name":"MySQL IF Statement","isPartOf":{"@id":"https:\/\/www.mysqltutorial.org\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/mysql-if-statement\/#primaryimage"},"image":{"@id":"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/mysql-if-statement\/#primaryimage"},"thumbnailUrl":"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2013\/01\/mysql-if-statement.jpg","datePublished":"2013-01-15T01:21:25+00:00","dateModified":"2023-11-30T07:20:38+00:00","description":"This tutorial shows you how to use MySQL IF statement to execute a block of SQL code based on a specified condition.","breadcrumb":{"@id":"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/mysql-if-statement\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/mysql-if-statement\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/mysql-if-statement\/#primaryimage","url":"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2013\/01\/mysql-if-statement.jpg","contentUrl":"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2013\/01\/mysql-if-statement.jpg","width":150,"height":150},{"@type":"BreadcrumbList","@id":"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/mysql-if-statement\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.mysqltutorial.org\/"},{"@type":"ListItem","position":2,"name":"MySQL Stored Procedures","item":"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/"},{"@type":"ListItem","position":3,"name":"MySQL IF Statement"}]},{"@type":"WebSite","@id":"https:\/\/www.mysqltutorial.org\/#website","url":"https:\/\/www.mysqltutorial.org\/","name":"MySQL Tutorial","description":"A comprehensive MySQL Tutorial","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.mysqltutorial.org\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/997","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/comments?post=997"}],"version-history":[{"count":3,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/997\/revisions"}],"predecessor-version":[{"id":12627,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/997\/revisions\/12627"}],"up":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/518"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/media\/3365"}],"wp:attachment":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/media?parent=997"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}