Explain the difference between stored procedures and functions in T-SQL. When would you use each?

**Stored Procedures**:

- Can perform DML operations (INSERT, UPDATE, DELETE)

- Can return multiple result sets

- Can have OUTPUT parameters

- Can call other procedures and functions

- Cannot be used in SELECT statements

- Can use TRY/CATCH for error handling

- Can modify database state

**Functions**:

- Scalar functions return single value, table-valued functions return table

- Cannot perform DML operations (read-only)

- Must return a value (RETURN statement)

- Cannot have OUTPUT parameters

- Can be used in SELECT statements and WHERE clauses

- Cannot use TRY/CATCH (must use ISNULL/COALESCE for error handling)

- Cannot modify database state

**When to use Procedures**:

- Complex business logic with multiple steps

- Operations that modify data (INSERT, UPDATE, DELETE)

- Need to return multiple result sets

- Need error handling with TRY/CATCH

- Example: ProcessOrder procedure that validates inventory, creates order, updates stock

**When to use Functions**:

- Reusable calculations (scalar functions)

- Reusable filtered views (inline table-valued functions)

- Need to use in SELECT or WHERE clauses

- Read-only operations

- Example: CalculateDiscount function used in SELECT, GetCustomerOrders table-valued function

**Performance consideration**: Inline table-valued functions are expanded into query plan (like views), enabling optimization. Multi-statement table-valued functions are materialized (like temp tables), preventing optimization. Scalar functions in WHERE clauses prevent index usage.

**Real-world example**: Salesforce uses stored procedures for CRM operations (create lead, update opportunity) and functions for calculations (discount amount, commission rate).

What is the difference between table variables and temp tables? When should you use each?

**Table Variables (@table)**:

- Declared with DECLARE @table TABLE (...)

- Scoped to batch/procedure (cannot be used in nested procedures)

- No statistics maintained (optimizer always estimates 1 row)

- No logging for DML operations (faster for small datasets)

- Cannot add indexes after creation (must define in DECLARE)

- Cannot use ALTER TABLE

- Automatically cleaned up at end of scope