MENU
  1. Accueil
  2. SQL
  3. Guide complet sur les paramètres des procédures stockées SQL : utilisation et meilleures pratiques

Guide complet sur les paramètres des procédures stockées SQL : utilisation et meilleures pratiques

SQL

En utilisant les paramètres des procédures stockées SQL, vous pouvez effectuer des manipulations de données efficaces et flexibles. Les procédures stockées simplifient les opérations complexes dans la base de données et sont des outils puissants pour améliorer la réutilisabilité et la maintenabilité. Dans cet article, nous allons expliquer en détail tout ce que vous devez savoir sur les paramètres des procédures stockées, y compris les types de paramètres, la manière de les déclarer, des exemples pratiques, et la gestion des erreurs.

Sommaire

Notions de base des procédures stockées

Une procédure stockée est un ensemble d’instructions SQL qui s’exécutent dans la base de données et peuvent être appelées comme une fonction. Cela permet de gérer efficacement des requêtes complexes et des tâches répétitives, tout en améliorant la réutilisabilité et la maintenabilité. Les procédures stockées contribuent également à l’optimisation des performances et au renforcement de la sécurité.

Avantages des procédures stockées

Voici les principaux avantages de l’utilisation des procédures stockées :

  1. Réutilisabilité : une fois créées, elles peuvent être réutilisées plusieurs fois.
  2. Performance : elles sont précompilées, ce qui améliore la vitesse d’exécution.
  3. Sécurité : elles réduisent les risques d’injection SQL en permettant d’exécuter des opérations via des procédures plutôt que des requêtes SQL directes.
  4. Maintenabilité : en centralisant la logique en un seul endroit, elles facilitent la maintenance.

Syntaxe de base des procédures stockées

Voici la syntaxe de base pour créer une procédure stockée dans SQL Server :

CREATE PROCEDURE ProcedureName
AS
BEGIN
    -- Écrire les instructions SQL ici
END;

À titre d’exemple, créons une procédure stockée simple pour récupérer des données de la table des employés.

CREATE PROCEDURE GetEmployees
AS
BEGIN
    SELECT * FROM Employees;
END;

Cette procédure stockée récupère tous les enregistrements de la table des employés lorsqu’elle est exécutée.

Types de paramètres et leurs caractéristiques

Les procédures stockées peuvent utiliser plusieurs types de paramètres pour effectuer des manipulations de données flexibles. Il existe trois types de paramètres : les paramètres d’entrée, les paramètres de sortie et les paramètres d’entrée-sortie. Examinons en détail les caractéristiques et les utilisations de chacun d’eux.

Paramètres d’entrée

Les paramètres d’entrée sont utilisés pour passer des valeurs à la procédure stockée. Ils reçoivent une valeur de l’appelant et effectuent des opérations basées sur cette valeur. Ils sont déclarés avec le mot-clé IN.

Exemple :

CREATE PROCEDURE GetEmployeeByID
    @EmployeeID INT
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

Dans cet exemple, le paramètre d’entrée @EmployeeID est utilisé pour obtenir les informations d’un employé spécifique.

Paramètres de sortie

Les paramètres de sortie sont utilisés pour retourner le résultat de l’exécution de la procédure stockée à l’appelant. Ils sont déclarés avec le mot-clé OUT.

Exemple :

CREATE PROCEDURE GetEmployeeCount
    @EmployeeCount INT OUTPUT
AS
BEGIN
    SELECT @EmployeeCount = COUNT(*) FROM Employees;
END;

Dans cet exemple, le paramètre de sortie @EmployeeCount est utilisé pour obtenir le nombre d’employés et le retourner à l’appelant.

Paramètres d’entrée-sortie

Les paramètres d’entrée-sortie sont utilisés pour passer une valeur à la procédure stockée et pour renvoyer une valeur mise à jour après le traitement. Ils sont déclarés avec le mot-clé INOUT.

Exemple :

CREATE PROCEDURE UpdateEmployeeSalary
    @EmployeeID INT,
    @NewSalary DECIMAL(10, 2) OUTPUT
