SQL Server 2000 Stored Procedures — Storage and Invocation Which statement best describes how stored procedures are managed and used in SQL Server?

Introduction:
Stored procedures encapsulate SQL and control logic at the database tier. Understanding where they live and how applications call them is foundational for architecture, performance, and security.

Given Data / Assumptions:

• Target platform: SQL Server 2000 (behavior conceptually similar today).
• Focus: storage location and invocation model for stored procedures.

Concept / Approach:
In SQL Server, stored procedures are database objects. They are authored and stored in the database catalog and executed by clients via RPC-style calls or EXEC statements. They are compiled to execution plans which can be reused from cache.

Step-by-Step Solution:
1) Evaluate statements that match SQL Server's object model.2) Recognize that procedures are not stored on client machines by requirement.3) Note that BEFORE/INSTEAD OF/AFTER are trigger types, not procedure types.4) Select the statement consistent with database-resident procedures callable by applications.

Verification / Alternative check:
Check system catalog views (e.g., sys.objects). Stored procedures appear as database objects and are invoked using EXEC or through application frameworks.

Why Other Options Are Wrong:

• Never kept in database: False; they are stored in the database.
• Stored on users' computers: Not required or typical.
• Support BEFORE/INSTEAD OF/AFTER types: Those are trigger types, not procedure types.
• Only scripts, not compiled/cached: SQL Server caches execution plans for procedures.

Common Pitfalls:
Mixing up triggers with stored procedures and assuming code must live on clients.

Final Answer:
Stored procedures are kept inside the database and can be invoked by application programs.