SQL STORED PROCEDURES

SQL
POSTGRES
INTERACTIVE

Complete SQL STORED PROCEDURE Technical Guide

1. Fundamental Syntax 
CREATE [OR REPLACE] PROCEDURE procedure_name (
    [parameter1 datatype [IN|OUT|INOUT],
    [parameter2 datatype [IN|OUT|INOUT],
    ...
)
[LANGUAGE language_name]
AS $$
    -- Procedure body
    -- SQL statements
    -- Control structures
$$;
  1. Parameters: Input (IN), output (OUT), or bidirectional (INOUT)
  2. Language support: SQL, PL/pgSQL, T-SQL, PL/SQL, etc.
  3. Replacement: OR REPLACE updates existing procedures
  4. Delimiters: $$ or other delimiters for procedure body
2. Parameter Handling
Basic Parameter Types
-- PostgreSQL example
CREATE PROCEDURE transfer_funds(
    sender_id INT,
    recipient_id INT,
    amount DECIMAL(10,2),
    OUT status TEXT
) AS $$
BEGIN
    -- Implementation
END;
$$ LANGUAGE plpgsql;
Default Values
-- SQL Server example
CREATE PROCEDURE get_orders(
    @customer_id INT,
    @start_date DATE = NULL,
    @max_results INT = 100
) AS
BEGIN
    -- Implementation
END;
3. Control Flow & Logic
Conditional Logic
-- MySQL example
CREATE PROCEDURE apply_discount(IN product_id INT)
BEGIN
    DECLARE price DECIMAL(10,2);
    DECLARE discount DECIMAL(3,2);
    
    SELECT unit_price INTO price FROM products WHERE id = product_id;
    
    IF price > 1000 THEN
        SET discount = 0.15;
    ELSEIF price > 500 THEN
        SET discount = 0.10;
    ELSE
        SET discount = 0.05;
    END IF;
    
    UPDATE products SET discount = discount WHERE id = product_id;
END;
Loops & Cursors
-- Oracle PL/SQL example
CREATE PROCEDURE process_orders AS
    CURSOR order_cursor IS SELECT * FROM orders WHERE status = 'pending';
    order_rec order_cursor%ROWTYPE;
BEGIN
    OPEN order_cursor;
    LOOP
        FETCH order_cursor INTO order_rec;
        EXIT WHEN order_cursor%NOTFOUND;
        -- Process each order
    END LOOP;
    CLOSE order_cursor;
END;
4. Error Handling
SQL Server TRY/CATCH
CREATE PROCEDURE safe_delete (
    @order_id INT,
    @deleted BIT OUTPUT
) AS
BEGIN TRY
    DELETE FROM orders WHERE id = @order_id;
    SET @deleted = 1;
END TRY
BEGIN CATCH
    SET @deleted = 0;
    -- Log error details
    INSERT INTO error_log VALUES (
        ERROR_NUMBER(),
        ERROR_MESSAGE(),
        GETDATE()
    );
END CATCH;
PostgreSQL Exception Blocks
CREATE PROCEDURE update_inventory() AS $$
BEGIN
    -- Operations
EXCEPTION
    WHEN unique_violation THEN
        RAISE NOTICE 'Duplicate key violation';
    WHEN others THEN
        RAISE EXCEPTION 'Error: %', SQLERRM;
END;
$$ LANGUAGE plpgsql;
5. Database-Specific Features
SQL Server Table-Valued Parameters
CREATE TYPE OrderItems AS TABLE (
    product_id INT,
    quantity INT,
    price DECIMAL(10,2)
);

CREATE PROCEDURE create_order (
    @customer_id INT,
    @items OrderItems READONLY
) AS
BEGIN
    -- Process table-valued parameter
END;
Oracle Package Procedures
CREATE OR REPLACE PACKAGE order_mgmt AS
    PROCEDURE create_order(p_customer_id IN NUMBER);
    PROCEDURE cancel_order(p_order_id IN NUMBER);
    FUNCTION order_total(p_order_id IN NUMBER) RETURN NUMBER;
END order_mgmt;

CREATE OR REPLACE PACKAGE BODY order_mgmt AS
    -- Implementation
END order_mgmt;
6. Performance & Security
  1. Execution Plans: Cached plans for parameterized queries
  2. Recompilation: When and how to force plan regeneration
  3. Permission Granularity: EXECUTE vs underlying table permissions
  4. SQL Injection: Using parameters instead of dynamic SQL
Optimization Example
-- SQL Server WITH RECOMPILE option
CREATE PROCEDURE get_monthly_report
    @year INT,
    @month INT
WITH RECOMPILE AS
BEGIN
    -- Highly variable query patterns
END;

Advanced Stored Procedure Topics

1. Dynamic SQL Execution 
-- SQL Server EXECUTE with parameters
CREATE PROCEDURE search_products (
    @column_name VARCHAR(100),
    @search_value VARCHAR(100)
) AS
BEGIN
    DECLARE @sql NVARCHAR(1000);
    SET @sql = 'SELECT * FROM products WHERE ' + 
               QUOTENAME(@column_name) + ' = @value';
    
    EXEC sp_executesql @sql, 
         N'@value VARCHAR(100)', 
         @value = @search_value;
END;

-- PostgreSQL EXECUTE format
CREATE PROCEDURE dynamic_query(table_name TEXT) AS $$
BEGIN
    EXECUTE format('SELECT * FROM %I LIMIT 100', table_name);
END;
$$ LANGUAGE plpgsql;
  1. QUOTENAME/format(): Safe object name quoting to prevent injection
  2. sp_executesql: Parameterized dynamic SQL in SQL Server
  3. Performance impact: Dynamic SQL can't reuse execution plans
  4. Security audit: Special logging for dynamic SQL execution
2. Temporary Table Patterns 
-- SQL Server temp tables
CREATE PROCEDURE calculate_totals AS
BEGIN
    CREATE TABLE #OrderTotals (
        customer_id INT,
        total_amount DECIMAL(18,2)
    );
    
    INSERT INTO #OrderTotals
    SELECT customer_id, SUM(amount)
    FROM orders
    GROUP BY customer_id;
    
    -- Further processing
END;

-- PostgreSQL WITH clauses
CREATE PROCEDURE analyze_data() AS $$
BEGIN
    WITH intermediate AS (
        SELECT product_id, AVG(price) as avg_price
        FROM prices
        GROUP BY product_id
    )
    UPDATE products p
    SET last_avg_price = i.avg_price
    FROM intermediate i
    WHERE p.id = i.product_id;
END;
$$ LANGUAGE plpgsql;
  1. Session scope: Temp tables visible only to current connection
  2. Indexing: Adding indexes to temp tables for complex processing
  3. Table variables: Alternative with different performance characteristics
  4. CTEs vs temp tables: Choosing based on complexity and reuse
3. Procedure Composition 
-- Oracle nested procedure example
CREATE PROCEDURE process_order(order_id NUMBER) AS
    PROCEDURE validate_order(oid NUMBER) IS
    BEGIN
        -- Validation logic
    END;
    
    PROCEDURE charge_payment(oid NUMBER) IS
    BEGIN
        -- Payment processing
    END;
BEGIN
    validate_order(order_id);
    charge_payment(order_id);
    -- Main logic
END;

-- SQL Server procedure calling
CREATE PROCEDURE monthly_report AS
BEGIN
    EXEC prepare_report_data;
    EXEC generate_report_tables;
    EXEC send_report_emails;
END;
  1. Encapsulation: Breaking complex logic into modular units
  2. Variable scope: Outer procedure variables visible to inner procedures
  3. Transaction flow: Managing transactions across nested calls
  4. Debugging: Stepping through nested procedure execution
4. Debugging & Diagnostics 
-- SQL Server debugging tools
CREATE PROCEDURE complex_calculation AS
BEGIN
    DECLARE @debug INT = 1;
    
    -- Step 1
    IF @debug = 1 PRINT 'Starting calculation';
    -- ...
    
    -- Breakpoint in SSMS
    SELECT * FROM intermediate_data; -- Inspect mid-procedure
    
    -- Log to table
    INSERT INTO execution_log VALUES ('Step completed', GETDATE());
END;

-- PostgreSQL RAISE NOTICE
CREATE PROCEDURE validate_data() AS $$
DECLARE
    row_count INT;
BEGIN
    RAISE NOTICE 'Validation started at %', NOW();
    SELECT COUNT(*) INTO row_count FROM raw_data;
    RAISE NOTICE 'Processing % rows', row_count;
END;
$$ LANGUAGE plpgsql;
  1. IDE debugging: SSMS, PL/SQL Developer, pgAdmin breakpoints
  2. Diagnostic output: PRINT, RAISE NOTICE, DBMS_OUTPUT
  3. Timing metrics: Measuring procedure execution duration
  4. Error context: Capturing line numbers and call stacks
5. CLR & External Language Integration 
// SQL Server CLR Procedure (C#)
[Microsoft.SqlServer.Server.SqlProcedure]
public static void RegexMatch(SqlString input, SqlString pattern, out SqlBoolean result)
{
    result = System.Text.RegularExpressions.Regex.IsMatch(input.Value, pattern.Value);
}

-- Registering the CLR procedure
CREATE ASSEMBLY RegexFunctions FROM 'path\to\assembly.dll';
CREATE PROCEDURE clr_regex_match(
    @input NVARCHAR(MAX),
    @pattern NVARCHAR(100),
    @result BIT OUTPUT
)
AS EXTERNAL NAME RegexFunctions.UserDefinedFunctions.RegexMatch;

-- PostgreSQL PL/Python
CREATE PROCEDURE analyze_sentiment(text_content TEXT) AS $$
import nltk
from nltk.sentiment import SentimentIntensityAnalyzer
sid = SentimentIntensityAnalyzer()
return sid.polarity_scores(text_content)
$$ LANGUAGE plpython3u;
  1. Performance tradeoffs: CLR vs native SQL for complex logic
  2. Security permissions: SAFE, EXTERNAL_ACCESS, UNSAFE modes
  3. Language options: Python, R, Java, .NET integrations
  4. Debugging: Testing CLR code outside database context
6. Dependency Management 
-- SQL Server dependency queries
SELECT referencing_schema_name, referencing_entity_name
FROM sys.dm_sql_referencing_entities('dbo.orders', 'OBJECT');

-- PostgreSQL dependency tracking
SELECT pg_describe_object(classid, objid, 0) AS dependent_object
FROM pg_depend
WHERE refobjid = 'public.orders'::regclass::oid;

-- Oracle dependency graph
SELECT name, type, referenced_name, referenced_type
FROM user_dependencies
WHERE referenced_name = 'ORDERS';
  1. Impact analysis: Understanding effects of schema changes
  2. Version control: Tracking procedure changes with schema
  3. Deployment planning: Ordering procedure creation/updates
  4. Circular references: Breaking dependency cycles