AS
BEGIN
    UPDATE Employees SET Salary = @NewSalary WHERE EmployeeID = @EmployeeID;
    SELECT @NewSalary = Salary FROM Employees WHERE EmployeeID = @EmployeeID;
END;

Dans cet exemple, le paramètre d’entrée-sortie @NewSalary est utilisé pour mettre à jour le salaire d’un employé et renvoyer le salaire mis à jour.

Déclaration et utilisation des paramètres

Nous expliquons comment déclarer et utiliser les paramètres dans une procédure stockée avec des exemples concrets. En déclarant et en utilisant correctement les paramètres, vous pouvez considérablement améliorer la flexibilité et la réutilisabilité de vos procédures stockées.

Déclaration des paramètres

Lors de la déclaration de paramètres dans une procédure stockée, vous devez spécifier le nom du paramètre, le type de données, et éventuellement la direction (entrée, sortie, entrée-sortie).

Syntaxe de base :

CREATE PROCEDURE ProcedureName
    @ParameterName DataType [IN | OUT | INOUT]
AS
BEGIN
    -- Écrire les instructions SQL ici
END;

Exemple :

CREATE PROCEDURE GetEmployeeByName
    @EmployeeName NVARCHAR(50)
AS
BEGIN
    SELECT * FROM Employees WHERE Name = @EmployeeName;
END;

Dans cet exemple, le paramètre @EmployeeName est déclaré et utilisé pour rechercher des données en fonction du nom de l’employé.

Utilisation des paramètres

Les paramètres déclarés peuvent être utilisés comme des variables normales dans la procédure stockée. Vous pouvez définir des valeurs pour les paramètres et exécuter des instructions SQL basées sur ces valeurs.

Exemple :

CREATE PROCEDURE UpdateEmployeeDepartment
    @EmployeeID INT,
    @NewDepartmentID INT
AS
BEGIN
    UPDATE Employees SET DepartmentID = @NewDepartmentID WHERE EmployeeID = @EmployeeID;
END;

Dans cette procédure stockée, deux paramètres d’entrée, @EmployeeID et @NewDepartmentID, sont utilisés pour mettre à jour le département d’un employé.

Exemple d’utilisation des paramètres : insertion de données

Voici un exemple d’utilisation des paramètres pour insérer des données.

Exemple :

CREATE PROCEDURE AddNewEmployee
    @EmployeeName NVARCHAR(50),
    @HireDate DATE,
    @DepartmentID INT
AS
BEGIN
    INSERT INTO Employees (Name, HireDate, DepartmentID)
    VALUES (@EmployeeName, @HireDate, @DepartmentID);
END;

Cette procédure stockée insère les informations d’un nouvel employé dans la table Employees.

Types de données des paramètres

Les paramètres des procédures stockées peuvent avoir divers types de données, ce qui permet des manipulations de données efficaces et précises. Nous allons expliquer ici les principaux types de données et comment les choisir.

Types de données de base

Voici quelques types de données couramment utilisés dans les procédures stockées.

  • INT : Stocke des valeurs entières. Exemples : âge, ID.
  • DECIMAL : Stocke des nombres avec des décimales. Exemples : prix, salaire.
  • NVARCHAR : Stocke des chaînes de caractères de longueur variable. Exemples : nom, adresse.
  • DATE : Stocke des dates. Exemples : date de naissance, date d’embauche.

Choix du type de données

Lors du choix du type de données d’un paramètre, considérez les points suivants :

Nature des données

Choisissez le type de données approprié en fonction de la nature des données. Par exemple, utilisez le type INT pour les quantités ou les comptes, DECIMAL pour les prix ou les ratios, et NVARCHAR pour les informations textuelles.

Efficacité du stockage

Le choix du type de données affecte également l’efficacité du stockage. En choisissant un type de données de taille appropriée, vous pouvez minimiser l’utilisation de l’espace de stockage. Par exemple, pour les chaînes de caractères courtes, utilisez NVARCHAR(50) avec une longueur appropriée.

Précision des données

Pour les données numériques, choisissez un type de données en fonction de la précision et de l’échelle nécessaires. Par exemple, utilisez DECIMAL(10, 2) pour gérer des montants avec une précision de deux chiffres après la virgule.

Exemple pratique : choix du type de données des paramètres

