LuisLlamas Logo
procedimientos-almacenados-en-sql

Stored Procedures in SQL

  • 5 min
  • Advanced

A Stored Procedure (often called SP or Stored Proc) is a set of T-SQL instructions saved in the database under a name.

They are the foundation of database development. Think of them as the functions or methods in languages like Java or Python, but living inside SQL Server.

Why use a stored procedure?

  1. Code Reuse: Write the logic once, execute it a thousand times.
  2. Security: You can grant a user permission to execute the procedure without giving them permission to see the underlying tables.
  3. Performance: SQL Server compiles and optimizes the execution plan the first time it runs and reuses it afterwards.
  4. Reduce Network Traffic: Instead of sending 500 lines of code from your application to the server, you only send EXEC MyProcedure.

Creating a Procedure: CREATE PROCEDURE

To create a procedure, we use the CREATE PROCEDURE statement (or its abbreviation CREATE PROC), followed by the name, parameters (if any), and the code body.

CREATE PROCEDURE ProcedureName
AS
BEGIN
    -- Your T-SQL code goes here
    SELECT * FROM MyTable;
END
Copied!
  • ProcedureName: The unique name that identifies the stored procedure.
  • AS: Indicates the start of the code block containing the SQL instructions.
  • BEGIN…END: Delimits the body of the procedure.

For example, let’s create a procedure that returns the list of all active products.

CREATE PROCEDURE usp_GetActiveProducts
AS
BEGIN
    SET NOCOUNT ON; -- Good practice (we'll see why below)

    SELECT ProductID, Name, Price
    FROM Products
    WHERE Active = 1;
END
Copied!

Never name your procedures starting with sp_ (e.g., sp_GetCustomers). That prefix is reserved for System Procedures from Microsoft.

If you use sp_, SQL Server will first look in the system master database, which hurts performance and can cause conflicts. Use prefixes like usp_ (User Stored Procedure) or proc_.

Executing a Procedure: EXEC

Once created, the procedure lives in the database. To invoke it, we use the EXEC keyword.

In our example we would do,

EXEC usp_GetActiveProducts;
Copied!

Procedures with Parameters

A procedure that always does exactly the same thing is not very useful. The real power comes when we pass it data to work with dynamically.

Parameters are defined after the procedure name, starting with @ and their data type.

For example, if we want to filter by category,

CREATE PROCEDURE usp_ProductsByCategory
    @CategoryID INT -- Input parameter
AS
BEGIN
    SET NOCOUNT ON;

    SELECT Name, Price, Stock
    FROM Products
    WHERE CategoryID = @CategoryID; -- We use the parameter in the WHERE clause
END
Copied!

To execute it, we must pass the value:

-- Option 1: By position
EXEC usp_ProductsByCategory 5;

-- Option 2: By name (Safer and more readable)
EXEC usp_ProductsByCategory @CategoryID = 5;
Copied!

Output Parameters

In addition to input parameters, stored procedures can return values via output parameters. These parameters are defined with the OUTPUT keyword.

Let’s create a procedure that counts the number of customers in a city and returns that value:

CREATE PROCEDURE CountCustomersByCity
    @City NVARCHAR(50),
    @TotalCustomers INT OUTPUT
AS
BEGIN
    SELECT @TotalCustomers = COUNT(*) FROM Customers WHERE City = @City;
END;
Copied!

To execute this procedure and get the output value, we do the following:

DECLARE @Total INT;
EXEC CountCustomersByCity @City = 'Madrid', @TotalCustomers = @Total OUTPUT;
PRINT 'Total customers in Madrid: ' + CAST(@Total AS NVARCHAR);
Copied!

Parameters with Default Values

Sometimes we want a parameter to be optional. If the user doesn’t send it, we will use a default value. This is achieved by assigning a value with = in the definition.

CREATE PROCEDURE usp_SearchCustomers
    @City NVARCHAR(50) = 'Madrid' -- If nothing is said, it will be Madrid
AS
BEGIN
    SET NOCOUNT ON;

    SELECT FirstName, LastName, Email
    FROM Customers
    WHERE City = @City;
END
Copied!

Now we can call it in two ways:

EXEC usp_SearchCustomers;              -- Searches in 'Madrid' (Default)
EXEC usp_SearchCustomers @City = 'Valencia'; -- Searches in 'Valencia'
Copied!

The Importance of SET NOCOUNT ON

You may have noticed that in the examples I include SET NOCOUNT ON;.

By default, every time you execute a SQL statement, the server returns a message: (X rows affected). In a complex procedure with many steps, these messages generate “noise” on the network and can confuse some client applications that expect only data, not informational messages.

SET NOCOUNT ON turns off these messages, slightly improving performance and cleaning up the output.

Modifying and Deleting: ALTER and DROP

Of course, we can modify or delete a procedure.

  • To delete it: DROP PROCEDURE usp_Name;
  • To modify it: ALTER PROCEDURE usp_Name ...

Logically, if you delete or modify a procedure, you will affect all queries that use it.

You will have to analyze the effects before modifying it.

On this page