Stored Procedures - Learning Module

Loading content...

0/252

CREATE PROCEDURE

Defining Your First Procedures

Every stored procedure begins with the CREATE PROCEDURE statement—the DDL command that transforms SQL logic into a named, reusable database object. While the core concept is universal across database systems, the syntax and capabilities vary significantly between SQL Server's T-SQL, Oracle's PL/SQL, PostgreSQL's PL/pgSQL, and MySQL's SQL/PSM.

Mastering CREATE PROCEDURE means understanding not just where to put your SQL, but how to structure procedures for maintainability, how naming conventions affect discoverability, how to document your work, and how to avoid common pitfalls that make procedures difficult to debug and maintain.

What You Will Learn

By the end of this page, you will understand the complete anatomy of CREATE PROCEDURE across major database systems, how to structure procedure bodies effectively, naming conventions used in professional environments, documentation practices, and the differences between CREATE, ALTER, and CREATE OR REPLACE semantics.

Basic Syntax Structure

The CREATE PROCEDURE statement follows a general pattern across all databases, though specific syntax elements differ:

Universal Structure:

CREATE PROCEDURE procedure_name
    ( parameter_list )
    [ additional_clauses ]
AS | BEGIN
    procedure_body
END;

The key components are:

CREATE PROCEDURE — The DDL keyword combination
procedure_name — Unique identifier within the schema
parameter_list — Optional input/output parameters
additional_clauses — Database-specific modifiers (language, security, etc.)
procedure_body — The SQL and procedural statements to execute

create_procedure_basic.sql

-- Basic procedure structure in SQL Server
CREATE PROCEDURE dbo.GetActiveCustomers
AS
BEGIN
    -- SET NOCOUNT ON prevents row count messages
    SET NOCOUNT ON;
    
    SELECT 
        CustomerID,
        CustomerName,
        Email,
        LastOrderDate
    FROM Customers
    WHERE Status = 'Active'
    ORDER BY CustomerName;
END;
GO
 
-- Calling the procedure
EXEC dbo.GetActiveCustomers;
-- or
EXECUTE dbo.GetActiveCustomers;

PostgreSQL Procedure Behavior

PostgreSQL introduced procedures in version 11 primarily for transaction control (COMMIT/ROLLBACK within procedures). For returning result sets, PostgreSQL uses functions with RETURNS TABLE or RETURNS SETOF. This differs from SQL Server and MySQL where procedures can freely return result sets.

Procedure Naming Conventions

Professional database development demands consistent, meaningful naming conventions. Procedure names should communicate purpose at a glance.

Common naming patterns:

Procedure Naming Conventions by Organization Style
Pattern	Example	Use Case
`action_entity`	`get_customer`, `update_order`	Simple, readable (PostgreSQL style)
`sp_ActionEntity`	`sp_GetCustomer`, `sp_UpdateOrder`	Legacy SQL Server style (avoid reserved names)
`usp_EntityAction`	`usp_CustomerGet`, `usp_OrderUpdate`	User stored procedure prefix
`Entity_Action`	`Customer_Get`, `Order_Update`	Entity-first organization
`proc_ModuleAction`	`proc_SalesGetTotal`, `proc_InvAdjust`	Module-prefixed grouping

Naming Best Practices

•Be descriptive — Names should explain what the procedure does: CalculateOrderTotal not CalcOT
•Use consistent casing — Choose PascalCase, snake_case, or camelCase and apply uniformly
•Include action verbs — Get, Set, Insert, Update, Delete, Calculate, Validate, Process
•Avoid reserved prefixes — SQL Server reserves sp_ for system procedures; use usp_ instead
•Group related procedures — Prefix by entity or module: Order_Create, Order_Cancel, Order_Ship
•Consider schema organization — Use schemas (dbo, sales, hr) to namespace procedures

Schema-Based Organization

Rather than encoding module names in procedure names (proc_SalesGetTotal), consider using schemas: sales.GetTotal, inventory.AdjustStock. Schemas provide cleaner naming, easier permission management, and better tooling support.

naming_examples.sql

SQL Server

-- Schema-based organization (recommended)
CREATE SCHEMA Sales;
GO
 
CREATE PROCEDURE Sales.GetOrderTotal
    @OrderID INT
AS
BEGIN
    SELECT SUM(Quantity * UnitPrice) AS Total
    FROM Sales.OrderLines
    WHERE OrderID = @OrderID;
END;
GO
 
CREATE PROCEDURE Sales.ProcessReturn
    @OrderID INT,
    @Reason NVARCHAR(500)
AS
BEGIN
    -- Return processing logic
END;
GO
 
-- Calling with schema qualification
EXEC Sales.GetOrderTotal @OrderID = 12345;
 
-- Compare with prefixed naming (less recommended)
CREATE PROCEDURE usp_Sales_GetOrderTotal
    @OrderID INT
AS
BEGIN
    -- Same logic but name is longer and less clean
END;

CREATE vs ALTER vs CREATE OR REPLACE

Procedure management involves creating new procedures and modifying existing ones. Different databases offer different mechanisms:

CREATE PROCEDURE:

Creates a new procedure
Fails if procedure already exists (except with OR REPLACE)
Used for initial deployment

