Especificación de parámetros en un procedimiento almacenado
Se aplica a:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)
Al especificar parámetros de procedimiento, los programas de llamada pueden pasar valores en el cuerpo del procedimiento. Estos valores se pueden usar para distintos fines durante la ejecución del procedimiento. Los parámetros de procedimiento también pueden devolver valores al programa de llamada si el parámetro se marca como OUTPUT.
Un procedimiento puede tener un máximo de 2100 parámetros; cada uno con un nombre, un tipo de datos y una dirección asignados. Opcionalmente, a los parámetros se les pueden asignar valores predeterminados.
En la siguiente sección se proporciona información acerca de cómo pasar valores en parámetros y cómo se usa cada uno de los atributos de parámetro durante una llamada a procedimiento.
Nota
Consulte la AdventureWorks serie de bases de datos de ejemplo para los ejercicios de este artículo. Para más información, vea Bases de datos de ejemplo AdventureWorks.
Pasar valores a parámetros
Los valores de parámetro suministrados con una llamada a procedimiento deben ser constantes o una variable; no se puede usar un nombre de función como valor de parámetro. Las variables pueden ser definidas por el usuario o variables del sistema, como @@spid.
En los siguientes ejemplos se muestra cómo se pasan valores de parámetros al uspGetWhereUsedProductIDdel procedimiento. Ilustran cómo pasar parámetros como constantes y variables y también cómo usar una variable para pasar el valor de una función.
USE AdventureWorks2022;
GO
-- Passing values as constants.
EXEC dbo.uspGetWhereUsedProductID 819, '20050225';
GO
-- Passing values as variables.
DECLARE @ProductID int, @CheckDate datetime;
SET @ProductID = 819;
SET @CheckDate = '20050225';
EXEC dbo.uspGetWhereUsedProductID @ProductID, @CheckDate;
GO
En el ejemplo siguiente se devuelve un error porque una función no se puede pasar como un valor de parámetro.
-- Try to use a function as a parameter value.
-- This produces an error message.
EXEC dbo.uspGetWhereUsedProductID 819, GETDATE();
En su lugar, use una variable para pasar un valor de función al parámetro , como en el ejemplo siguiente:
-- Passing the function value as a variable.
DECLARE @CheckDate datetime;
SET @CheckDate = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;
GO
Especificar nombres de parámetro
Al crear un procedimiento y declarar un nombre de parámetro, dicho nombre debe comenzar con un único carácter @ y debe ser único en el ámbito del procedimiento.
La asignación de nombres de forma explícita a los parámetros y la asignación de los valores adecuados para cada uno en una llamada a procedimiento permite proporcionar los parámetros en cualquier orden. Por ejemplo, si el procedimiento my_proc espera tres parámetros denominados @first, @secondy @third, los valores pasados al procedimiento se pueden asignar a los nombres de parámetro, como : EXECUTE my_proc @second = 2, @first = 1, @third = 3;.
Nota
Si se proporciona un valor de parámetro con el formato @parameter = value, se deben proporcionar todos los parámetros posteriores de esta manera. Si los valores de parámetro no se pasan con el formato @parameter = value, los valores deben proporcionarse en el orden idéntico (de izquierda a derecha) que los parámetros se muestran en la instrucción CREATE PROCEDURE. Se recomienda especificar nombres de parámetro, tanto para mejorar la legibilidad como la compatibilidad con versiones futuras del procedimiento almacenado.
Advertencia
Cualquier parámetro pasado en el formulario @parameter = value con el parámetro mal escrito provocará SQL Server generar un error e impedirá la ejecución del procedimiento.
Especificación de tipos de datos de parámetros
Los parámetros se deben definir con un tipo de datos cuando se declaran en una instrucción CREATE PROCEDURE. El tipo de datos de un parámetro determina el tipo y el rango de valores que se aceptan para él cuando se llama al procedimiento. Por ejemplo, si define un parámetro con un tipo de datos tinyint , solo se aceptan valores numéricos del intervalo comprendido entre 0 y 255 cuando se pasan en dicho parámetro. Se devuelve un error si, para ejecutar un procedimiento, se usa un valor incompatible con el tipo de datos.
Especificar los valores predeterminados del parámetro
Un parámetro se considera opcional si tiene un valor predeterminado especificado cuando se declara. No es necesario proporcionar un valor para un parámetro opcional de una llamada a procedimiento.
El valor predeterminado de un parámetro se usa cuando:
- No existe ningún valor especificado en la llama a procedimiento.
- Se especifica la palabra clave DEFAULT como valor en la llamada a procedimiento.
Nota
Si el valor predeterminado es una cadena de caracteres que contiene espacios en blanco incrustados o signos de puntuación, o si comienza con un número (por ejemplo, 6abc), debe ir entre comillas simples y rectas.
Nota
Los parámetros predeterminados no se admiten en Azure Synapse Analytics o Analytics Platform System (PDW).
Si no se puede especificar ningún valor adecuadamente como valor predeterminado para el parámetro, especifique NULL como predeterminado. Es aconsejable que el procedimiento devuelva un mensaje personalizado si el procedimiento se ejecuta sin un valor para el parámetro.
En el ejemplo siguiente se crea el procedimiento uspGetSalesYTD con un parámetro de entrada, @SalesPerson. NULL se asigna como valor predeterminado para el parámetro y se usa en instrucciones de control de errores para devolver un mensaje de error personalizado para los casos en los que el procedimiento se ejecuta sin un valor para el @SalesPerson parámetro.
USE AdventureWorks2022;
GO
IF OBJECT_ID('Sales.uspGetSalesYTD', 'P') IS NOT NULL
DROP PROCEDURE Sales.uspGetSalesYTD;
GO
CREATE PROCEDURE Sales.uspGetSalesYTD
@SalesPerson nvarchar(50) = NULL -- NULL default value
AS
SET NOCOUNT ON;
-- Validate the @SalesPerson parameter.
IF @SalesPerson IS NULL
BEGIN
PRINT 'ERROR: You must specify the last name of the sales person.'
RETURN
END
-- Get the sales for the specified sales person and
-- assign it to the output parameter.
SELECT SalesYTD
FROM Sales.SalesPerson AS sp
JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID
WHERE LastName = @SalesPerson;
RETURN
GO
En el ejemplo siguiente se ejecuta el procedimiento. La primera instrucción ejecuta el procedimiento sin especificar ningún valor de entrada. Esto hace que las instrucciones de control de errores del procedimiento devuelvan el mensaje de error personalizado. La segunda instrucción proporciona un valor de entrada y devuelve el conjunto de resultados esperado.
-- Run the procedure without specifying an input value.
EXEC Sales.uspGetSalesYTD;
GO
-- Run the procedure with an input value.
EXEC Sales.uspGetSalesYTD N'Blythe';
GO
Aunque se pueden omitir parámetros para los que se han proporcionado valores predeterminados, la lista de parámetros que no aceptan valores NULL solo se puede truncar. Por ejemplo, si un procedimiento tiene cinco parámetros, sin especificar los nombres de parámetro con , @parameter = valuese puede omitir el cuarto y el quinto parámetro. Sin embargo, no se puede omitir el cuarto parámetro siempre que se incluya el quinto parámetro, a menos que los parámetros se proporcionen con el formato @parameter = value.
Especificación de varios parámetros con valores predeterminados
Puede omitir parámetros si especifica los nombres de parámetros. Considere el siguiente procedimiento almacenado con varios parámetros opcionales con NULL valores predeterminados.
USE AdventureWorks2022;
GO
IF OBJECT_ID ( 'Production.uspSearchList', 'P' ) IS NOT NULL
DROP PROCEDURE Production.uspSearchList;
GO
CREATE PROCEDURE Production.uspSearchList
@ListPrice money
, @ProductCategoryID int = NULL -- NULL default value
, @ProductSubcategoryID int = NULL -- NULL default value
, @ProductBusinessEntityID int = NULL -- NULL default value
AS
SET NOCOUNT ON;
SELECT
p.Name, p.Class, p.ListPrice, p.ProductID, pc.Name, psc.Name, v.Name
FROM
Production.Product AS p
INNER JOIN Production.ProductSubCategory AS psc ON p.ProductSubcategoryID = psc.ProductSubcategoryID
INNER JOIN Production.ProductCategory AS pc ON psc.ProductCategoryID = pc.ProductCategoryID
INNER JOIN Purchasing.ProductVendor AS pv ON p.ProductID = pv.ProductID
INNER JOIN Purchasing.Vendor AS v ON pv.BusinessEntityID = v.BusinessEntityID
WHERE (p.ListPrice < @ListPrice)
AND (pc.ProductCategoryID = @ProductCategoryID or @ProductCategoryID IS NULL)
AND (psc.ProductSubcategoryID = @ProductSubcategoryID or @ProductSubcategoryID IS NULL)
AND (pv.BusinessEntityID = @ProductBusinessEntityID or @ProductBusinessEntityID IS NULL);
GO
Puede especificar o omitir parámetros con valores predeterminados, como se muestra en la serie de ejemplos siguientes, siempre que cada uno de ellos se proporcione con su nombre de parámetro con el formato @parameter = value:
--Find all Products with a list price less than 150.00 and in the ProductCategoryID = 4
EXEC Production.uspSearchList @ListPrice = 150, @ProductCategoryID = 4;
--Find all Products with a list price less than 150.00 and in the ProductSubCategoryID = 36
EXEC Production.uspSearchList @ListPrice = 150, @ProductSubCategoryID = 36;
--Find all Products with a list price less than 150.00 and from @ProductBusinessEntityID = 1498
EXEC Production.uspSearchList @ListPrice = 150, @ProductBusinessEntityID = 1498;
--Find all Products with a list price less than 150.00 and in the ProductSubCategoryID = 36 and from @ProductBusinessEntityID = 1498
EXEC Production.uspSearchList @ListPrice = 150, @ProductCategoryID = 4, @ProductBusinessEntityID = 1498;
El ejemplo siguiente no es una sintaxis de T-SQL válida, porque todos los parámetros posteriores se deben proporcionar de la misma manera, una vez proporcionado un nombre de parámetro. Siempre se recomienda proporcionar nombres de parámetro para todos los valores y evita errores y confusión.
EXEC Production.uspSearchList @ListPrice = 150, 4, 1498;
Especificar la dirección del parámetro
La dirección de un parámetro es de entrada, el valor se pasa al cuerpo del procedimiento, o de salida, el procedimiento devuelve un valor al programa de llamada. El valor predeterminado es un parámetro de entrada.
Para especificar un parámetro de salida, se debe indicar la palabra clave OUTPUT en la definición del parámetro en la instrucción CREATE PROCEDURE. El procedimiento devuelve el valor actual del parámetro de salida al programa de llamada cuando se abandona el procedimiento. El programa de llamada también debe usar la palabra clave OUTPUT al ejecutar el procedimiento, a fin de guardar el valor del parámetro en una variable que se pueda usar en el programa de llamada.
En el siguiente ejemplo se crea el procedimiento Production.usp_GetList, que devuelve una lista de productos con precios que no superan un importe especificado. El ejemplo muestra la utilización de varias instrucciones SELECT y varios parámetros OUTPUT. Los parámetros OUTPUT permiten a un procedimiento externo, un proceso por lotes o más de una instrucción Transact-SQL tener acceso a un conjunto de valores durante la ejecución del procedimiento.
USE AdventureWorks2022;
GO
IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL
DROP PROCEDURE Production.uspGetList;
GO
CREATE PROCEDURE Production.uspGetList
@Product varchar(40)
, @MaxPrice money
, @ComparePrice money OUTPUT
, @ListPrice money OUT
AS
SET NOCOUNT ON;
SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
-- Populate the output variable @ListPprice.
SET @ListPrice = (SELECT MAX(p.ListPrice)
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
-- Populate the output variable @compareprice.
SET @ComparePrice = @MaxPrice;
GO
Ejecute usp_GetList para devolver una lista de productos de Adventure Works (Bikes) que cuestan menos de $700. Los parámetros OUTPUT @cost y @compareprices se usan con el lenguaje de control de flujo para devolver un mensaje en la ventana Messages .
Nota
La variable OUTPUT debe definirse durante la creación del procedimiento y también durante el uso de la variable. El nombre del parámetro y de la variable no tienen por qué coincidir. Sin embargo, el tipo de datos y el posicionamiento de parámetros deben coincidir (a menos que @listprice = variable se use).
DECLARE @ComparePrice money, @Cost money ;
EXECUTE Production.uspGetList '%Bikes%', 700,
@ComparePrice OUT,
@Cost OUTPUT
IF @Cost <= @ComparePrice
BEGIN
PRINT 'These products can be purchased for less than
$'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
END
ELSE
PRINT 'The prices for all products in this category exceed
$'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.';
Éste es el conjunto de resultados parciales:
Product List Price
-------------------------------------------------- ------------------
Road-750 Black, 58 539.99
Mountain-500 Silver, 40 564.99
Mountain-500 Silver, 42 564.99
...
Road-750 Black, 48 539.99
Road-750 Black, 52 539.99
(14 row(s) affected)
These items can be purchased for less than $700.00.
Pasos siguientes
Comentarios
Próximamente: A lo largo de 2024 iremos eliminando gradualmente GitHub Issues como mecanismo de comentarios sobre el contenido y lo sustituiremos por un nuevo sistema de comentarios. Para más información, vea: https://aka.ms/ContentUserFeedback.
Enviar y ver comentarios de