SQL Server Interview Question #106

What is a stored procedure?

Views, Stored Procedures & User-Defined Functions Mid-Level Intermediate

Detailed Explanation

A stored procedure is a named T-SQL program stored in the database. It can accept input parameters, return output parameters and result sets, execute multiple SQL statements, manage transactions, and contain procedural logic.

Stored procedures are commonly used to centralize data-access operations, implement complex database workflows, reduce repeated SQL text, and expose controlled database operations to applications.

Code Example

CREATE PROCEDURE dbo.GetProductById
    @Id INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT Id, Name, Price
    FROM dbo.Products
    WHERE Id = @Id;
END;
GO

EXEC dbo.GetProductById @Id = 10;