SQL Server Interview Question #108

What are input parameters in a stored procedure?

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

Detailed Explanation

Input parameters allow callers to supply values to a stored procedure. They make procedures reusable and avoid embedding user values directly into SQL text.

Proper parameterization is important for security and can help SQL Server reuse execution plans. Parameters should use data types and lengths compatible with the columns they are compared against.

Code Example

CREATE PROCEDURE dbo.GetProductsByCategory
    @CategoryId INT,
    @MinimumPrice DECIMAL(18,2) = 0
AS
BEGIN
    SET NOCOUNT ON;

    SELECT Id, Name, Price
    FROM dbo.Products
    WHERE CategoryId = @CategoryId
      AND Price >= @MinimumPrice;
END;