ALTER PROCEDURE:

Modifies an existing procedure
Fails if procedure doesn't exist
Preserves permissions (usually)
Available in SQL Server, partially in other systems

CREATE OR REPLACE:

Creates if new, replaces if exists
Single idempotent statement for deployment
Available in PostgreSQL, Oracle
Preferred pattern for CI/CD deployments

DROP + CREATE:

Removes and recreates
Loses permissions — must regrant after
MySQL's only modification approach
Risky in production due to permission reset

modification_patterns.sql

-- CREATE: Fails if exists
CREATE PROCEDURE dbo.CalculateTax
    @Amount DECIMAL(18,2),
    @State CHAR(2),
    @TaxAmount DECIMAL(18,2) OUTPUT
AS
BEGIN
    SET @TaxAmount = CASE @State
        WHEN 'CA' THEN @Amount * 0.0725
        WHEN 'NY' THEN @Amount * 0.08
        ELSE @Amount * 0.06
    END;
END;
GO
 
-- ALTER: Modify existing (preserves permissions)
ALTER PROCEDURE dbo.CalculateTax
    @Amount DECIMAL(18,2),
    @State CHAR(2),
    @TaxAmount DECIMAL(18,2) OUTPUT
AS
BEGIN
    -- Updated logic with new states
    SET @TaxAmount = CASE @State
        WHEN 'CA' THEN @Amount * 0.0725
        WHEN 'NY' THEN @Amount * 0.08875  -- Updated rate
        WHEN 'TX' THEN @Amount * 0.0625   -- Added Texas
        WHEN 'FL' THEN @Amount * 0.06
        ELSE @Amount * 0.05
    END;
END;
GO
 
-- CREATE OR ALTER (SQL Server 2016+): Best of both
CREATE OR ALTER PROCEDURE dbo.CalculateTax
    @Amount DECIMAL(18,2),
    @State CHAR(2),
    @TaxAmount DECIMAL(18,2) OUTPUT
AS
BEGIN
    -- This works whether procedure exists or not
    SET @TaxAmount = CASE @State
        WHEN 'CA' THEN @Amount * 0.0725
        WHEN 'NY' THEN @Amount * 0.08875
        WHEN 'TX' THEN @Amount * 0.0625
        WHEN 'FL' THEN @Amount * 0.06
        ELSE @Amount * 0.05
    END;
END;
GO

Permission Management

DROP PROCEDURE removes all GRANT permissions. In MySQL where DROP/CREATE is required, always script permission grants alongside procedure definitions. In SQL Server, prefer ALTER or CREATE OR ALTER to preserve permissions.

Procedure Body Structure

The procedure body contains the logic to execute. Different databases structure this differently, but common elements include:

Declaration Section:

Define local variables
Declare cursors
Define local exception handlers

Execution Section:

SQL statements (SELECT, INSERT, UPDATE, DELETE)
Control flow (IF, CASE, LOOP, WHILE)
Variable assignments
Procedure calls

Exception Section (optional):

Error handling code
Cleanup operations

body_structure.sql

CREATE PROCEDURE Sales.ProcessOrder
    @OrderID INT,
    @Success BIT OUTPUT,
    @ErrorMessage NVARCHAR(500) OUTPUT
AS
BEGIN
    -- =============================================
    -- Declaration Section
    -- =============================================
    SET NOCOUNT ON;  -- Suppress row count messages
    
    DECLARE @TotalAmount DECIMAL(18,2);
    DECLARE @CustomerID INT;
    DECLARE @InventoryOK BIT = 1;
    
    -- =============================================
    -- Execution Section
    -- =============================================
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Get order details
        SELECT @CustomerID = CustomerID,
               @TotalAmount = TotalAmount
        FROM Sales.Orders
        WHERE OrderID = @OrderID;
        
        -- Validate inventory
        IF EXISTS (
            SELECT 1 FROM Sales.OrderLines ol
            JOIN Inventory.Stock s ON ol.ProductID = s.ProductID
            WHERE ol.OrderID = @OrderID
              AND ol.Quantity > s.AvailableQuantity
        )
        BEGIN
            SET @InventoryOK = 0;
            SET @ErrorMessage = 'Insufficient inventory';
        END
        
        IF @InventoryOK = 1
        BEGIN
            -- Deduct inventory
            UPDATE s
            SET s.AvailableQuantity = s.AvailableQuantity - ol.Quantity
            FROM Inventory.Stock s
            JOIN Sales.OrderLines ol ON s.ProductID = ol.ProductID
            WHERE ol.OrderID = @OrderID;
            
            -- Update order status
            UPDATE Sales.Orders
            SET Status = 'Processed',
                ProcessedDate = GETDATE()
            WHERE OrderID = @OrderID;
            
            -- Record transaction
            INSERT INTO Sales.Transactions (OrderID, Amount, TransactionDate)
            VALUES (@OrderID, @TotalAmount, GETDATE());
            
            SET @Success = 1;
            SET @ErrorMessage = NULL;
        END
        ELSE
        BEGIN
            SET @Success = 0;
        END
        
        COMMIT TRANSACTION;
    END TRY
    
    -- =============================================
    -- Exception Section
    -- =============================================
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        SET @Success = 0;
        SET @ErrorMessage = ERROR_MESSAGE();
    END CATCH