Dans l’exemple suivant, nous choisissons les types de données appropriés dans une procédure stockée pour gérer les informations des employés.

CREATE PROCEDURE AddEmployee
    @EmployeeName NVARCHAR(100),
    @BirthDate DATE,
    @Salary DECIMAL(10, 2),
    @DepartmentID INT
AS
BEGIN
    INSERT INTO Employees (Name, BirthDate, Salary, DepartmentID)
    VALUES (@EmployeeName, @BirthDate, @Salary, @DepartmentID);
END;

Dans cet exemple, nous utilisons NVARCHAR(100) pour le nom de l’employé, DATE pour la date de naissance, DECIMAL(10, 2) pour le salaire, et INT pour l’ID du département.

Branchements conditionnels avec des paramètres

Vous pouvez utiliser des branchements conditionnels avec des paramètres dans les procédures stockées pour créer des requêtes flexibles et puissantes. Nous allons expliquer les bases des branchements conditionnels et fournir des exemples pratiques.

Branchement conditionnel de base

En utilisant l’instruction IF, vous pouvez exécuter différentes opérations en fonction des valeurs des paramètres.

Exemple :

CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT,
    @IncludeSalary BIT
AS
BEGIN
    IF @IncludeSalary = 1
    BEGIN
        SELECT Name, BirthDate, Salary FROM Employees WHERE EmployeeID = @EmployeeID;
    END
    ELSE
    BEGIN
        SELECT Name, BirthDate FROM Employees WHERE EmployeeID = @EmployeeID;
    END
END;

Dans cette procédure stockée, en fonction de la valeur du paramètre @IncludeSalary, le salaire est inclus ou non dans les résultats.

Branchement conditionnel complexe

En combinant plusieurs conditions, vous pouvez implémenter une logique plus complexe.

Exemple :

CREATE PROCEDURE FilterEmployees
    @DepartmentID INT = NULL,
    @MinSalary DECIMAL(10, 2) = NULL,
    @MaxSalary DECIMAL(10, 2) = NULL
AS
BEGIN
    SELECT * FROM Employees
    WHERE (@DepartmentID IS NULL OR DepartmentID = @DepartmentID)
    AND (@MinSalary IS NULL OR Salary >= @MinSalary)
    AND (@MaxSalary IS NULL OR Salary <= @MaxSalary);
END;

Dans cet exemple, les employés sont filtrés en fonction des paramètres @DepartmentID, @MinSalary, et @MaxSalary. Si un paramètre est NULL, cette condition est ignorée.

Exemple pratique : mise à jour des données avec branchement conditionnel

Dans l’exemple suivant, nous utilisons des paramètres pour mettre à jour les données d’un employé en fonction de conditions.

Exemple :

CREATE PROCEDURE UpdateEmployeeInfo
    @EmployeeID INT,
    @NewName NVARCHAR(100) = NULL,
    @NewDepartmentID INT = NULL,
    @NewSalary DECIMAL(10, 2) = NULL
AS
BEGIN
    IF @NewName IS NOT NULL
    BEGIN
        UPDATE Employees SET Name = @NewName WHERE EmployeeID = @EmployeeID;
    END

    IF @NewDepartmentID IS NOT NULL
    BEGIN
        UPDATE Employees SET DepartmentID = @NewDepartmentID WHERE EmployeeID = @EmployeeID;
    END

    IF @NewSalary IS NOT NULL
    BEGIN
        UPDATE Employees SET Salary = @NewSalary WHERE EmployeeID = @EmployeeID;
    END
END;

Dans cette procédure stockée, les informations de l’employé sont mises à jour en fonction des paramètres @NewName, @NewDepartmentID, et @NewSalary. Chaque champ est mis à jour uniquement si le paramètre correspondant n’est pas NULL.

Utilisation de plusieurs paramètres

En utilisant plusieurs paramètres dans une procédure stockée, vous pouvez exécuter des requêtes plus flexibles et complexes. Nous expliquons ici comment utiliser plusieurs paramètres et les meilleures pratiques à suivre.

Principes de base des multiples paramètres

Pour ajouter plusieurs paramètres à une procédure stockée, déclarez chaque paramètre séparément, en les séparant par des virgules.

