How to create a stored procedure with parameters in SQL Server

Joao Barros 06 de July de 2026 5 min read

A stored procedure is a block of T-SQL saved in the database that you can run whenever you need it, and parameters make it flexible: you pass different values and get different results without rewriting the query. Knowing how to create a stored procedure with parameters in SQL Server helps you centralise logic, reduce repetition and protect your queries against SQL injection. On top of that, SQL Server caches the procedure's execution plan, which can improve performance when you run it many times.

Prerequisites

A SQL Server instance (the free SQL Server Express works fine).
SQL Server Management Studio (SSMS) or Azure Data Studio to run the queries.
A database where you can create objects and the CREATE PROCEDURE permission.
Basic knowledge of SELECT and WHERE.

Step 1: Create a sample table

To test, we need some data. Create a simple sales table and insert a few rows. The values are fictional and are there only so you can see the procedure returning results. Run this code on your test database:

CREATE TABLE dbo.Vendas (
    VendaID     INT IDENTITY(1,1) PRIMARY KEY,
    Cliente     NVARCHAR(100),
    Pais        NVARCHAR(50),
    Valor       DECIMAL(10,2),
    DataVenda   DATE
);

INSERT INTO dbo.Vendas (Cliente, Pais, Valor, DataVenda) VALUES
('Ana Silva',    'Portugal', 120.50, '2026-01-15'),
('Joao Costa',   'Portugal', 340.00, '2026-02-03'),
('Marta Ruiz',   'Espanha',   85.90, '2026-02-20'),
('Pedro Alves',  'Portugal', 210.00, '2026-03-11');

Step 2: Create the stored procedure with one parameter

Now create the procedure. The input parameter starts with @ and has a data type, just like a column. This procedure returns the sales for a country of your choice:

CREATE PROCEDURE dbo.ObterVendasPorPais
    @Pais NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT Cliente, Valor, DataVenda
    FROM dbo.Vendas
    WHERE Pais = @Pais
    ORDER BY DataVenda;
END;

SET NOCOUNT ON avoids unnecessary "rows affected" messages and makes the procedure slightly faster. @Pais behaves like a variable inside the procedure. Give the parameter the same data type as the column you are comparing (here, NVARCHAR(50)); mismatched types force SQL Server into conversions that can slow the query down.

Step 3: Execute the stored procedure

To run the procedure you use EXEC followed by the parameter value. You can pass the value positionally or, better still, by the parameter name (more readable):

-- Forma posicional
EXEC dbo.ObterVendasPorPais 'Portugal';

-- Forma nomeada (recomendada)
EXEC dbo.ObterVendasPorPais @Pais = 'Espanha';

Each call returns only the rows for the given country. You changed the result without touching the query: that is the power of parameters.

Security tip: passing values as parameters, instead of concatenating them directly into the query text, is the correct way to avoid SQL injection. The procedure always treats the value as data, never as code.

Step 4: Multiple parameters and a default value

You can have as many parameters as you want. A parameter with a default value becomes optional: if you do not pass it, it uses the defined value. Here we add a minimum amount that defaults to 0. Use CREATE OR ALTER to update the procedure without dropping it first:

CREATE OR ALTER PROCEDURE dbo.ObterVendasPorPais
    @Pais        NVARCHAR(50),
    @ValorMinimo DECIMAL(10,2) = 0
AS
BEGIN
    SET NOCOUNT ON;

    SELECT Cliente, Valor, DataVenda
    FROM dbo.Vendas
    WHERE Pais = @Pais
      AND Valor >= @ValorMinimo
    ORDER BY Valor DESC;
END;

Now you have two ways to call the procedure:

-- Usa o valor por omissao (@ValorMinimo = 0)
EXEC dbo.ObterVendasPorPais @Pais = 'Portugal';

-- Passa os dois parametros
EXEC dbo.ObterVendasPorPais @Pais = 'Portugal', @ValorMinimo = 200;

Step 5: Return a value with an OUTPUT parameter

Sometimes you want to return a single value, such as a total or a count, instead of a table. For that, use an OUTPUT parameter: the value you assign to it inside the procedure becomes available to the caller.

CREATE OR ALTER PROCEDURE dbo.ContarVendasPorPais
    @Pais    NVARCHAR(50),
    @Total   INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT @Total = COUNT(*)
    FROM dbo.Vendas
    WHERE Pais = @Pais;
END;

To run it, declare a variable that will receive the result and mark it with OUTPUT in the call as well:

DECLARE @n INT;
EXEC dbo.ContarVendasPorPais @Pais = 'Portugal', @Total = @n OUTPUT;
PRINT 'Vendas em Portugal: ' + CAST(@n AS NVARCHAR(10));

Check the result

Confirm the procedure exists by querying the sys.procedures system view:

SELECT name, create_date, modify_date
FROM sys.procedures
WHERE name IN ('ObterVendasPorPais', 'ContarVendasPorPais');

In SSMS you can also see it in Object Explorer, inside the database, under Programmability > Stored Procedures. If running EXEC dbo.ObterVendasPorPais @Pais = 'Portugal' returns the expected rows, everything is working.

Conclusion

You now know how to create a stored procedure with one or several parameters, define default values and return results with an OUTPUT parameter. Because the logic lives in a single place, you can reuse it from applications, reports or other procedures without copying code. The natural next step is to make the procedure more robust with error handling using TRY...CATCH and transactions, so that write operations stay consistent. Which parameter will you add first to your next procedure: a start date or an amount limit?