END;

Delimiter Handling

MySQL requires special handling for procedure definitions because the default statement delimiter (semicolon) conflicts with semicolons inside the procedure body.

The problem:

When you type CREATE PROCEDURE ... BEGIN ... END;, the MySQL client sees the first semicolon inside the procedure body and thinks the statement is complete. The procedure definition fails.

The solution:

Temporarily change the delimiter to something else (commonly // or $$), define the procedure, then restore the delimiter.

mysql_delimiter.sql

MySQL

-- Common error: This FAILS without delimiter change
-- CREATE PROCEDURE GetCount()
-- BEGIN
--     SELECT COUNT(*) FROM users;  <-- Client stops here
-- END;
 
-- Correct approach: Change delimiter
DELIMITER //
 
CREATE PROCEDURE inventory.calculate_reorder_point(
    IN p_product_id INT,
    OUT p_reorder_point INT,
    OUT p_reorder_quantity INT
)
BEGIN
    DECLARE v_avg_daily_sales DECIMAL(10,2);
    DECLARE v_lead_time_days INT;
    DECLARE v_safety_stock INT;
    
    -- Calculate average daily sales
    SELECT AVG(quantity)
    INTO v_avg_daily_sales
    FROM sales_history
    WHERE product_id = p_product_id
      AND sale_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY);
    
    -- Get product lead time
    SELECT lead_time_days, safety_stock_days
    INTO v_lead_time_days, v_safety_stock
    FROM products
    WHERE product_id = p_product_id;
    
    -- Calculate reorder point: (avg daily sales × lead time) + safety stock
    SET p_reorder_point = CEILING(
        v_avg_daily_sales * (v_lead_time_days + v_safety_stock)
    );
    
    -- Economic order quantity (simplified)
    SET p_reorder_quantity = CEILING(v_avg_daily_sales * 30);  
    
END //
 
DELIMITER ;
 
-- Now you can call normally
CALL inventory.calculate_reorder_point(101, @reorder_pt, @reorder_qty);
SELECT @reorder_pt AS reorder_point, @reorder_qty AS reorder_quantity;

Delimiter Best Practices

Always restore the delimiter to ; after your procedure definition. Use a consistent delimiter across your team (// or $$). When working with tools like MySQL Workbench, the tool may handle delimiters automatically, but scripts should always be explicit.

Procedure Attributes and Options

Beyond the basic structure, procedures can have various attributes that control their behavior, security context, and optimization:

Common options across databases:

Procedure Attributes by Database System
Attribute	SQL Server	PostgreSQL	MySQL	Oracle
Security Context	EXECUTE AS	SECURITY DEFINER/INVOKER	SQL SECURITY	AUTHID
Language	Implicit T-SQL	LANGUAGE plpgsql/sql	Implicit SQL/PSM	Implicit PL/SQL
Determinism	N/A	IMMUTABLE/STABLE/VOLATILE	DETERMINISTIC/NOT DETERMINISTIC	DETERMINISTIC
Read/Modify	N/A	N/A	READS SQL DATA/MODIFIES SQL DATA	N/A
Encryption	WITH ENCRYPTION	N/A	N/A	WRAPPED (obfuscation)

procedure_options.sql

-- Security context options
CREATE PROCEDURE dbo.GetSensitiveData
WITH EXECUTE AS OWNER  -- Runs with procedure owner's permissions
AS
BEGIN
    SELECT * FROM ConfidentialRecords;
END;
GO
 
-- Encryption: Hide procedure source code
CREATE PROCEDURE dbo.SecretAlgorithm
WITH ENCRYPTION
AS
BEGIN
    -- Source will not be visible in system views
    SELECT 'Secret calculation' AS Result;
END;
GO
 
-- Recompile: Generate fresh plan on each execution
CREATE PROCEDURE dbo.DynamicReport
WITH RECOMPILE
AS
BEGIN
    -- Good for procedures with highly variable data distributions
    SELECT * FROM SalesData WHERE Region = 'Dynamic';
END;
GO
 
-- Native compilation (In-Memory OLTP)
CREATE PROCEDURE dbo.FastInsert
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, 
                   LANGUAGE = N'English')
    INSERT INTO dbo.InMemoryTable (Col1, Col2)
    VALUES (1, 2);
END;
GO

SECURITY DEFINER Risks

SECURITY DEFINER/EXECUTE AS OWNER can create security vulnerabilities if not used carefully. In PostgreSQL, always SET search_path explicitly to prevent privilege escalation attacks. Document why elevated permissions are necessary.

Documentation Practices

Well-documented procedures are maintainable procedures. Professional database development requires consistent documentation standards.

Essential documentation elements:

Purpose — What the procedure does
Parameters — Description of each input/output
Returns — What results or outputs to expect
Dependencies — Tables, other procedures, external systems
Examples — How to call the procedure
Change History — Who changed what and when
Notes — Performance considerations, edge cases, warnings

documentation_example.sql