Syntaxe de base :

CREATE PROCEDURE ProcedureName
    @Parameter1 DataType,
    @Parameter2 DataType,
    ...
AS
BEGIN
    -- Écrire les instructions SQL ici
END;

Exemple :

CREATE PROCEDURE GetEmployeeInfo
    @EmployeeID INT,
    @DepartmentID INT
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID AND DepartmentID = @DepartmentID;
END;

Dans cet exemple, deux paramètres, @EmployeeID et @DepartmentID, sont utilisés pour obtenir les informations d’un employé spécifique.

Valeurs par défaut des paramètres

En définissant des valeurs par défaut pour les paramètres, vous pouvez omettre des valeurs lors de l’appel de la procédure.

Exemple :

CREATE PROCEDURE GetEmployeesByDepartment
    @DepartmentID INT = NULL
AS
BEGIN
    IF @DepartmentID IS NULL
    BEGIN
        SELECT * FROM Employees;
    END
    ELSE
    BEGIN
        SELECT * FROM Employees WHERE DepartmentID = @DepartmentID;
    END
END;

Dans cette procédure stockée, si le paramètre @DepartmentID n’est pas spécifié, tous les employés sont récupérés ; s’il est spécifié, seuls les employés du département particulier sont récupérés.

Ordre et spécification des paramètres

Lorsque vous appelez une procédure stockée, faites attention à l’ordre des paramètres. En spécifiant le nom des paramètres, vous pouvez appeler la procédure sans dépendre de l’ordre.

Exemple :

EXEC GetEmployeeInfo @EmployeeID = 1, @DepartmentID = 2;

Dans cet appel, en spécifiant le nom des paramètres, vous pouvez passer les valeurs correctement, indépendamment de l’ordre.

Exemple pratique : insertion de données avec plusieurs paramètres

Dans l’exemple suivant, nous utilisons plusieurs paramètres pour insérer de nouvelles données d’employé.

Exemple :

CREATE PROCEDURE AddEmployee
    @EmployeeName NVARCHAR(100),
    @BirthDate DATE,
    @Salary DECIMAL(10, 2),
    @DepartmentID INT
AS
BEGIN
    INSERT INTO Employees (Name, BirthDate, Salary, DepartmentID)
    VALUES (@EmployeeName, @BirthDate, @Salary, @DepartmentID);
END;

Cette procédure stockée utilise quatre paramètres, @EmployeeName, @BirthDate, @Salary, et @DepartmentID, pour insérer les informations d’un nouvel employé.

Exemple pratique : génération de SQL dynamique

En utilisant des paramètres pour générer des SQL dynamiques, vous pouvez créer des requêtes flexibles et générales. Cependant, lors de l’utilisation de SQL dynamique, il est important de prendre des mesures appropriées pour prévenir le risque d’injection SQL.

Syntaxe de base du SQL dynamique

Pour utiliser le SQL dynamique, vous utilisez EXEC ou sp_executesql pour exécuter des instructions SQL. En particulier, en utilisant sp_executesql, vous pouvez exécuter des requêtes paramétrées, ce qui améliore la sécurité.

Exemple :

CREATE PROCEDURE SearchEmployees
    @SearchTerm NVARCHAR(100)
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'SELECT * FROM Employees WHERE Name LIKE @Term'

    EXEC sp_executesql @SQL, N'@Term NVARCHAR(100)', @Term = '%' + @SearchTerm + '%'
END;

Dans cette procédure stockée, le paramètre @SearchTerm est utilisé pour rechercher des enregistrements correspondant partiellement au nom de l’employé.

Avantages et précautions du SQL dynamique

Les avantages de l’utilisation du SQL dynamique sont les suivants :

  • Flexibilité : vous pouvez modifier la requête dynamiquement au moment de l’exécution.
  • Réutilisabilité : vous pouvez réutiliser la même requête de base avec différentes conditions.

Précautions :

  • Risque d’injection SQL : en utilisant directement l’entrée utilisateur dans le SQL dynamique, il y a un risque d’attaque par injection SQL, il est donc recommandé d’utiliser des requêtes paramétrées.
  • Performance : le SQL dynamique peut entraîner une performance inférieure par rapport au SQL normal.

