Create a stored procedure with SQL Server

A stored procedure is a block of SQL statements that can be saved and executed at any time.

They are particularly useful for automating repetitive or complex tasks, and allow you to gain in performance (and sometimes in network load).

Let's discover together, in a few steps, how to create and execute a stored procedure in SQL Server.

Creating a procedure

To create the procedure, you will obviously need to be connected to your SQL database.

For example in a terminal, or with SQL Server Management Studio (SSMS)

Once connected to the SQL Server instance on which you want to create the stored procedure, the script to create your procedure will look like this:

CREATE PROCEDURE <ProcedureName>
    -- Liste des paramètres
AS
BEGIN
    -- Contenu de la procédure
END;

As you can guess, to create a procedure, we will use the CREATE PROCEDURE command, then the parameters will come after its name, and finally the content will be between the BEGIN and END statements.

An example procedure

We are going to create an example procedure, in order to see the possibilities that SQL Server offers us.

The use case is of course fictional, and so is the database schema!

From a simple order number (on an e-commerce site), we will retrieve the customer information, the order information, and insert a new row into the invoices table.

Here's what it looks like:

CREATE PROCEDURE GenerateCustomerInvoice
    @OrderId INT
AS
BEGIN
    -- Déclaration de la variable intermédiaire pour stocker le ClientID
    DECLARE @CustomerId INT;
    -- Déclaration de la variable intermédiaire pour stocker le Prix total
    DECLARE @Price DECIMAL;
    -- Déclaration de la variable pour stocker l'identifiant de la facture
    DECLARE @CreatedId INT;

    -- 1. Récupérer le CustomerId de la commande
    SELECT @CustomerId = c.Id
    FROM Customer c
    INNER JOIN Order o
	    ON o.CustomerId = c.Id
    WHERE o.Id = @OrderId;
    
    -- 2. Récupérer le prix total de la commande
    SELECT @Price = SUM(p.Price)
    FROM Product p
    INNER JOIN Order o
	    ON p.OrderId = o.Id
    WHERE o.Id = @OrderId;

    -- Vérification : si le client n'existe pas, on arrête l'exécution
    IF @CustomerId IS NULL
    BEGIN
        RETURN;
    END

    -- 3. Insérer une nouvelle commande dans la table Commandes
    INSERT INTO Invoice(CustomerId, TotalPrice)
    VALUES (@CustomerId , @Price);
		
		-- 4. Sauvegarde de l'identifiant de la facture créé
		SET @CreatedId = SCOPE_IDENTITY();
		
		-- 5. On retourne la facture créée
    SELECT * FROM Invoice WHERE Id = @CreatedId;
    
END;

You can see that inside this procedure, we use several SQL commands together, such as SELECT, INSERT, IF, SET,…

And in addition to bringing all these commands together in a single block, this allows us to check certain conditions so that our queries execute correctly!

Be careful though, a procedure does not act as a transaction.

Executing a procedure

Once the procedure has been created, you can execute it with the EXEC command, passing it the necessary parameters:

EXEC GenerateCustomerInvoice @OrderId = 1;

This will execute the SQL statements defined in the procedure, and return the result from the procedure's final SELECT.

If needed, a procedure can be called from another procedure, and so on, exactly like a function in classic programming!

Modifying a procedure

Once your procedure is saved, if you need to modify how it works, you can use the ALTER PROCEDURE command, like this:

ALTER PROCEDURE <ProcedureName>
    -- Liste des paramètres
AS
BEGIN
    -- Contenu de la procédure
END;

You will then need to resubmit the entire code of the modified procedure!