-- ===========================================================================
-- Procedure: Sales.ProcessRefund
-- ===========================================================================
-- Purpose:   Processes a refund for a completed order. Restores inventory,
--            creates a refund transaction record, and updates order status.
--
-- Parameters:
--   @OrderID        INT           - The order to refund (required)
--   @RefundReason   NVARCHAR(500) - Reason for refund (required)
--   @PartialAmount  DECIMAL(18,2) - Partial refund amount; NULL for full refund
--   @RefundID       INT OUTPUT    - Returns the ID of the created refund record
--   @Success        BIT OUTPUT    - 1 if successful, 0 if failed
--   @ErrorMessage   NVARCHAR(500) OUTPUT - Error description if failed
--
-- Returns:    Outputs populated, no result set
--
-- Dependencies:
--   - Tables: Sales.Orders, Sales.OrderLines, Sales.Refunds, Inventory.Stock
--   - Procedures: Inventory.RestoreStock
--
-- Example:
--   DECLARE @rid INT, @ok BIT, @err NVARCHAR(500);
--   EXEC Sales.ProcessRefund 
--       @OrderID = 12345,
--       @RefundReason = 'Customer request - item not as described',
--       @PartialAmount = NULL,  -- Full refund
--       @RefundID = @rid OUTPUT,
--       @Success = @ok OUTPUT,
--       @ErrorMessage = @err OUTPUT;
--   SELECT @rid AS RefundID, @ok AS Success, @err AS Error;
--
-- Change History:
--   2024-01-15  Smith, J.   Initial creation
--   2024-03-22  Doe, A.     Added partial refund support
--   2024-06-10  Smith, J.   Fixed inventory restoration for multi-line orders
--
-- Notes:
--   - Full refunds automatically restore all inventory
--   - Partial refunds do NOT restore inventory (manual process)
--   - Order must be in 'Completed' or 'Shipped' status
--   - Creates audit trail in Sales.RefundAudit
-- ===========================================================================
CREATE OR ALTER PROCEDURE Sales.ProcessRefund
    @OrderID        INT,
    @RefundReason   NVARCHAR(500),
    @PartialAmount  DECIMAL(18,2) = NULL,
    @RefundID       INT OUTPUT,
    @Success        BIT OUTPUT,
    @ErrorMessage   NVARCHAR(500) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Implementation...
    
END;
GO

Database Comments

Beyond inline comments, use database-level comments (COMMENT ON in PostgreSQL, sp_addextendedproperty in SQL Server). These are queryable and can integrate with documentation systems.

Common CREATE PROCEDURE Mistakes

Avoid these common pitfalls when creating stored procedures:

Mistakes to Avoid

•Missing SET NOCOUNT ON (SQL Server) — Without it, row count messages are sent to client after each statement, causing overhead and potentially confusing ORM/driver logic
•Using reserved procedure prefixes — sp_ in SQL Server causes the engine to search master database first, adding lookup overhead
•No error handling — Procedures without TRY/CATCH or EXCEPTION blocks fail ungracefully, leaving transactions half-complete
•Hardcoded values — Magic numbers and strings should be parameters or configuration-based
•No schema qualification — SELECT * FROM Orders may resolve to wrong schema; always qualify: Sales.Orders
•Missing transaction control — Multi-statement operations without explicit transactions risk partial updates
•**Over-permissive SELECT *** — Returns unnecessary columns, breaks when schema changes; list specific columns
•No validation of inputs — Trusting all parameter values without validation risks data integrity issues

common_mistakes.sql

SQL Server

-- ❌ BAD: Multiple common mistakes
CREATE PROCEDURE sp_ProcessOrder  -- Reserved prefix
    @id int                        -- Cryptic name
AS
    SELECT * FROM Orders WHERE OrderID = @id  -- No schema, SELECT *
    UPDATE Orders SET Status = 'Done' WHERE OrderID = @id  -- No transaction
GO
 
-- ✅ GOOD: Professional implementation
CREATE PROCEDURE Sales.ProcessOrder
    @OrderID INT,
    @Success BIT OUTPUT,
    @ErrorMessage NVARCHAR(500) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;  -- Suppress row counts
    
    -- Validate input
    IF @OrderID IS NULL OR @OrderID <= 0
    BEGIN
        SET @Success = 0;
        SET @ErrorMessage = 'Invalid OrderID parameter';
        RETURN;
    END
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Check order exists
        IF NOT EXISTS (SELECT 1 FROM Sales.Orders WHERE OrderID = @OrderID)
        BEGIN
            SET @Success = 0;
            SET @ErrorMessage = 'Order not found: ' + CAST(@OrderID AS NVARCHAR);
            RETURN;
        END
        
        -- Update with specific columns
        UPDATE Sales.Orders 
        SET Status = 'Processed',
            ProcessedDate = GETDATE(),
            ProcessedBy = SYSTEM_USER
        WHERE OrderID = @OrderID;
        
        COMMIT TRANSACTION;
        SET @Success = 1;
        SET @ErrorMessage = NULL;
        
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        SET @Success = 0;
        SET @ErrorMessage = ERROR_MESSAGE();
    END CATCH
END;
GO

Summary: Creating Professional Procedures

We've explored the CREATE PROCEDURE statement in depth. Let's consolidate the key takeaways:

Key Takeaways