Exemple pratique : génération de SQL dynamique avec plusieurs conditions

Dans l’exemple suivant, nous utilisons plusieurs paramètres pour générer un SQL dynamique basé sur plusieurs conditions.

Exemple :

CREATE PROCEDURE FilterEmployees
    @Name NVARCHAR(100) = NULL,
    @MinSalary DECIMAL(10, 2) = NULL,
    @MaxSalary DECIMAL(10, 2) = NULL
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'SELECT * FROM Employees WHERE 1=1'

    IF @Name IS NOT NULL
    BEGIN
        SET @SQL = @SQL + ' AND Name LIKE @Name'
    END

    IF @MinSalary IS NOT NULL
    BEGIN
        SET @SQL = @SQL + ' AND Salary >= @MinSalary'
    END

    IF @MaxSalary IS NOT NULL
    BEGIN
        SET @SQL = @SQL + ' AND Salary <= @MaxSalary'
    END

    EXEC sp_executesql @SQL,
        N'@Name NVARCHAR(100), @MinSalary DECIMAL(10, 2), @MaxSalary DECIMAL(10, 2)',
        @Name = '%' + @Name + '%',
        @MinSalary = @MinSalary,
        @MaxSalary = @MaxSalary
END;

Dans cette procédure stockée, les employés sont filtrés en fonction des paramètres @Name, @MinSalary, et @MaxSalary. Chaque condition est ajoutée dynamiquement.

Validation des paramètres et gestion des erreurs

Lors de l’utilisation de paramètres dans une procédure stockée, il est important de valider les valeurs des paramètres et de gérer correctement les erreurs. Cela permet de garantir la cohérence et la fiabilité des données.

Validation des paramètres

En validant les valeurs des paramètres dans une procédure stockée, vous pouvez prévenir les données incorrectes et les erreurs inattendues.

Exemple :

CREATE PROCEDURE UpdateEmployeeSalary
    @EmployeeID INT,
    @NewSalary DECIMAL(10, 2)
AS
BEGIN
    -- Validation des paramètres
    IF @EmployeeID <= 0
    BEGIN
        RAISERROR('EmployeeID must be greater than 0', 16, 1)
        RETURN
    END

    IF @NewSalary < 0
    BEGIN
        RAISERROR('Salary cannot be negative', 16, 1)
        RETURN
    END

    -- Mise à jour
    UPDATE Employees SET Salary = @NewSalary WHERE EmployeeID = @EmployeeID;
END;

Dans cette procédure stockée, nous validons que @EmployeeID est un entier positif et que @NewSalary n’est pas une valeur négative. Si les conditions ne sont pas remplies, une erreur est générée et le traitement est interrompu.

Gestion des erreurs

Il est important de traiter correctement les erreurs qui peuvent survenir dans une procédure stockée. L’utilisation du bloc TRY...CATCH est une méthode courante pour gérer les erreurs.

Exemple :

CREATE PROCEDURE TransferEmployee
    @EmployeeID INT,
    @NewDepartmentID INT
AS
BEGIN
    BEGIN TRY
        -- Début de la transaction
        BEGIN TRANSACTION

        -- Mise à jour du département
        UPDATE Employees SET DepartmentID = @NewDepartmentID WHERE EmployeeID = @EmployeeID;

        -- Commit
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        -- Rollback
        ROLLBACK TRANSACTION

        -- Récupération du message d'erreur
        DECLARE @ErrorMessage NVARCHAR(4000)
        SELECT @ErrorMessage = ERROR_MESSAGE()

        -- Générer l'erreur
        RAISERROR(@ErrorMessage, 16, 1)
    END CATCH
END;

Dans cette procédure stockée, nous effectuons une mise à jour du département dans une transaction. Si une erreur survient, la transaction est annulée et le message d’erreur est affiché.

Exemple pratique : combiner validation des paramètres et gestion des erreurs

Dans l’exemple suivant, nous combinons la validation des paramètres et la gestion des erreurs pour créer une procédure stockée plus robuste.

Exemple :

