Devolución de datos de un procedimiento almacenado

Se aplica a:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Base de datos de Azure SQL en Microsoft Fabric

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.

Los ejemplos de código de este artículo usan la base de datos de ejemplo de AdventureWorks2025 o AdventureWorksDW2025, que puede descargar de la página principal de Ejemplos de Microsoft SQL Server y proyectos de comunidad.

Devolución de datos con conjuntos de resultados

Si incluye una instrucción SELECT en el cuerpo de un procedimiento almacenado (pero no un SELECT ... INTO o INSERT ... SELECT), las filas especificadas por la instrucción SELECT se envían 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. Para conjuntos de resultados pequeños, los resultados se agrupan para volver al cliente y la ejecución continúa. Si se ejecutan varias instrucciones SELECT durante la ejecución del procedimiento almacenado, se envían 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

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
     INNER 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 que se pueden usar como parámetros de salida, vea CREATE PROCEDURE.

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 valor pasado al 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 que realiza la llamada cuando se cierra 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
     INNER 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 nombre de familia 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 AS 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 ejecutada pasaría el valor de variable @SalesYTDBySalesPerson al 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. Esto se conoce a menudo como funcionalidad de paso a 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.

Note

El tipo de datos de cursor no se puede enlazar a variables de aplicación a través de las API de base de datos como OLE DB, ODBC, ADO y DB-Library. Dado que los parámetros de salida deben enlazarse antes de que una aplicación pueda ejecutar un procedimiento, no se puede llamar a procedimientos con parámetros de salida de cursor desde las API de base 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 únicamente las que se encuentran en la posición del cursor o más allá, 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.

Note

Un conjunto de resultados vacío no es el 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. Este es también el caso si se asigna un cursor a un parámetro, pero ese cursor nunca se abre.

Note

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 AS CURSOR;

EXECUTE 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 devuelto del procedimiento my_proc, como:

DECLARE @result AS 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	Meaning
`0`	Ejecución exitosa.
`1`	No se especifica el valor de parámetro requerido.
`2`	El valor de parámetro especificado no es válido.
`3`	Se ha producido un error al obtener el valor de venta.
`4`	`NULL` valor de ventas 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 nombre de familia de un vendedor.
Valida el parámetro @SalesPerson.
- Si @SalesPerson es NULL, el procedimiento imprime un mensaje y devuelve el código devuelto 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 nombre de familia igual al valor de @SalesPerson. Si el recuento es cero, el procedimiento devuelve el código de retorno 2.
Consulta las ventas de año a fecha para el vendedor con el nombre de familia especificado y la asigna al parámetro de salida @SalesYTD.
Comprueba si hay errores de SQL Server mediante la prueba de @@ERROR.
- 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 al especificar un nombre de familia para el parámetro de entrada y guardar 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
     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

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