•Syntax varies by database — Core concepts are universal, but T-SQL, PL/pgSQL, MySQL's PSM, and PL/SQL have distinct syntax requirements.
•Naming matters — Use descriptive names with consistent conventions; leverage schemas for organization over prefixes.
•Choose the right modification approach — CREATE OR REPLACE/ALTER preserves permissions; DROP+CREATE loses them.
•Structure procedures consistently — Declaration, execution, and exception sections create maintainable code.
•Handle MySQL delimiters explicitly — Use DELIMITER to avoid parsing conflicts.
•Understand procedure attributes — Security context, determinism, and other options affect behavior significantly.
•Document thoroughly — Header comments with purpose, parameters, examples, and change history are mandatory for professional work.
•Avoid common mistakes — SET NOCOUNT ON, schema qualification, error handling, and input validation.

What's next:

With CREATE PROCEDURE mastered, we move to parameters—the mechanism for passing data into and out of procedures. The next page covers IN, OUT, and INOUT parameters, default values, and parameter passing patterns.

Page Complete

You now understand how to create stored procedures across major database systems, including proper naming, modification strategies, body structure, attributes, and documentation. Next, we'll learn how to parameterize procedures for flexible, reusable code.

CREATE PROCEDURE

Defining Your First Procedures

What You Will Learn

Basic Syntax Structure

The CREATE PROCEDURE statement follows a general pattern across all databases, though specific syntax elements differ:

Universal Structure:

CREATE PROCEDURE procedure_name
    ( parameter_list )
    [ additional_clauses ]
AS | BEGIN
    procedure_body
END;

The key components are:

CREATE PROCEDURE — The DDL keyword combination
procedure_name — Unique identifier within the schema
parameter_list — Optional input/output parameters
additional_clauses — Database-specific modifiers (language, security, etc.)
procedure_body — The SQL and procedural statements to execute

create_procedure_basic.sql

-- Basic procedure structure in SQL Server
CREATE PROCEDURE dbo.GetActiveCustomers
AS
BEGIN
    -- SET NOCOUNT ON prevents row count messages
    SET NOCOUNT ON;
    
    SELECT 
        CustomerID,
        CustomerName,
        Email,
        LastOrderDate
    FROM Customers
    WHERE Status = 'Active'
    ORDER BY CustomerName;
END;
GO
 
-- Calling the procedure
EXEC dbo.GetActiveCustomers;
-- or
EXECUTE dbo.GetActiveCustomers;

PostgreSQL Procedure Behavior

Procedure Naming Conventions

Professional database development demands consistent, meaningful naming conventions. Procedure names should communicate purpose at a glance.

Common naming patterns:

Procedure Naming Conventions by Organization Style
Pattern	Example	Use Case
`action_entity`	`get_customer`, `update_order`	Simple, readable (PostgreSQL style)
`sp_ActionEntity`	`sp_GetCustomer`, `sp_UpdateOrder`	Legacy SQL Server style (avoid reserved names)
`usp_EntityAction`	`usp_CustomerGet`, `usp_OrderUpdate`	User stored procedure prefix
`Entity_Action`	`Customer_Get`, `Order_Update`	Entity-first organization
`proc_ModuleAction`	`proc_SalesGetTotal`, `proc_InvAdjust`	Module-prefixed grouping

Naming Best Practices

•Be descriptive — Names should explain what the procedure does: CalculateOrderTotal not CalcOT
•Use consistent casing — Choose PascalCase, snake_case, or camelCase and apply uniformly
•Include action verbs — Get, Set, Insert, Update, Delete, Calculate, Validate, Process
•Avoid reserved prefixes — SQL Server reserves sp_ for system procedures; use usp_ instead
•Group related procedures — Prefix by entity or module: Order_Create, Order_Cancel, Order_Ship
•Consider schema organization — Use schemas (dbo, sales, hr) to namespace procedures

Schema-Based Organization

naming_examples.sql

SQL Server

-- Schema-based organization (recommended)
CREATE SCHEMA Sales;
GO
 
CREATE PROCEDURE Sales.GetOrderTotal
    @OrderID INT
AS
BEGIN
    SELECT SUM(Quantity * UnitPrice) AS Total
    FROM Sales.OrderLines
    WHERE OrderID = @OrderID;
END;
GO
 
CREATE PROCEDURE Sales.ProcessReturn
    @OrderID INT,
    @Reason NVARCHAR(500)
AS
BEGIN
    -- Return processing logic
END;
GO
 
-- Calling with schema qualification
EXEC Sales.GetOrderTotal @OrderID = 12345;
 
-- Compare with prefixed naming (less recommended)
CREATE PROCEDURE usp_Sales_GetOrderTotal
    @OrderID INT
AS
BEGIN
    -- Same logic but name is longer and less clean
END;

CREATE vs ALTER vs CREATE OR REPLACE

Procedure management involves creating new procedures and modifying existing ones. Different databases offer different mechanisms:

CREATE PROCEDURE:

Creates a new procedure
Fails if procedure already exists (except with OR REPLACE)
Used for initial deployment

ALTER PROCEDURE:

Modifies an existing procedure
Fails if procedure doesn't exist
Preserves permissions (usually)
Available in SQL Server, partially in other systems

