Stored Procedures

Executable Database Objects for Business Logic

Introduction

Stored procedure: precompiled SQL statements. Executed server-side. Parameters: accept input, return output. Business logic: encapsulated in database. Performance: faster (pre-compiled), security: application doesn't see SQL.

"Stored procedures encapsulate business logic: complex operations, transactions, validation. Server-side execution: performance and security benefits. Centralized: easier maintenance." -- Database programming

Definition

Concept

Named database object: contains SQL and procedural statements. Stored: in database (not application). Compiled: once (reused). Parameterized: accepts inputs, returns outputs.

Components

Declaration: parameters (IN, OUT, INOUT). Logic: SQL statements, control flow. Transactions: atomic execution. Error handling: TRY/CATCH (DBMS-dependent).

Execution Model

Call: CALL procedure_name(args) or EXEC procedure_name args. Returns: result set or output parameters. Transaction: atomic (all or none).

Benefits

Performance

Pre-compiled: no parsing overhead. Network: single call (not multiple queries). Efficiency: optimized by database engine.

Security

SQL injection: prevented (parameters bound). Permission: grant procedure access (not table access). Sensitive logic: protected (not in application).

Reusability

Multiple applications: call same procedure. Consistency: business logic centralized. Maintenance: single place to update.

Atomicity

Multi-step operations: atomic. All succeed or all fail. Transactions: automatic. Consistency: guaranteed.

Syntax and Structure

Basic Syntax

CREATE PROCEDURE procedure_name [parameters]ASBEGIN -- SQL and control flow statementsEND;

Example

CREATE PROCEDURE get_employee_salary @emp_id INT, @salary OUT DECIMAL(10, 2)ASBEGIN SELECT @salary = salary FROM employees WHERE emp_id = @emp_id;END;

Modification

ALTER PROCEDURE procedure_name -- New definitionDROP PROCEDURE procedure_name;

Parameters

Input Parameters

IN: pass value to procedure. Example: emp_id (find employee). Default: direction=IN.

Output Parameters

OUT: return value to caller. Example: salary (result). Populated: in procedure (using SET or SELECT INTO).

Input/Output

INOUT: both (pass and return). Example: quantity (increment). Flexible: modify and return.

Example

CREATE PROCEDURE transfer_funds @from_account INT, @to_account INT, @amount DECIMAL(10, 2), @success OUT BITASBEGIN -- Debit from_account, credit to_account -- SET @success = 1 if success, 0 if failEND;

Control Flow

Conditional

IF condition BEGIN -- statements ENDELSE BEGIN -- alternative END;

Loops

WHILE conditionBEGIN -- statements -- BREAK, CONTINUEEND;

Error Handling

BEGIN TRY -- statementsEND TRYBEGIN CATCH -- error handling SELECT ERROR_MESSAGE()END CATCH;

Transactions

BEGIN TRANSACTION -- DML statements IF @@ERROR <> 0 ROLLBACK ELSE COMMIT

Execution

CALL Syntax

CALL procedure_name(arg1, arg2, @output_param);SELECT @output_param; -- get result

EXEC (SQL Server)

EXEC procedure_name @param1 = value1, @param2 = value2;

Result Sets

SELECT: returns rows (result set). Caller: processes rows. Multiple: SELECT statements possible (multiple result sets).

Performance

Advantages

Pre-compiled: no parsing. Network: reduced (single call). Caching: execution plan cached. Indexes: optimized queries.

Disadvantages

Overhead: compilation (if not cached). Version control: harder (stored in database). Testing: requires database access.

Optimization

Indexes: maintain relevant indexes. Statistics: keep up-to-date. Monitoring: profile slow procedures. Rewrite: if necessary.

Security Considerations

SQL Injection

Parameters: protected (bound values). String building: risky (use parameters). Dynamic SQL: EXEC sp_executesql (parameterized).

Permissions

GRANT EXECUTE: users call procedure (not access tables). Granular: delegate access carefully. Audit: track procedure calls.

Best Practices

Parameters: always use. Validation: input checks. Error handling: proper (don't expose internals). Logging: audit trail.

Examples

Simple Lookup

CREATE PROCEDURE GetEmployeeName @emp_id INT, @name OUT VARCHAR(100)ASBEGIN SELECT @name = name FROM employees WHERE emp_id = @emp_id;END;

Data Modification

CREATE PROCEDURE InsertEmployee @name VARCHAR(100), @salary DECIMAL(10, 2), @dept_id INT, @new_id OUT INTASBEGIN BEGIN TRANSACTION INSERT INTO employees (name, salary, dept_id) VALUES (@name, @salary, @dept_id); SET @new_id = SCOPE_IDENTITY(); COMMIT;END;

Complex Logic

CREATE PROCEDURE BonusPayroll @dept_id INT, @bonus_percent DECIMAL(5, 2)ASBEGIN UPDATE employees SET salary = salary * (1 + @bonus_percent / 100) WHERE dept_id = @dept_id AND status = 'active'; DECLARE @updated INT = @@ROWCOUNT; RETURN @updated;END;

References

  • Ramakrishnan, R., and Gehrke, J. "Database Management Systems." McGraw-Hill, 3rd edition, 2003.