Retourner des données d’une procédure stockée

Article
01/23/2025

S’applique à :SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Base de données SQL dans Microsoft Fabric

Il existe trois méthodes permettant de retourner des données depuis une procédure vers un programme appelant : les jeux de résultats, les paramètres de sortie et les codes de retour. Cet article fournit des informations sur les trois approches.

Les exemples de code Transact-SQL de cet article utilisent l’exemple de base de données AdventureWorks2022 ou AdventureWorksDW2022, que vous pouvez télécharger à partir de la page d’accueil Microsoft SQL Server Samples and Community Projects.

Retourner des données avec des jeux de résultats

Si vous incluez une instruction SELECT dans le corps d’une procédure stockée (mais pas une SELECT ... INTO ou une INSERT ... SELECT), les lignes spécifiées par l’instruction SELECT sont envoyées directement au client. Pour les jeux de résultats volumineux, l’exécution de la procédure stockée ne passe pas à l’instruction tant que le jeu de résultat n’a pas été entièrement envoyé au client. Pour les petits jeux de résultats, les résultats sont mis en file d'attente pour être retournés au client et l'exécution se poursuit. Si plusieurs instructions SELECT de ce type sont exécutées pendant l’exécution de la procédure stockée, plusieurs jeux de résultats sont envoyés au client. Ce comportement s’applique également aux lots Transact-SQL imbriqués, aux procédures stockées imbriquées et aux lots Transact-SQL du plus haut niveau.

Exemples de retour de données avec un jeu de résultats

Cet exemple montre une procédure stockée qui retourne les valeurs LastName et SalesYTD pour toutes les lignes SalesPerson qui s’affichent également dans la vue vEmployee.

USE AdventureWorks2022;
GO

IF OBJECT_ID('Sales.uspGetEmployeeSalesYTD', 'P') IS NOT NULL
    DROP PROCEDURE Sales.uspGetEmployeeSalesYTD;
GO

CREATE PROCEDURE Sales.uspGetEmployeeSalesYTD
AS
SET NOCOUNT ON;
SELECT LastName,
       SalesYTD
FROM Sales.SalesPerson AS sp
     INNER JOIN HumanResources.vEmployee AS e
         ON e.BusinessEntityID = sp.BusinessEntityID;
RETURN;
GO

Retourner des données à l'aide d'un paramètre de sortie

Si vous spécifiez le mot clé de sortie pour un paramètre dans la définition de procédure, la procédure peut retourner la valeur actuelle du paramètre au programme appelant lors de la sortie de la procédure. Pour enregistrer la valeur du paramètre dans une variable afin que le programme appelant puisse l'utiliser, ce dernier doit inclure le mot clé de sortie lorsqu'il exécute la procédure. Pour plus d’informations sur les types de données pouvant être utilisés en tant que paramètres de sortie, consultez CREATE PROCEDURE.

Exemples de paramètres de sortie

L'exemple ci-dessous illustre une procédure avec un paramètre d'entrée et un paramètre de sortie. Le paramètre @SalesPerson doit recevoir une valeur d'entrée spécifiée par le programme appelant. L’instruction SELECT utilise la valeur passée dans le paramètre d’entrée pour obtenir la valeur SalesYTD correcte. L’instruction SELECT affecte également la valeur au paramètre de sortie @SalesYTD, qui retourne la valeur au programme appelant lorsque la procédure se termine.

USE AdventureWorks2022;
GO

IF OBJECT_ID('Sales.uspGetEmployeeSalesYTD', 'P') IS NOT NULL
    DROP PROCEDURE Sales.uspGetEmployeeSalesYTD;
GO

CREATE PROCEDURE Sales.uspGetEmployeeSalesYTD
@SalesPerson NVARCHAR (50), @SalesYTD MONEY OUTPUT
AS
SET NOCOUNT ON;
SELECT @SalesYTD = SalesYTD
FROM Sales.SalesPerson AS sp
     INNER JOIN HumanResources.vEmployee AS e
         ON e.BusinessEntityID = sp.BusinessEntityID
WHERE LastName = @SalesPerson;
RETURN;
GO

L’exemple suivant appelle la procédure créée dans le premier exemple et enregistre le paramètre de sortie @SalesYTD retourné par la procédure appelée dans la variable @SalesYTDBySalesPerson.

L'exemple :

Déclare la variable @SalesYTDBySalesPerson pour recevoir la valeur de sortie de la procédure.
Exécute la procédure Sales.uspGetEmployeeSalesYTD en spécifiant un nom de famille pour le paramètre d’entrée. Enregistrez la valeur de sortie dans la variable @SalesYTDBySalesPerson.
Appelle PRINT pour afficher la valeur enregistrée dans @SalesYTDBySalesPerson.

DECLARE @SalesYTDBySalesPerson AS MONEY;

