Parámetros y variables
Los parámetros y las variables son una parte fundamental de hacer dinámico a un procedimiento almacenado. Los parámetros de entrada habilitan al usuario que está ejecutando el procedimiento a pasar valores al procedimiento almacenado. Los parámetros de salida amplían la salida de los procedimientos almacenados mas allá del conjunto de resultados retornado por una consulta. Los datos desde un parámetro de salida son capturados en memoria cuando el procedimiento se ejecuta. Para retornar un valor desde el parámetro de salida se debe crear una variable para que lo mantenga. Se puede mostrar el valor con los comando SELECT o PRINT, o usar el valor para completar otro comando en el procedimiento.
En resumen, un parámetro de entrada se define en el procedimiento almacenado y un valor es provisto cuando el procedimiento se ejecuta. Un parámetro de salida se define usando la palabra clave OUTPUT. Cuando se ejecuta el procedimiento, se guarda en memoria un valor para el parámetro de salida. Para utilizarlo, se declara una variable para mantener al valor. Los valores de salida típica de muestran en pantalla una vez que terminó la ejecución del procedimiento.
El siguiente procedimiento muestra el uso de ambos parámetros, de salida y de entrada:
USE Pubs
GO
CREATE PROCEDURE dbo.SalesForTitle
@Title.varchar(80), --Parámetro de entrada
@YtdSales int OUTPUT, --Primer parámetro de salida
@TitleText varchar(80) OUTPUT –Segundo parámetro de salida
AS
-- Asigna la columna de datos a los parmetros de salida y
-- controla por un título que coincida con el parámetro de entrada
SELECT @YtdSales = ytd_sales, @TitleText = title
FROM titles WHERE title LIKE @Title
GO
El parámetro de entrada es @Title (título), y los de salida son @YtdSales y @TitleText. Fíjese que los tres parámetros tienen definidos sus tipos de datos. Los parámetros de salida incluye la palabra clave OUTPUT. Después que se definen los parámetros, el comando SELECT utiliza a los tres parámetros. Primero, a los parámetros de salida se les asigna a las columnas de la consulta. Cuando se ejecuta la consulta, los parámetros de salida contendrán los valores de esas dos columnas. La cláusula WHERE del comando SELECT contiene el parámetro de entrada, @Title. Cuando se ejecuta el procedimiento, se debe proveer un valor para el parámetro de entrada o fallará la consulta.
El siguiente comando ejecuta el procedimiento almacenado que vimos:
-- Declara variables para recibir las salidas del procedimiento
DECLARE @y_YtdSales int, @t_TitleText varchar(80)
EXECUTE SalesForTitle
-- configura los valores de variables con los parámetros de salida
@YtdSales = @y_YtdSales OUTPUT,
@Titletext = @t_TitleText OUTPUT,
@Title = “%Garlic%” – especifica un valor para el parámetro de entrada
-- Muestra las variables retornadas por el procedimiento
SELECT “Title” = @t_TitleText, “Número de Ventas” = @y_YtdSales
GO
Se declaran dos variables: @y_YtdSales y @t_TitleText. Estas dos variables recibirán los valores almacenados en los parámetros de salida. Advierta que el tipo de datos de la variables que reciben son los mismos que los tipos de los correspondientes parámetros de salida. Estas dos variables pueden tener el mismo nombre que los parámetros de salida correspondientes dado que las variables en un procedimiento almacenado son locales al batch que las contiene. Por claridad, los nombres de las variables son diferentes a los de los parámetros de salida. Cuando se declara una variable, esta no se asigna con un parámetro de salida. Las variables son asignadas con parámetros de salida después del comando EXECUTE. Fíjese que la palabra OUTPUT se indica cuando el parámetro se asigna a la variable. Si no se especifica OUTPUT, la variable no puede mostrar los valores en el comando SSELECT al final del código. Por ‘ultimo, el parámetro de entrada @Title es igualado a %Garlic%. Este valor se envía a la cláusula WHERE del comando SELECT del procedimiento almacenado. Dado que la cláusula WHERE usa la palabra clave KEYWORD, se pueden usar caracteres comodines (tal como %) para que que la consulta busque aquellos títulos que contienen la palabra Garlic.
A continuación se muestra un modo mas sucinto de ejecutar el procedimiento. Fíjese que no es necesario asignar específicamente las variables desde el procedimiento almacenado al valor del parámetro de salida o a las variables de salida declaradas aquí:
DECLARE @y_YtdSales int, @t_TitleText varchar(80)
EXECUTE SalesForTitle
“%Garlic%”, -- configura el valor del parámetro de entrada
@y_YtdSales OUTPUT -- recibe el primer parámetro de salida
@t_TitleText OUTPUT -- recibe el segundo parámetro de salida
-- muestra las variables retornadas por la ejecución del procedimiento
SELECT “Title” = @t_TitleText, “Número de ventas” = @y_YtdSales
GO
Cuando se ejecuta el procedimiento, este retorna lo siguiente:
Title | Number Return of Sales |
NULL | NULL |
Un interesante resultado de este procedimiento es que se retorna sólo una fila. Aún cuando el comando SELECT en el procedimiento devuelve múltiples filas, cada variable mantiene sólo un valor (la última fila de datos retornada). Mas adelante veremos algunas soluciones a este problema.
El comando RETURN y el manejo de errores
A menudo, se necesita codificar para manejar errores. SQL Server provee funciones y comandos para tratar con los errores que se producen durante la ejecución de un procedimiento almacenado. Las dos categorías primarias de errores de computo, tales como una base no disponible o errores de usuarios. Se utilizan códigos de retorno y la función @@ERROR para manejar errores que se producen cuando se ejecuta el procedimiento.
Los códigos de retorno pueden usarse para otros propósitos además de manejar errores. El comando RETURN se usa para generar códigos y salidas de un batch, y puede proveer d cualquier valor entero al programa que llamador. Veremos ejemplos de códigos que usan los códigos de retorno para proveer un valor tanto como para manejar errores o para otros fines.
El comando RETURN se usa primariamente para el manejo de errores porque cuando se corre el comando RETURN, el procedimiento almacenado termina inmediatamente.
Considere el procedimiento almacenado SalesForTitle del punto anterior. Si el valor especificado para el valor de entrada (@Title) no existe en la base ded atos, ejecutar el procedimiento retorna el siguiente conjunto de resultados:
Title | Number Return of Sales | Code |
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean | 375 | 0 |
Es mas claro explicar el usuario que no se han encontrado registroa. El ejemplo siguiente muestra como modificar el procedimiento almacenado SalesForTitle para usar un código de retorno (y proveer un mensaje mas útil):
ALTER PROCEDURE dbo.SalesForTitle
@Title varchar(80), @YtdSales int OUTPUT,
@titletext varchar (80) OUTPUT
AS
-- Controla para ver si el titulo esta la base. Sino, sale y devuelve 1
IF (SELECT COUNT(*) FROM titles WHERE title LIKE @Title) = 0
RETURN(1)
ELSE
SELECT @YtdSales = ytd_sales, @TitleText = title
FROM titles WHERE title LIKE @Title
GO
La sentencia IF posterior la palabra clave AS determina si el parámetro de entrada es provisto cuando el procedimiento se ejecuta y si coincide con algún registro de la base de datos. Si la función COUNT retorna 0, entonces el código de retorno es puesto en 1, RETURN(1). En otro caso, se ejecuta la consulta SELECT por las ventas anuales y la información del libro. En este caso el código de retorno es igual a 0.
Para invocar el procedimiento se necesitan hacer algunos cambios. El ejemplo siguiente configura al parámetro de entrada @Title igual a Garlic%:
--Agrega @r_Code para mantener el resultado
DECLARE @y_YtdSales int, @t_TitleText varchar(80), @r_Code int
-- Ejecuta el procedimeinto e iguala @r_Code al el procedimiento
EXECUTE @r_Code = SalesForTitle
@YtdSales = @y_YtdSales OUTPUT,
@TitleText = @t_TitleText OUTPUT,
@Title = "Garlic%"
-- Determina el valor de @r_Code y ejecuta el codigo
IF @r_Code = 0
SELECT "Title" = @t_TitleText,
"Number of Sales" = @y_YtdSales, "Return Code" = @r_Code
ELSE IF @r_Code = 1
PRINT 'No matching titles in the database. Return code=' + CONVERT(varchar(1),@r_Code)
GO
No hay comentarios:
Publicar un comentario