CREATE OR REPLACE:

Creates if new, replaces if exists
Single idempotent statement for deployment
Available in PostgreSQL, Oracle
Preferred pattern for CI/CD deployments

DROP + CREATE:

Removes and recreates
Loses permissions — must regrant after
MySQL's only modification approach
Risky in production due to permission reset

modification_patterns.sql

-- CREATE: Fails if exists
CREATE PROCEDURE dbo.CalculateTax
    @Amount DECIMAL(18,2),
    @State CHAR(2),
    @TaxAmount DECIMAL(18,2) OUTPUT
AS
BEGIN
    SET @TaxAmount = CASE @State
        WHEN 'CA' THEN @Amount * 0.0725
        WHEN 'NY' THEN @Amount * 0.08
        ELSE @Amount * 0.06
    END;
END;
GO
 
-- ALTER: Modify existing (preserves permissions)
ALTER PROCEDURE dbo.CalculateTax
    @Amount DECIMAL(18,2),
    @State CHAR(2),
    @TaxAmount DECIMAL(18,2) OUTPUT
AS
BEGIN
    -- Updated logic with new states
    SET @TaxAmount = CASE @State
        WHEN 'CA' THEN @Amount * 0.0725
        WHEN 'NY' THEN @Amount * 0.08875  -- Updated rate
        WHEN 'TX' THEN @Amount * 0.0625   -- Added Texas
        WHEN 'FL' THEN @Amount * 0.06
        ELSE @Amount * 0.05
    END;
END;
GO
 
-- CREATE OR ALTER (SQL Server 2016+): Best of both
CREATE OR ALTER PROCEDURE dbo.CalculateTax
    @Amount DECIMAL(18,2),
    @State CHAR(2),
    @TaxAmount DECIMAL(18,2) OUTPUT
AS
BEGIN
    -- This works whether procedure exists or not
    SET @TaxAmount = CASE @State
        WHEN 'CA' THEN @Amount * 0.0725
        WHEN 'NY' THEN @Amount * 0.08875
        WHEN 'TX' THEN @Amount * 0.0625
        WHEN 'FL' THEN @Amount * 0.06
        ELSE @Amount * 0.05
    END;
END;
GO

Permission Management

Procedure Body Structure

The procedure body contains the logic to execute. Different databases structure this differently, but common elements include:

Declaration Section:

Define local variables
Declare cursors
Define local exception handlers

Execution Section:

SQL statements (SELECT, INSERT, UPDATE, DELETE)
Control flow (IF, CASE, LOOP, WHILE)
Variable assignments
Procedure calls

Exception Section (optional):

Error handling code
Cleanup operations

body_structure.sql

CREATE PROCEDURE Sales.ProcessOrder
    @OrderID INT,
    @Success BIT OUTPUT,
    @ErrorMessage NVARCHAR(500) OUTPUT
AS
BEGIN
    -- =============================================
    -- Declaration Section
    -- =============================================
    SET NOCOUNT ON;  -- Suppress row count messages
    
    DECLARE @TotalAmount DECIMAL(18,2);
    DECLARE @CustomerID INT;
    DECLARE @InventoryOK BIT = 1;
    
    -- =============================================
    -- Execution Section
    -- =============================================
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Get order details
        SELECT @CustomerID = CustomerID,
               @TotalAmount = TotalAmount
        FROM Sales.Orders
        WHERE OrderID = @OrderID;
        
        -- Validate inventory
        IF EXISTS (
            SELECT 1 FROM Sales.OrderLines ol
            JOIN Inventory.Stock s ON ol.ProductID = s.ProductID
            WHERE ol.OrderID = @OrderID
              AND ol.Quantity > s.AvailableQuantity
        )
        BEGIN
            SET @InventoryOK = 0;
            SET @ErrorMessage = 'Insufficient inventory';
        END
        
        IF @InventoryOK = 1
        BEGIN
            -- Deduct inventory
            UPDATE s
            SET s.AvailableQuantity = s.AvailableQuantity - ol.Quantity
            FROM Inventory.Stock s
            JOIN Sales.OrderLines ol ON s.ProductID = ol.ProductID
            WHERE ol.OrderID = @OrderID;
            
            -- Update order status
            UPDATE Sales.Orders
            SET Status = 'Processed',
                ProcessedDate = GETDATE()
            WHERE OrderID = @OrderID;
            
            -- Record transaction
            INSERT INTO Sales.Transactions (OrderID, Amount, TransactionDate)
            VALUES (@OrderID, @TotalAmount, GETDATE());
            
            SET @Success = 1;
            SET @ErrorMessage = NULL;
        END
        ELSE
        BEGIN
            SET @Success = 0;
        END
        
        COMMIT TRANSACTION;
    END TRY
    
    -- =============================================
    -- Exception Section
    -- =============================================
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        SET @Success = 0;
        SET @ErrorMessage = ERROR_MESSAGE();
    END CATCH
END;

Delimiter Handling

MySQL requires special handling for procedure definitions because the default statement delimiter (semicolon) conflicts with semicolons inside the procedure body.

The problem:

When you type CREATE PROCEDURE ... BEGIN ... END;, the MySQL client sees the first semicolon inside the procedure body and thinks the statement is complete. The procedure definition fails.