EXECUTE Sales.uspGetEmployeeSalesYTD N'Blythe',
    @SalesYTD = @SalesYTDBySalesPerson OUTPUT;

PRINT 'Year-to-date sales for this employee is '
    + CONVERT (VARCHAR (10), @SalesYTDBySalesPerson);
GO

Des valeurs d'entrée peuvent également être définies pour les paramètres de sortie lorsque la procédure est exécutée. Ainsi, la procédure peut recevoir une valeur du programme appelant, la modifier ou l'utiliser pour exécuter des opérations, puis retourner la nouvelle valeur au programme appelant. Dans l’exemple précédent, la variable @SalesYTDBySalesPerson peut recevoir une valeur avant que le programme appelle la procédure Sales.uspGetEmployeeSalesYTD . L’instruction exécutée transmettait la valeur de la variable @SalesYTDBySalesPerson dans le paramètre de sortie @SalesYTD. Ensuite, dans le corps de la procédure, la valeur peut être utilisée pour des calculs qui génèrent une nouvelle valeur. La nouvelle valeur est repassée hors de la procédure par le paramètre de sortie, mettant à jour la valeur dans la variable @SalesYTDBySalesPerson quand la procédure se termine. Il s’agit souvent de fonctionnalité de pass-by-reference.

Si vous spécifiez la sortie pour un paramètre lorsque vous appelez une procédure alors que le paramètre n'est pas défini avec la sortie dans la définition de la procédure, vous obtenez un message d'erreur. Il est néanmoins possible d'exécuter une procédure avec des paramètres de sortie et de ne pas spécifier la sortie lors de l'exécution de la procédure. Aucune erreur n'est retournée, mais vous ne pouvez pas utiliser la valeur de sortie dans le programme appelant.

Utiliser le type de données du curseur dans des paramètres de sortie

Les procédures Transact-SQL ne peuvent utiliser le type de données cursor que pour des paramètres de sortie. Si le type de données du curseur est spécifié pour un paramètre, les mots clés de variation et de sortie doivent être spécifiés pour ce paramètre dans la définition de la procédure. Un paramètre peut être spécifié comme sortie uniquement, mais si le mot clé de variation est spécifié dans la déclaration du paramètre, le type de données doit obligatoirement être curseur et vous devez également préciser le mot clé de sortie.

Remarque

Le type de données du curseur ne peut pas être lié aux variables d’application via les API de base de données telles que OLE DB, ODBC, ADO et DB-Library. Étant donné que les paramètres de sortie doivent être liés avant qu’une application puisse exécuter une procédure, les procédures avec des paramètres de sortie de curseur ne peuvent pas être appelées à partir des API de base de données. Ces procédures peuvent être appelées à partir de lots, procédures ou déclencheurs Transact-SQL seulement quand la variable de sortie cursor est affectée à une variable Transact-SQL cursor locale.

Règles pour les paramètres de sortie du curseur

Les règles suivantes régissent les paramètres de sortie de type cursor lors de l’exécution de la procédure :

Pour un curseur vers l’avant uniquement, les lignes retournées dans le jeu de résultats du curseur sont uniquement celles situées au-delà de la position du curseur, à la fin de l’exécution de la procédure. Par exemple:
- Un curseur ne permettant pas le défilement est ouvert dans une procédure, dans un jeu de résultats de 100 lignes, appelé RS.
- La procédure extrait les cinq premières lignes du jeu de résultats RS.
- La procédure est renvoyée vers son appelant.
- Le jeu de résultats RS renvoyé à l'appelant est constitué des lignes 6 à 100 de RS et le curseur dans l'appelant est placé avant la première ligne de RS.
Dans le cas d'un curseur avant uniquement, si le curseur se trouve avant la première ligne lorsque la procédure existe, la totalité du jeu de résultats est renvoyée au traitement d'instructions, à la procédure ou au déclencheur appelant. Lorsqu'elle est renvoyée, la position du curseur est fixée au début de la première ligne.
Dans le cas d'un curseur avant uniquement, si le curseur est placé au-delà de la fin de la dernière ligne lorsque la procédure existe, le système renvoie un jeu de résultats vide au traitement d'instructions, à la procédure ou au déclencheur appelant.

Remarque

Un jeu de résultats vide n’est pas identique à une valeur Null.
Dans le cas d'un curseur permettant le défilement, toutes les lignes du jeu de résultats sont renvoyées au traitement d'instructions, à la procédure ou au déclencheur appelant lorsque la procédure existe. Lors du renvoi, la position du curseur est conservée à la position de la dernière extraction exécutée dans la procédure.
Pour tous les types de curseur, si le curseur est fermé, une valeur NULL est repassée au traitement d'instructions, à la procédure ou au déclencheur appelant. C’est également le cas si un curseur est affecté à un paramètre, mais que ce curseur n’est jamais ouvert.

