Devolución de datos de un procedimiento almacenado

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Existen tres formas de devolver datos de un procedimiento a un programa de llamada: conjuntos de resultados, parámetros de salida y códigos de retorno. En este tema se proporciona información sobre los tres enfoques.

Devolución de datos con conjuntos de resultados

Si incluye una instrucción SELECT en el cuerpo de un procedimiento almacenado (pero no una instrucción SELECT... INTO o INSERT ... SELECT), las filas especificadas por la instrucción SELECT se enviarán directamente al cliente. En el caso de conjuntos de resultados grandes, la ejecución del procedimiento almacenado no continuará hasta la siguiente instrucción hasta que el conjunto de resultados se haya enviado completamente al cliente. En cuanto a los conjuntos de resultados pequeños, los resultados se ponen en cola para su devolución al cliente y la ejecución continuará. Si se ejecutan varias instrucciones SELECT durante la ejecución del procedimiento almacenado, se enviarán varios conjuntos de resultados al cliente. Este comportamiento también se aplica a los lotes de Transact-SQL anidados, los procedimientos almacenados anidados y los lotes de Transact-SQL de nivel superior.

Ejemplos de devolución de datos con un conjunto de resultados

Los ejemplos siguientes usan la base de datos de ejemploAdventureWorks2022. En el ejemplo siguiente se muestra un procedimiento almacenado que devuelve los valores LastName y SalesYTD para todas las filas SalesPerson que también aparecen en la vista 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  
   JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID;
   
   RETURN;
GO 

Devolución de datos mediante un parámetro de salida

Si especifica la palabra clave OUTPUT para un parámetro en la definición del procedimiento, este, al salir, podrá devolver el valor actual del parámetro al programa de llamada. Para guardar el valor del parámetro en una variable que pueda usarse en el programa de llamada, este último debe usar la palabra clave OUTPUT para ejecutar el procedimiento. Para obtener más información sobre los tipos de datos se pueden usar como parámetros de salida, vea CREATE PROCEDURE (Transact-SQL).

Ejemplos de parámetros de salida

En el ejemplo siguiente se muestra un procedimiento con un parámetro de entrada y otro de salida. El parámetro @SalesPerson recibirá un valor de entrada especificado por el programa de llamada. La instrucción SELECT usa el último valor del parámetro de entrada para obtener el valor de SalesYTD correcto. La instrucción SELECT también asigna el valor al parámetro de salida @SalesYTD , que devuelve el valor al programa de llamada cuando finaliza el procedimiento.

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  
    JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID  
    WHERE LastName = @SalesPerson;

    RETURN;
GO 

El ejemplo siguiente llama al procedimiento creado en el primer ejemplo y guarda el parámetro de salida @SalesYTD devuelto desde el procedimiento llamado en la variable @SalesYTDBySalesPerson.

El ejemplo:

  • Declara la variable @SalesYTDBySalesPerson para recibir el valor de salida del procedimiento.
  • Ejecuta el procedimiento Sales.uspGetEmployeeSalesYTD especificando un apellido para el parámetro de entrada. Guarde el valor de salida en la variable @SalesYTDBySalesPerson.
  • Llama a PRINT para mostrar el valor guardado en @SalesYTDBySalesPerson.
DECLARE @SalesYTDBySalesPerson money;

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

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

También es posible especificar los valores de entrada para los parámetros de salida cuando se ejecuta el procedimiento. Esto permite al procedimiento recibir un valor del programa de llamada, cambiarlo o realizar operaciones con él y, a continuación, devolver el nuevo valor al programa de llamada. En el ejemplo anterior, es posible asignar un valor a la variable @SalesYTDBySalesPerson antes de que el programa llame al procedimiento Sales.uspGetEmployeeSalesYTD . La instrucción de ejecución pasaría el valor de la variable @SalesYTDBySalesPerson en el parámetro de salida @SalesYTD. Posteriormente, en el cuerpo del procedimiento, el valor se podría usar para realizar cálculos que generen un valor nuevo. El nuevo valor se devolvería al procedimiento mediante el parámetro de salida y se actualizaría el valor de la variable @SalesYTDBySalesPerson cuando finalice el procedimiento. A esto se le suele denominar "capacidad de paso por referencia".