CREATE PROCEDURE PromoteEmployee
    @EmployeeID INT,
    @NewTitle NVARCHAR(100),
    @NewSalary DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRY
        -- Validation des paramètres
        IF @EmployeeID <= 0
        BEGIN
            RAISERROR('EmployeeID must be greater than 0', 16, 1)
            RETURN
        END

        IF @NewSalary < 0
        BEGIN
            RAISERROR('Salary cannot be negative', 16, 1)
            RETURN
        END

        -- Début de la transaction
        BEGIN TRANSACTION

        -- Promotion de l'employé
        UPDATE Employees SET Title = @NewTitle, Salary = @NewSalary WHERE EmployeeID = @EmployeeID;

        -- Commit
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        -- Rollback
        ROLLBACK TRANSACTION

        -- Récupération du message d'erreur
        DECLARE @ErrorMessage NVARCHAR(4000)
        SELECT @ErrorMessage = ERROR_MESSAGE()

        -- Générer l'erreur
        RAISERROR(@ErrorMessage, 16, 1)
    END CATCH
END;

Dans cette procédure stockée, nous validons l’ID de l’employé et la nouvelle valeur de salaire, puis nous effectuons la promotion dans une transaction. Si une erreur survient, la transaction est annulée et un message d’erreur est affiché.

Exemples avancés : traitement par lots avec procédures stockées

Les procédures stockées sont des outils puissants pour exécuter efficacement le traitement par lots. Lors de la manipulation de grandes quantités de données en masse, l’utilisation de procédures stockées permet d’améliorer les performances et de réutiliser le code.

Concepts de base du traitement par lots

Le traitement par lots est une méthode qui consiste à traiter de grandes quantités de données en une seule fois, y compris l’insertion, la mise à jour et la suppression de données. Cela permet d’effectuer des manipulations de données plus efficacement que le traitement individuel.

Avantages du traitement par lots

  • Amélioration des performances : le traitement d’une grande quantité de données en une seule fois est plus rapide que le traitement individuel.
  • Cohérence : vous pouvez maintenir la cohérence en utilisant des transactions.
  • Réutilisabilité : vous pouvez réutiliser le même processus plusieurs fois, ce qui augmente la réutilisabilité du code.

Exemple de traitement par lots : mise à jour des salaires des employés

Dans la procédure stockée suivante, nous mettons à jour les salaires des employés en masse en fonction des conditions spécifiées.

Exemple :

CREATE PROCEDURE UpdateSalariesBatch
    @DepartmentID INT,
    @SalaryIncrease DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRY
        -- Début de la transaction
        BEGIN TRANSACTION

        -- Mise à jour des salaires des employés du département spécifié
        UPDATE Employees
        SET Salary = Salary + @SalaryIncrease
        WHERE DepartmentID = @DepartmentID;

        -- Commit
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        -- Rollback
        ROLLBACK TRANSACTION

        -- Récupération du message d'erreur
        DECLARE @ErrorMessage NVARCHAR(4000)
        SELECT @ErrorMessage = ERROR_MESSAGE()

        -- Générer l'erreur
        RAISERROR(@ErrorMessage, 16, 1)
    END CATCH
END;

Dans cette procédure stockée, nous mettons à jour les salaires de tous les employés d’un département spécifique en masse. Nous utilisons une transaction pour maintenir la cohérence et annuler en cas d’erreur.

Exemple de traitement par lots : insertion de données volumineuses

Dans la procédure stockée suivante, nous chargeons des données d’une autre table et insérons un grand nombre d’enregistrements en masse.

Exemple :

CREATE PROCEDURE InsertNewEmployeesBatch
AS
BEGIN
    BEGIN TRY
        -- Début de la transaction
        BEGIN TRANSACTION

        -- Insertion en masse des nouvelles données des employés
        INSERT INTO Employees (Name, BirthDate, Salary, DepartmentID)
        SELECT Name, BirthDate, Salary, DepartmentID
        FROM NewEmployees;

        -- Commit
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        -- Rollback
        ROLLBACK TRANSACTION

        -- Récupération du message d'erreur
        DECLARE @ErrorMessage NVARCHAR(4000)
        SELECT @ErrorMessage = ERROR_MESSAGE()

        -- Générer l'erreur
        RAISERROR(@ErrorMessage, 16, 1)
    END CATCH
