{"id":4009,"date":"2025-06-07T09:39:42","date_gmt":"2025-06-07T09:39:42","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&#038;p=4009"},"modified":"2025-06-07T09:39:44","modified_gmt":"2025-06-07T09:39:44","slug":"sql-stored-procedures","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/sql\/sql-stored-procedures\/","title":{"rendered":"SQL Stored Procedures"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Stored Procedures are pre-written SQL code saved in the database that you can execute repeatedly. They help encapsulate logic, improve performance, and simplify complex tasks.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udd39 <strong>What is a Stored Procedure?<\/strong><br>A stored procedure is a named set of SQL statements that can accept input parameters, perform operations, and optionally return results. It runs on the database server, reducing client-server communication.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udd39 <strong>Basic Syntax<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><code>-- MySQL \/ PostgreSQL \/ SQL Server syntax example\nCREATE PROCEDURE procedure_name (IN param1 INT, OUT param2 VARCHAR(50))\nBEGIN\n    -- SQL statements here\nEND;<\/code><\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Note: Syntax varies slightly by DBMS (especially for parameter modes and delimiters).<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udd39 <strong>Simple Example: Add Two Numbers<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><code>-- MySQL example\nDELIMITER \/\/\nCREATE PROCEDURE AddNumbers(IN a INT, IN b INT, OUT sum INT)\nBEGIN\n    SET sum = a + b;\nEND \/\/\nDELIMITER ;<\/code><\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Call the procedure:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><code>CALL AddNumbers(5, 7, @result);\nSELECT @result;  -- Output: 12<\/code><\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udd39 <strong>Use Cases<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Encapsulate complex business logic<\/li>\n\n\n\n<li>Reuse code efficiently<\/li>\n\n\n\n<li>Improve performance by reducing network traffic<\/li>\n\n\n\n<li>Implement transactions with multiple SQL statements<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udd39 <strong>Advantages<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Centralized logic in DB<\/li>\n\n\n\n<li>Enhanced security (control user access)<\/li>\n\n\n\n<li>Reduced client complexity<\/li>\n\n\n\n<li>Easier maintenance<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udd39 <strong>Important Notes<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Stored procedures may differ syntactically between DBMS (MySQL, Oracle, SQL Server, PostgreSQL) \u2014 always check documentation.<\/li>\n\n\n\n<li>Use appropriate error handling within procedures.<\/li>\n\n\n\n<li>Avoid overusing stored procedures for simple queries where direct SQL suffices.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83e\udde0 <strong>Quick Recap<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-table has-small-font-size\"><table><thead><tr><th>Point<\/th><th>Explanation<\/th><\/tr><\/thead><tbody><tr><td>Definition<\/td><td>Predefined SQL code saved in DB<\/td><\/tr><tr><td>Input\/Output Parameters<\/td><td>Procedures can accept inputs &amp; return outputs<\/td><\/tr><tr><td>Execution<\/td><td>Runs on DB server, callable via <code>CALL<\/code> or <code>EXEC<\/code><\/td><\/tr><tr><td>Use Cases<\/td><td>Business logic, performance, reuse<\/td><\/tr><tr><td>Syntax varies<\/td><td>Depends on DBMS<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udca1 Stored procedures streamline SQL operations and help keep your application logic organized and efficient.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Stored Procedures are pre-written SQL code saved in the database that you can execute repeatedly. They help encapsulate logic, improve performance, and simplify complex tasks. \ud83d\udd39 What is a Stored Procedure?A stored procedure is a named set of SQL statements that can accept input parameters, perform operations, and optionally return results. It runs on the [&hellip;]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,979],"class_list":["post-4009","cposts","type-cposts","status-publish","hentry","category-notes","category-sql"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/4009","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=4009"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=4009"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}