The solution:

Temporarily change the delimiter to something else (commonly // or $$), define the procedure, then restore the delimiter.

mysql_delimiter.sql

MySQL

-- Common error: This FAILS without delimiter change
-- CREATE PROCEDURE GetCount()
-- BEGIN
--     SELECT COUNT(*) FROM users;  <-- Client stops here
-- END;
 
-- Correct approach: Change delimiter
DELIMITER //
 
CREATE PROCEDURE inventory.calculate_reorder_point(
    IN p_product_id INT,
    OUT p_reorder_point INT,
    OUT p_reorder_quantity INT
)
BEGIN
    DECLARE v_avg_daily_sales DECIMAL(10,2);
    DECLARE v_lead_time_days INT;
    DECLARE v_safety_stock INT;
    
    -- Calculate average daily sales
    SELECT AVG(quantity)
    INTO v_avg_daily_sales
    FROM sales_history
    WHERE product_id = p_product_id
      AND sale_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY);
    
    -- Get product lead time
    SELECT lead_time_days, safety_stock_days
    INTO v_lead_time_days, v_safety_stock
    FROM products
    WHERE product_id = p_product_id;
    
    -- Calculate reorder point: (avg daily sales × lead time) + safety stock
    SET p_reorder_point = CEILING(
        v_avg_daily_sales * (v_lead_time_days + v_safety_stock)
    );
    
    -- Economic order quantity (simplified)
    SET p_reorder_quantity = CEILING(v_avg_daily_sales * 30);  
    
END //
 
DELIMITER ;
 
-- Now you can call normally
CALL inventory.calculate_reorder_point(101, @reorder_pt, @reorder_qty);
SELECT @reorder_pt AS reorder_point, @reorder_qty AS reorder_quantity;

Delimiter Best Practices

Procedure Attributes and Options

Beyond the basic structure, procedures can have various attributes that control their behavior, security context, and optimization:

Common options across databases:

Procedure Attributes by Database System
Attribute	SQL Server	PostgreSQL	MySQL	Oracle
Security Context	EXECUTE AS	SECURITY DEFINER/INVOKER	SQL SECURITY	AUTHID
Language	Implicit T-SQL	LANGUAGE plpgsql/sql	Implicit SQL/PSM	Implicit PL/SQL
Determinism	N/A	IMMUTABLE/STABLE/VOLATILE	DETERMINISTIC/NOT DETERMINISTIC	DETERMINISTIC
Read/Modify	N/A	N/A	READS SQL DATA/MODIFIES SQL DATA	N/A
Encryption	WITH ENCRYPTION	N/A	N/A	WRAPPED (obfuscation)

procedure_options.sql

-- Security context options
CREATE PROCEDURE dbo.GetSensitiveData
WITH EXECUTE AS OWNER  -- Runs with procedure owner's permissions
AS
BEGIN
    SELECT * FROM ConfidentialRecords;
END;
GO
 
-- Encryption: Hide procedure source code
CREATE PROCEDURE dbo.SecretAlgorithm
WITH ENCRYPTION
AS
BEGIN
    -- Source will not be visible in system views
    SELECT 'Secret calculation' AS Result;
END;
GO
 
-- Recompile: Generate fresh plan on each execution
CREATE PROCEDURE dbo.DynamicReport
WITH RECOMPILE
AS
BEGIN
    -- Good for procedures with highly variable data distributions
    SELECT * FROM SalesData WHERE Region = 'Dynamic';
END;
GO
 
-- Native compilation (In-Memory OLTP)
CREATE PROCEDURE dbo.FastInsert
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, 
                   LANGUAGE = N'English')
    INSERT INTO dbo.InMemoryTable (Col1, Col2)
    VALUES (1, 2);
END;
GO

SECURITY DEFINER Risks

Documentation Practices

Well-documented procedures are maintainable procedures. Professional database development requires consistent documentation standards.

Essential documentation elements:

Purpose — What the procedure does
Parameters — Description of each input/output
Returns — What results or outputs to expect
Dependencies — Tables, other procedures, external systems
Examples — How to call the procedure
Change History — Who changed what and when
Notes — Performance considerations, edge cases, warnings

documentation_example.sql