END;

Dans cette procédure stockée, nous chargeons des données de la table NewEmployees et les insérons en masse dans la table Employees. Nous utilisons une transaction pour garantir la cohérence et annuler en cas d’erreur.

Exercices pratiques

Nous proposons des exercices pratiques pour approfondir votre apprentissage et améliorer vos compétences pratiques. Ces exercices vous aideront à mieux comprendre l’utilisation des paramètres des procédures stockées et des exemples d’application du traitement par lots.

Exercice 1 : création d’une procédure stockée de base

Créez une procédure stockée de base en fonction des exigences suivantes :

  • Accepter EmployeeID comme paramètre d’entrée et créer une procédure stockée pour obtenir les détails de cet employé.
  • Les informations sur l’employé doivent inclure Name, BirthDate, et DepartmentID.

Exemple de réponse

CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT
AS
BEGIN
    SELECT Name, BirthDate, DepartmentID
    FROM Employees
    WHERE EmployeeID = @EmployeeID;
END;

Exercice 2 : création d’une procédure stockée avec branchement conditionnel

Créez une procédure stockée avec branchement conditionnel en fonction des exigences suivantes :

  • Accepter DepartmentID et MinSalary comme paramètres d’entrée, et filtrer les employés en fonction des conditions spécifiées.
  • Si DepartmentID est NULL, recherchez tous les employés.
  • Si MinSalary est NULL, n’appliquez aucune condition liée au salaire.

Exemple de réponse

CREATE PROCEDURE FilterEmployees
    @DepartmentID INT = NULL,
    @MinSalary DECIMAL(10, 2) = NULL
AS
BEGIN
    SELECT * FROM Employees
    WHERE (@DepartmentID IS NULL OR DepartmentID = @DepartmentID)
    AND (@MinSalary IS NULL OR Salary >= @MinSalary);
END;

Exercice 3 : création d’une procédure stockée avec gestion des erreurs

Créez une procédure stockée avec gestion des erreurs en fonction des exigences suivantes :

  • Accepter EmployeeID et NewSalary comme paramètres d’entrée et mettre à jour le salaire de l’employé.
  • Si EmployeeID est inférieur ou égal à 0 ou si NewSalary est une valeur négative, générer une erreur.
  • Utiliser une transaction pour maintenir la cohérence.

Exemple de réponse

CREATE PROCEDURE UpdateEmployeeSalary
    @EmployeeID INT,
    @NewSalary DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRY
        -- Validation des paramètres
        IF @EmployeeID <= 0
        BEGIN
            RAISERROR('EmployeeID must be greater than 0', 16, 1)
            RETURN
        END

        IF @NewSalary < 0
        BEGIN
            RAISERROR('Salary cannot be negative', 16, 1)
            RETURN
        END

        -- Début de la transaction
        BEGIN TRANSACTION

        -- Mise à jour du salaire de l'employé
        UPDATE Employees SET Salary = @NewSalary WHERE EmployeeID = @EmployeeID;

        -- Commit
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        -- Rollback
        ROLLBACK TRANSACTION

        -- Récupération du message d'erreur
        DECLARE @ErrorMessage NVARCHAR(4000)
        SELECT @ErrorMessage = ERROR_MESSAGE()

        -- Générer l'erreur
        RAISERROR(@ErrorMessage, 16, 1)
    END CATCH
END;

Conclusion

En utilisant les paramètres des procédures stockées SQL, vous pouvez améliorer considérablement l’efficacité et la flexibilité des opérations de données. Dans cet article, nous avons couvert en détail les types de paramètres, la manière de les déclarer, des exemples pratiques, la gestion des erreurs, et des exemples d’application du traitement par lots, allant des concepts de base aux applications avancées. En utilisant ces connaissances, vous pouvez réaliser des opérations de base de données plus complexes et plus efficaces. L’utilisation appropriée des paramètres permet de créer un code SQL sûr et facile à maintenir.

SQL
Gestion des erreurs SQL Base de Données Procédure Stockée Paramètres d'Entrée Paramètres Traitement par Lot Paramètres de Sortie
Sommaire