Explain the difference between Stored Procedures and Functions in SQL Server.

Stored procedures and functions both encapsulate reusable logic, but they serve different purposes and behave differently inside SQL Server.

Stored procedures:

• Primarily designed to perform actions: DML operations, transaction control, administrative tasks.
• Can return multiple result sets and output parameters.
• Cannot be directly used in SELECT, WHERE, or JOIN clauses.
• Commonly used as API endpoints from the application layer.

Functions:

• Intended for computation and value-returning logic.
• Scalar functions return a single value; table-valued functions return a rowset.
• Can be used inside SELECT, WHERE, and JOIN clauses like expressions or tables.
• Have tighter restrictions: no explicit transaction control; most cannot perform data-modifying side effects.

In short: procedures orchestrate operations, while functions compute values that integrate with queries.