Stored Procedures

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]
AS
BEGIN
 -- SQL and control flow statements
END;

Example

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

Modification

ALTER PROCEDURE procedure_name
 -- New definition

DROP 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 BIT
AS
BEGIN
 -- Debit from_account, credit to_account
 -- SET @success = 1 if success, 0 if fail
END;

Control Flow

Conditional

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

Loops

WHILE condition
BEGIN
 -- statements
 -- BREAK, CONTINUE
END;

Error Handling

BEGIN TRY
 -- statements
END TRY
BEGIN 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)
AS
BEGIN
 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 INT
AS
BEGIN
 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)
AS
BEGIN
 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.