-- ===========================================================================
-- Procedure: Sales.ProcessRefund
-- ===========================================================================
-- Purpose:   Processes a refund for a completed order. Restores inventory,
--            creates a refund transaction record, and updates order status.
--
-- Parameters:
--   @OrderID        INT           - The order to refund (required)
--   @RefundReason   NVARCHAR(500) - Reason for refund (required)
--   @PartialAmount  DECIMAL(18,2) - Partial refund amount; NULL for full refund
--   @RefundID       INT OUTPUT    - Returns the ID of the created refund record
--   @Success        BIT OUTPUT    - 1 if successful, 0 if failed
--   @ErrorMessage   NVARCHAR(500) OUTPUT - Error description if failed
--
-- Returns:    Outputs populated, no result set
--
-- Dependencies:
--   - Tables: Sales.Orders, Sales.OrderLines, Sales.Refunds, Inventory.Stock
--   - Procedures: Inventory.RestoreStock
--
-- Example:
--   DECLARE @rid INT, @ok BIT, @err NVARCHAR(500);
--   EXEC Sales.ProcessRefund 
--       @OrderID = 12345,
--       @RefundReason = 'Customer request - item not as described',
--       @PartialAmount = NULL,  -- Full refund
--       @RefundID = @rid OUTPUT,
--       @Success = @ok OUTPUT,
--       @ErrorMessage = @err OUTPUT;
--   SELECT @rid AS RefundID, @ok AS Success, @err AS Error;
--
-- Change History:
--   2024-01-15  Smith, J.   Initial creation
--   2024-03-22  Doe, A.     Added partial refund support
--   2024-06-10  Smith, J.   Fixed inventory restoration for multi-line orders
--
-- Notes:
--   - Full refunds automatically restore all inventory
--   - Partial refunds do NOT restore inventory (manual process)
--   - Order must be in 'Completed' or 'Shipped' status
--   - Creates audit trail in Sales.RefundAudit
-- ===========================================================================
CREATE OR ALTER PROCEDURE Sales.ProcessRefund
    @OrderID        INT,
    @RefundReason   NVARCHAR(500),
    @PartialAmount  DECIMAL(18,2) = NULL,
    @RefundID       INT OUTPUT,
    @Success        BIT OUTPUT,
    @ErrorMessage   NVARCHAR(500) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Implementation...
    
END;
GO

Database Comments

Beyond inline comments, use database-level comments (COMMENT ON in PostgreSQL, sp_addextendedproperty in SQL Server). These are queryable and can integrate with documentation systems.

Common CREATE PROCEDURE Mistakes

Avoid these common pitfalls when creating stored procedures:

Mistakes to Avoid

•Missing SET NOCOUNT ON (SQL Server) — Without it, row count messages are sent to client after each statement, causing overhead and potentially confusing ORM/driver logic
•Using reserved procedure prefixes — sp_ in SQL Server causes the engine to search master database first, adding lookup overhead
•No error handling — Procedures without TRY/CATCH or EXCEPTION blocks fail ungracefully, leaving transactions half-complete
•Hardcoded values — Magic numbers and strings should be parameters or configuration-based
•No schema qualification — SELECT * FROM Orders may resolve to wrong schema; always qualify: Sales.Orders
•Missing transaction control — Multi-statement operations without explicit transactions risk partial updates
•**Over-permissive SELECT *** — Returns unnecessary columns, breaks when schema changes; list specific columns
•No validation of inputs — Trusting all parameter values without validation risks data integrity issues

common_mistakes.sql

SQL Server

-- ❌ BAD: Multiple common mistakes
CREATE PROCEDURE sp_ProcessOrder  -- Reserved prefix
    @id int                        -- Cryptic name
AS
    SELECT * FROM Orders WHERE OrderID = @id  -- No schema, SELECT *
    UPDATE Orders SET Status = 'Done' WHERE OrderID = @id  -- No transaction
GO
 
-- ✅ GOOD: Professional implementation
CREATE PROCEDURE Sales.ProcessOrder
    @OrderID INT,
    @Success BIT OUTPUT,
    @ErrorMessage NVARCHAR(500) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;  -- Suppress row counts
    
    -- Validate input
    IF @OrderID IS NULL OR @OrderID <= 0
    BEGIN
        SET @Success = 0;
        SET @ErrorMessage = 'Invalid OrderID parameter';
        RETURN;
    END
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Check order exists
        IF NOT EXISTS (SELECT 1 FROM Sales.Orders WHERE OrderID = @OrderID)
        BEGIN
            SET @Success = 0;
            SET @ErrorMessage = 'Order not found: ' + CAST(@OrderID AS NVARCHAR);
            RETURN;
        END
        
        -- Update with specific columns
        UPDATE Sales.Orders 
        SET Status = 'Processed',
            ProcessedDate = GETDATE(),
            ProcessedBy = SYSTEM_USER
        WHERE OrderID = @OrderID;
        
        COMMIT TRANSACTION;
        SET @Success = 1;
        SET @ErrorMessage = NULL;
        
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        SET @Success = 0;
        SET @ErrorMessage = ERROR_MESSAGE();
    END CATCH
END;
GO

Summary: Creating Professional Procedures

We've explored the CREATE PROCEDURE statement in depth. Let's consolidate the key takeaways:

Key Takeaways

•Syntax varies by database — Core concepts are universal, but T-SQL, PL/pgSQL, MySQL's PSM, and PL/SQL have distinct syntax requirements.
•Naming matters — Use descriptive names with consistent conventions; leverage schemas for organization over prefixes.
•Choose the right modification approach — CREATE OR REPLACE/ALTER preserves permissions; DROP+CREATE loses them.
•Structure procedures consistently — Declaration, execution, and exception sections create maintainable code.
•Handle MySQL delimiters explicitly — Use DELIMITER to avoid parsing conflicts.
•Understand procedure attributes — Security context, determinism, and other options affect behavior significantly.
•Document thoroughly — Header comments with purpose, parameters, examples, and change history are mandatory for professional work.
•Avoid common mistakes — SET NOCOUNT ON, schema qualification, error handling, and input validation.

What's next:

Page Complete