Si especifica OUTPUT para un parámetro cuando llama a un procedimiento y dicho parámetro no está definido mediante OUTPUT en la definición del procedimiento, se emite un mensaje de error. No obstante, puede ejecutar un procedimiento con parámetros de salida y no especificar OUTPUT al ejecutar el procedimiento. No se devuelve ningún error, pero no podrá utilizar el valor de salida en el programa que realiza la llamada.

Uso del tipo de datos de cursor en parámetros de salida

Los procedimientos Transact-SQL solo pueden usar el tipo de datos de cursor para los parámetros de salida. Si se especifica el tipo de datos de cursor para un parámetro, deben especificarse las palabras clave VARYING y OUTPUT para ese parámetro en la definición del procedimiento. Un parámetro solo se puede especificar como salida, pero si se especifica la palabra clave VARYING en la declaración del parámetro, el tipo de datos debe ser cursor y también se debe especificar la palabra clave OUTPUT.

Nota:

El tipo de datos cursor no se puede enlazar a variables de aplicación a través de las API de bases de datos tales como OLE DB, ODBC, ADO y DB-Library. Debido a que los parámetros de salida deben estar enlazados antes de que una aplicación pueda ejecutar un procedimiento, los procedimientos con parámetros de salida de cursor no pueden llamarse desde las API de bases de datos. Estos procedimientos solo pueden llamarse desde procesos por lotes, procedimientos o desencadenadores Transact-SQL cuando la variable de salida cursor esté asignada a una variable cursor local de Transact-SQL.

Reglas para parámetros de salida de cursor

Las siguientes reglas se aplican a los parámetros cursor OUTPUT cuando se ejecuta el procedimiento:

  • Para un cursor de solo avance, las filas devueltas en el conjunto de resultados del cursor son solo aquellas filas que estén en la posición del cursor y hacia delante al concluir la ejecución del procedimiento, por ejemplo:

    • En un procedimiento de un conjunto de resultados llamado RS de 100 filas, se abre un cursor no desplazable.

    • El procedimiento captura las primeras cinco filas del conjunto de resultados RS.

    • El procedimiento vuelve a quien le llamó.

    • El conjunto de resultados RS que se devolvió al autor de la llamada está formado por las filas 6 a 100 de RS; el cursor del autor de la llamada se coloca antes de la primera fila de RS.

  • Para un cursor de solo avance, si el cursor se coloca antes de la primera fila cuando finalice el procedimiento, el conjunto de resultados completo se devuelve al proceso por lotes, procedimiento o desencadenador de llamada. Cuando se devuelve, la posición del cursor se establece antes de la primera fila.

  • Para un cursor de solo avance, si el cursor se coloca después del final de la última fila cuando finaliza el procedimiento almacenado, se devolverá un conjunto de resultados vacío al proceso por lotes, procedimiento o desencadenador de llamada.

    Nota:

    Un conjunto de resultados vacío no es lo mismo que un valor NULL.

  • Para un cursor desplazable, todas las filas del conjunto de resultados se devuelven al proceso por lotes, procedimiento o desencadenador de llamada cuando finaliza el procedimiento. Cuando se devuelve, la posición del cursor se deja en la posición de la última captura ejecutada en el procedimiento.

  • Para cualquier tipo de cursor, si se ha cerrado el cursor, se devuelve un valor NULL al proceso por lotes, procedimiento o desencadenador de llamada. Esto también ocurrirá si se ha asignado un cursor a un parámetro, pero ese cursor nunca se abre.

    Nota:

    El estado cerrado solo tiene importancia en el momento del retorno. Por ejemplo, es válido cerrar un cursor a mitad del procedimiento, volver a abrirlo posteriormente en el procedimiento y devolver el conjunto de resultados de ese cursor al proceso por lotes, procedimiento o desencadenador de llamada.

Ejemplos de parámetros de salida de cursor

En el siguiente ejemplo, se crea un procedimiento que especifica un parámetro de salida @CurrencyCursor con el tipo de datos cursor. A continuación, se llama al procedimiento desde un lote.

Primero, crea el procedimiento que declara y, a continuación, abre un cursor en la tabla 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  

A continuación, se ejecuta un proceso por lotes que declara una variable cursor local, ejecuta el procedimiento para asignar el cursor a la variable local y, por último, captura las filas desde el cursor.

USE AdventureWorks2022;  
GO

DECLARE @MyCursor CURSOR;  

EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;  

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

CLOSE @MyCursor;  
DEALLOCATE @MyCursor;  
GO    

Devolución de datos con un código de retorno

