Stored procedures: encapsulate an operation with parameters

When a query must run from several applications or combine validations, reads, and changes, a procedure gives it a name and a stable interface. The interface is the contract of parameters, results, and behavior known by its callers.

A procedure can receive parameters, execute multiple statements, and return one or more result sets. It’s not just a saved query: it can also coordinate an entire operation.

Create or update the procedure

CREATE OR ALTER PROCEDURE dbo.usp_ProductosPorCategoria
    @IdCategoria SMALLINT,
    @SoloActivos BIT = 1
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        p.IdProducto,
        p.Nombre,
        p.Precio,
        p.Existencia
    FROM dbo.Producto AS p
    WHERE p.IdCategoria = @IdCategoria
      AND (@SoloActivos = 0 OR p.Activo = 1)
    ORDER BY p.Nombre;
END;
GO

CREATE OR ALTER lets the same script create the object when absent or modify it when present. The statement must start its batch; GO is the separator used by the client tool.

The @SoloActivos parameter has a default value. The caller can ignore it or send another value explicitly.

Run it

EXEC dbo.usp_ProductosPorCategoria
    @IdCategoria = 2;

EXEC dbo.usp_ProductosPorCategoria
    @IdCategoria = 2,
    @SoloActivos = 0;

Naming the parameters makes the call more readable and less dependent on its position.

Output parameters

An OUTPUT parameter returns one value in addition to result sets:

CREATE OR ALTER PROCEDURE dbo.usp_ContarProductosActivos
    @IdCategoria SMALLINT,
    @Cantidad INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT @Cantidad = COUNT(*)
    FROM dbo.Producto
    WHERE IdCategoria = @IdCategoria
      AND Activo = 1;
END;
GO

DECLARE @Total INT;

EXEC dbo.usp_ContarProductosActivos
    @IdCategoria = 2,
    @Cantidad = @Total OUTPUT;

SELECT @Total AS ProductosActivos;

The word OUTPUT is declared in the procedure and repeated in the call. If it is missing from the call, the outer variable does not receive the value.

Result, output and return

A SELECT returns rows. An OUTPUT parameter returns a value through the procedure interface. RETURN returns an integer usually reserved for execution status, not for transporting business data.

The application consuming the procedure must know what sets and parameters it expects. Changing column names, types, or order can break consumers even if the procedure continues to compile.

What should be checked

We validate types, default values, permissions, and error behavior. Dynamic SQL is SQL code built during execution. We avoid forming it by concatenating received text; when it is essential, we parameterize execution, sending values separately from the query text.

Optional filters can produce execution plans—the strategies chosen by SQL Server—that suit some values and perform poorly for others. We measure with representative data before applying solutions such as recompilation, which creates a new plan for the current execution, or dynamic SQL.