Remarque

L'état fermé n'a d'importance qu'au moment du retour. Par exemple, il est valide de fermer un curseur en cours de procédure, de l'ouvrir à nouveau ultérieurement dans la procédure, et de renvoyer le jeu de résultats de ce curseur au lot d'appels, à la procédure ou au déclencheur appelant.

Exemples de paramètres de sortie du curseur

Dans l’exemple ci-dessous, une procédure avec un paramètre de sortie, @CurrencyCursor utilisant le type de données curseur est créée. La procédure stockée est ensuite appelée dans un traitement.

Commencez par créer la procédure qui déclare un curseur puis l'ouvre dans la table Currency.

USE AdventureWorks2022;
GO

IF OBJECT_ID('dbo.uspCurrencyCursor', 'P') IS NOT NULL
    DROP PROCEDURE dbo.uspCurrencyCursor;
GO

CREATE PROCEDURE dbo.uspCurrencyCursor
@CurrencyCursor CURSOR VARYING OUTPUT
AS
SET NOCOUNT ON;
SET @CurrencyCursor = CURSOR FORWARD_ONLY STATIC
    FOR SELECT CurrencyCode,
               Name
        FROM Sales.Currency;
OPEN @CurrencyCursor;
GO

Ensuite, exécutez un traitement d'instructions qui déclare une variable curseur locale, exécute la procédure pour affecter le curseur à la variable locale et extrait les lignes du curseur.

USE AdventureWorks2022;
GO

DECLARE @MyCursor AS CURSOR;

EXECUTE dbo.uspCurrencyCursor
    @CurrencyCursor = @MyCursor OUTPUT;

WHILE (@@FETCH_STATUS = 0)
    BEGIN
        FETCH NEXT FROM @MyCursor;
    END

CLOSE @MyCursor;

DEALLOCATE @MyCursor;
GO

Retourner des données au moyen d'un code de retour

Une procédure peut retourner une valeur entière appelée « code de retour » pour indiquer l'état d'exécution d'une procédure. Vous spécifiez le code de retour d’une procédure avec RETURN. Comme dans le cas des paramètres de sortie, vous devez enregistrer le code de retour dans une variable lors de l'exécution de la procédure afin de pouvoir utiliser sa valeur dans le programme appelant. Par exemple, la variable d’affectation @result de type de données int est utilisée pour stocker le code de retour à partir de la procédure my_proc, par exemple :

DECLARE @result AS INT;
EXECUTE @result = my_proc;
GO

Les codes de retour sont couramment utilisés dans les blocs de contrôle de flux des procédures pour définir la valeur du code de retour pour chaque situation d'erreur possible. Vous pouvez utiliser la fonction @@ERROR après une instruction Transact-SQL pour détecter si une erreur s’est produite pendant l’exécution de l’instruction. Avant l'introduction de la gestion des erreurs TRY/CATCH/THROW dans Transact-SQL, les codes de retour étaient parfois nécessaires pour déterminer la réussite ou la défaillance des procédures stockées. Les procédures stockées doivent toujours indiquer une défaillance avec une erreur (générée avec THROW/RAISERROR si nécessaire) et ne pas utiliser un code de retour pour indiquer la défaillance. Il est également recommandé d’éviter d’utiliser le code de retour pour retourner des données d’application.

Exemples de codes de retour

L'exemple suivant illustre la procédure usp_GetSalesYTD de gestion d'erreurs qui définit les valeurs du code de retour pour diverses erreurs. Le tableau suivant montre la valeur entière attribuée par la procédure à chaque erreur possible, et la signification correspondante pour chaque valeur.

Valeur du code de retour	Signification
`0`	Exécution réussie.
`1`	La valeur de paramètre requise n’est pas spécifiée.
`2`	La valeur de paramètre spécifiée n’est pas valide.
`3`	Erreur lors de l'obtention de la valeur des ventes.
`4`	`NULL` valeur de vente trouvée pour le vendeur.

L’exemple crée une procédure nommée Sales.usp_GetSalesYTD, qui :

déclare le @SalesPerson paramètre et définit sa valeur par défaut sur NULL. Ce paramètre est destiné à prendre le nom de famille d’une personne commerciale.
Valide le paramètre @SalesPerson.
- Si @SalesPerson est NULL, la procédure imprime un message et retourne le code de retour 1.
- Sinon, si le paramètre @SalesPerson n’est pas NULL, la procédure vérifie le nombre de lignes dans la table HumanResources.vEmployee avec un nom de famille égal à la valeur de @SalesPerson. Si le nombre est égal à zéro, la procédure rend le code de retour 2.
Interroge les ventes depuis le début de l'année pour le commercial avec le nom de famille spécifié et les affecte au paramètre de sortie @SalesYTD.
Recherche les erreurs SQL Server en testant @@ERROR.
- Si @@ERROR n’est pas égal à zéro, la procédure rend le code de retour 3.
- Si @@ERROR était égal à zéro, la procédure vérifie si la valeur du paramètre @SalesYTD est NULL. Si aucune vente cumulée n’a été trouvée, la procédure renvoie le code de retour 4.
- Si aucune des conditions précédentes n’est vraie, la procédure retourne le code de retour 0.
Si elle est atteinte, la dernière instruction de la procédure stockée appelle la procédure stockée de manière récursive sans spécifier de valeur d’entrée.

À la fin de l’exemple, le code est fourni pour exécuter la procédure Sales.usp_GetSalesYTD tout en spécifiant un nom de famille pour le paramètre d’entrée et en enregistrant la valeur de sortie dans la variable @SalesYTD.

USE AdventureWorks2022;
GO

CREATE PROCEDURE Sales.usp_GetSalesYTD
    @SalesPerson NVARCHAR (50) = NULL,
    @SalesYTD MONEY = NULL OUTPUT
AS
IF @SalesPerson IS NULL
    BEGIN
        PRINT 'ERROR: You must specify a last name for the sales person.';
        RETURN (1);
    END
ELSE
    BEGIN
        IF (SELECT COUNT(*)
            FROM HumanResources.vEmployee
            WHERE LastName = @SalesPerson) = 0
            RETURN (2);
    END
SELECT @SalesYTD = SalesYTD
FROM Sales.SalesPerson AS sp
     INNER JOIN HumanResources.vEmployee AS e
         ON e.BusinessEntityID = sp.BusinessEntityID
WHERE LastName = @SalesPerson;
IF @@ERROR <> 0
    BEGIN
        RETURN (3);
    END
ELSE
    BEGIN
        IF @SalesYTD IS NULL
            RETURN (4);
        ELSE
            RETURN (0);
    END
EXECUTE Sales.usp_GetSalesYTD;
GO

DECLARE @SalesYTDForSalesPerson AS MONEY, @ret_code AS INT;

EXECUTE Sales.usp_GetSalesYTD N'Blythe',
    @SalesYTD = @SalesYTDForSalesPerson OUTPUT;

PRINT N'Year-to-date sales for this employee is '
    + CONVERT (VARCHAR (10), @SalesYTDForSalesPerson);
GO

L'exemple suivant crée un programme chargé de gérer les codes de retour retournés par la procédure usp_GetSalesYTD .

L'exemple :

Déclare des variables @SalesYTDForSalesPerson et @ret_code pour recevoir la valeur de sortie et le code de retour de la procédure.
Exécute la procédure Sales.usp_GetSalesYTD avec une valeur d’entrée spécifiée pour @SalesPerson et enregistre la valeur de sortie et le code de retour dans les variables.
Vérifie le code de retour dans @ret_code et appelle PRINT pour afficher un message approprié.

DECLARE @SalesYTDForSalesPerson AS MONEY, @ret_code AS INT;

EXECUTE
    @ret_code = Sales.usp_GetSalesYTD N'Blythe',
    @SalesYTD = @SalesYTDForSalesPerson OUTPUT;

IF @ret_code = 0
    BEGIN
        PRINT 'Procedure executed successfully';
        PRINT 'Year-to-date sales for this employee is '
            + CONVERT (VARCHAR (10), @SalesYTDForSalesPerson);
    END
ELSE
    IF @ret_code = 1
        PRINT 'ERROR: You must specify a last name for the sales person.';
    ELSE IF @ret_code = 2
        PRINT 'ERROR: You must enter a valid last name for the sales person.';
    ELSE IF @ret_code = 3
        PRINT 'ERROR: An error occurred getting sales value.';
    ELSE IF @ret_code = 4
        PRINT 'ERROR: No sales recorded for this employee.';
GO

Partage via

Retourner des données d’une procédure stockée

Retourner des données avec des jeux de résultats

Exemples de retour de données avec un jeu de résultats

Retourner des données à l'aide d'un paramètre de sortie

Exemples de paramètres de sortie

Utiliser le type de données du curseur dans des paramètres de sortie

Règles pour les paramètres de sortie du curseur

Exemples de paramètres de sortie du curseur

Retourner des données au moyen d'un code de retour

Exemples de codes de retour

Commentaires

Ressources supplémentaires

Partage via

Retourner des données d’une procédure stockée

Retourner des données avec des jeux de résultats

Exemples de retour de données avec un jeu de résultats

Retourner des données à l'aide d'un paramètre de sortie

Exemples de paramètres de sortie

Utiliser le type de données du curseur dans des paramètres de sortie

Règles pour les paramètres de sortie du curseur

Exemples de paramètres de sortie du curseur

Retourner des données au moyen d'un code de retour

Exemples de codes de retour

Contenu connexe

Commentaires

Ressources supplémentaires