Un procedimiento puede devolver un valor entero, denominado código de retorno, para indicar el estado de ejecución de un procedimiento. Se especifica el código de retorno para un procedimiento mediante la instrucción RETURN. Al igual que con los parámetros de salida, debe guardar el código de retorno en una variable cuando se ejecute el procedimiento a fin de usar su valor en el programa de llamada. Por ejemplo, la variable de asignación @result del tipo de datos int se usa para almacenar el código de retorno del procedimiento my_proc, como:

DECLARE @result int;

EXECUTE @result = my_proc;
GO

Los códigos de retorno suelen usarse en los bloques de control de flujo dentro de los procedimientos con el fin de establecer el valor del código de retorno para cada posible situación de error. Puede utilizar la función @@ERROR después de una instrucción Transact-SQL para detectar si se ha producido un error durante la ejecución de la instrucción. Antes de la introducción del control de errores TRY/CATCH/THROW en Transact-SQL, a veces los códigos de devolución eran necesarios para determinar la correcta ejecución o los errores de los procedimientos almacenados. Los procedimientos almacenados deben indicar siempre cualquier problema con un error (generado con THROW/RAISERROR en caso necesario) y no depender de un código de devolución para hacerlo. También conviene evitar el uso del código de devolución para devolver datos de la aplicación.

Ejemplos de códigos de retorno

El ejemplo siguiente muestra el procedimiento usp_GetSalesYTD con control de errores que establece valores del código de retorno especiales para varios errores. La tabla siguiente muestra el valor entero que asigna el procedimiento a cada error posible y el significado correspondiente de cada valor.

Valor de código de retorno Significado
0 Ejecución correcta.
1 No se ha especificado el valor del parámetro requerido.
2 El valor del parámetro especificado no es válido.
3 Se ha producido un error al obtener el valor de venta.
4 Valor de venta NULL encontrado para el vendedor.

En el ejemplo se crea un procedimiento denominado Sales.usp_GetSalesYTD, que:

  • Declara el parámetro @SalesPerson y establece su valor predeterminado en NULL. Este parámetro está pensado para tomar el apellido de un vendedor.
  • Valida el parámetro @SalesPerson.
    • Si @SalesPerson es NULL, el procedimiento imprime un mensaje y devuelve el código de retorno 1.
    • De lo contrario, si el parámetro @SalesPerson no es NULL, el procedimiento comprueba el recuento de filas de la tabla HumanResources.vEmployee con un apellido igual al valor de @SalesPerson. Si el recuento es cero, el procedimiento devuelve el código de retorno 2.
  • Consulta las ventas del año hasta la fecha del vendedor con el apellido especificado y lo asigna al parámetro de salida @SalesYTD.
  • Comprueba errores de SQL Server probando @@ERROR (Transact-SQL).
    • Si @@ERROR no es igual a cero, el procedimiento devuelve el código de retorno 3.
    • Si @@ERROR era igual a cero, el procedimiento comprueba si el valor del parámetro @SalesYTD es NULL. Si no se encontraron ventas del año hasta la fecha, el procedimiento devuelve el código de retorno 4.
    • Si ninguna de las condiciones anteriores es verdadera, el procedimiento devuelve el código de retorno 0.
  • Si se alcanza, la instrucción final del procedimiento almacenado invoca el procedimiento almacenado de forma recursiva sin especificar un valor de entrada.

Al final del ejemplo, se proporciona código para ejecutar el procedimiento Sales.usp_GetSalesYTD mientras se especifica un apellido para el parámetro de entrada y se guarda el valor de salida en 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
         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

    EXEC Sales.usp_GetSalesYTD;
GO


DECLARE @SalesYTDForSalesPerson money, @ret_code 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

El ejemplo siguiente crea un programa para controlar los códigos de retorno devueltos desde el procedimiento usp_GetSalesYTD .

El ejemplo:

  • Declara las variables @SalesYTDForSalesPerson y @ret_code para recibir el valor de salida y el código de retorno del procedimiento.
  • Ejecuta el procedimiento Sales.usp_GetSalesYTD con un valor de entrada especificado para @SalesPerson y guarda el valor de salida y el código de retorno en variables.
  • Comprueba el código de retorno en @ret_code y llama a PRINT (Transact-SQL) para mostrar un mensaje adecuado.

DECLARE @SalesYTDForSalesPerson money, @ret_code 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

Para más información sobre los procedimientos almacenados y los conceptos relacionados, consulte los siguientes artículos: