DECLARE @local_variable (Transact-SQL)
Se aplica a:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Punto de conexión de análisis SQL en Microsoft FabricAlmacenamiento en Microsoft Fabric
Las variables se declaran en el cuerpo de un proceso por lotes o un procedimiento con la instrucción DECLARE, y se les asignan valores con una instrucción SET o SELECT. Las variables de cursor pueden declararse con esta instrucción y utilizarse con otras instrucciones relacionadas con los cursores. Después de la declaración, todas las variables se inicializan como NULL, a menos que se proporcione un valor como parte de la declaración.
Convenciones de sintaxis de Transact-SQL
Sintaxis
La siguiente sintaxis es para SQL Server y Azure SQL Database:
DECLARE
{
{ @local_variable [AS] data_type [ = value ] }
| { @cursor_variable_name CURSOR }
} [ ,...n ]
| { @table_variable_name [AS] <table_type_definition> }
<table_type_definition> ::=
TABLE ( { <column_definition> | <table_constraint> | <table_index> } } [ ,...n ] )
<column_definition> ::=
column_name { scalar_data_type | AS computed_column_expression }
[ COLLATE collation_name ]
[ [ DEFAULT constant_expression ] | IDENTITY [ (seed, increment ) ] ]
[ ROWGUIDCOL ]
[ <column_constraint> ]
[ <column_index> ]
<column_constraint> ::=
{
[ NULL | NOT NULL ]
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor
| WITH ( < index_option > [ ,...n ] )
[ ON { filegroup | "default" } ]
| [ CHECK ( logical_expression ) ] [ ,...n ]
}
<column_index> ::=
INDEX index_name [ CLUSTERED | NONCLUSTERED ]
[ WITH ( <index_option> [ ,... n ] ) ]
[ ON { partition_scheme_name (column_name )
| filegroup_name
| default
}
]
[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
<table_constraint> ::=
{
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
( column_name [ ASC | DESC ] [ ,...n ]
[ WITH FILLFACTOR = fillfactor
| WITH ( <index_option> [ ,...n ] )
| [ CHECK ( logical_expression ) ] [ ,...n ]
}
<table_index> ::=
{
{
INDEX index_name [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ]
(column_name [ ASC | DESC ] [ ,... n ] )
| INDEX index_name CLUSTERED COLUMNSTORE
| INDEX index_name [ NONCLUSTERED ] COLUMNSTORE ( column_name [ ,... n ] )
}
[ WITH ( <index_option> [ ,... n ] ) ]
[ ON { partition_scheme_name ( column_name )
| filegroup_name
| default
}
]
[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
}
<index_option> ::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| STATISTICS_INCREMENTAL = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
| COMPRESSION_DELAY = { 0 | delay [ Minutes ] }
| DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
[ ON PARTITIONS ( { partition_number_expression | <range> }
[ , ...n ] ) ]
| XML_COMPRESSION = { ON | OFF }
[ ON PARTITIONS ( { <partition_number_expression> | <range> }
[ , ...n ] ) ] ]
}
La siguiente sintaxis es para Azure Synapse Analytics, Almacenamiento de datos paralelos y Microsoft Fabric:
DECLARE
{ { @local_variable [AS] data_type } [ = value [ COLLATE <collation_name> ] ] } [ ,...n ]
Nota:
Para ver la sintaxis de Transact-SQL para SQL Server 2014 (12.x) y versiones anteriores, consulte Versiones anteriores de la documentación.
Argumentos
@local_variable
Es el nombre de una variable. Los nombres de variables deben comenzar con un signo de arroba (@). Los nombres de las variables locales deben respetar las reglas de los identificadores.
data_type
Es cualquier tipo de tabla CLR (Common Language Runtime) definido por el usuario o cualquier tipo de datos de alias suministrados por el sistema. Una variable no puede ser de los tipos de datos text, ntext o image.
Para más información sobre los tipos de datos del sistema, vea Tipos de datos (Transact-SQL). Para más información sobre los tipos de datos definidos por el usuario CLR o de alias, vea CREATE TYPE (Transact-SQL).
= value
Asigna un valor a la variable en línea. El valor puede ser una constante o una expresión, pero debe coincidir con el tipo de declaración de la variable o poder convertirse implícitamente a ese tipo. Para más información, vea Expresiones (Transact-SQL).
@cursor_variable_name
Nombre de una variable de cursor. Los nombres de variable de cursor deben comenzar con un signo de arroba (@) y seguir las reglas de los identificadores.
CURSOR
Especifica que la variable es una variable de cursor local.
@table_variable_name
Es el nombre de una variable de tipo table. Los nombres de variable deben comenzar con un signo de arroba (@) y seguir las reglas de los identificadores.
<table_type_definition>
Define el tipo de datos de table. La declaración de tabla incluye definiciones de columna, nombres, tipos de datos y restricciones. Solo se permiten los tipos de restricciones PRIMARY KEY, UNIQUE, NULL y CHECK. Un tipo de datos de alias no puede usarse como tipo de datos escalar de una columna si hay una regla o una definición de valor predeterminado enlazada al tipo.
<table_type_definition>
Es un subconjunto de información que se usa para definir una tabla en CREATE TABLE. Aquí se incluyen los elementos y definiciones fundamentales. Para más información, consulte CREATE TABLE (Transact-SQL).
n
Es un marcador de posición que indica que se pueden especificar y asignar valores a varias variables. Cuando se declara una variable de table, esta debe ser la única variable de table que se declara en la instrucción DECLARE.
column_name
Es el nombre de la columna en la tabla.
scalar_data_type
Especifica que la columna es de un tipo de datos escalar.
computed_column_expression
Es una expresión que define el valor de una columna calculada. Se calcula a partir de una expresión mediante otras columnas de la misma tabla. Por ejemplo, una columna calculada puede tener la definición cost AS price * qty. La expresión puede ser un nombre de columna no calculada, una constante, una función, una variable o cualquier combinación de estos elementos conectados mediante uno o más operadores. La expresión no puede ser una subconsulta ni una función definida por el usuario. La expresión no puede hacer referencia a un tipo CLR definido por el usuario.
[ COLLATE collation_name ]
Especifica la intercalación de la columna. collation_name puede ser un nombre de intercalación de Windows o un nombre de intercalación de SQL, y solo es aplicable a las columnas de los tipos de datos char, varchar, text, nchar, nvarchar y ntext. Si no se especifica, se asignará a la columna la intercalación del tipo de datos definido por el usuario, si la columna es de un tipo de datos definido por el usuario, o la intercalación de la base de datos actual.
Para obtener más información sobre los nombres de intercalación de Windows y SQL, consulte COLLATE (Transact-SQL).
DEFAULT
Especifica el valor suministrado para la columna cuando no se ha especificado explícitamente un valor durante una inserción. Las definiciones DEFAULT se pueden aplicar a cualquier columna excepto a las definidas como timestamp o a aquellas que tengan la propiedad IDENTITY. Las definiciones DEFAULT desaparecen cuando se quita la tabla. Solo se puede utilizar un valor constante como valor predeterminado, por ejemplo, una cadena de caracteres, una función del sistema como SYSTEM_USER() o el valor NULL. Para mantener la compatibilidad con las versiones anteriores de SQL Server, se puede asignar un nombre de restricción a DEFAULT.
constant_expression
Es una constante, el valor NULL o una función del sistema que se utiliza como valor predeterminado de una columna.
IDENTITY
Indica que la nueva columna es una columna de identidad. Cuando se agrega una nueva fila a la tabla, SQL Server proporciona un valor incremental único para la columna. Las columnas de identidad suelen utilizarse con las restricciones PRIMARY KEY como identificadores de fila únicos para la tabla. La propiedad IDENTITY se puede asignar a columnas tinyint, smallint, int, decimal(p,0) o numeric(p,0) . Solo se puede crear una columna de identidad para cada tabla. Las restricciones DEFAULT y los valores predeterminados enlazados no se pueden utilizar con columnas de identidad. Se debe especificar los dos argumentos, seed e increment, o ninguno. Si no se especifica ninguno, el valor predeterminado es (1,1).
seed
Es el valor que se usa para la primera fila cargada en la tabla.
increment
Es el valor incremental que se agrega al valor de identidad de la fila que se había cargado antes.
ROWGUIDCOL
Indica que la nueva columna es una columna de identificador único global de fila. Solo se puede designar una columna uniqueidentifier por tabla como columna ROWGUIDCOL. La propiedad ROWGUIDCOL únicamente se puede asignar a una columna uniqueidentifier.
NULL | NOT NULL
Indica si NULL se permite en la variable. El valor predeterminado es NULL.
PRIMARY KEY
Es una restricción que exige la integridad de entidad de una o varias columnas dadas con un índice único. Solo se puede crear una restricción PRIMARY KEY para cada tabla.
UNIQUE
Es una restricción que proporciona la integridad de entidad de una o varias columnas dadas con un índice único. Las tablas pueden tener varias restricciones UNIQUE.
CLUSTERED | NONCLUSTERED
Indica que se ha creado un índice clúster o no clúster para la restricción PRIMARY KEY o UNIQUE. Las restricciones PRIMARY KEY utilizan CLUSTERED y las restricciones UNIQUE, NONCLUSTERED.
CLUSTERED solamente se puede especificar para una restricción. Si se especifica CLUSTERED para una restricción UNIQUE y también se especifica una restricción PRIMARY KEY, esta última utilizará NONCLUSTERED.
CHECK
Es una restricción que exige la integridad del dominio limitando los valores posibles que se pueden escribir en una o varias columnas.
logical_expression
Es una expresión lógica que devuelve TRUE o FALSE.
<index_option>
Especifica una o varias opciones de índice. En las variables table no se pueden crear índices de forma explícita; en estas variables table tampoco se conserva ninguna estadística. A partir de SQL Server 2014 (12.x), se introdujo una sintaxis nueva que le permite crear determinado tipos de índice alineados con la definición de tabla. Con esta nueva sintaxis, puede crear índices en las variables de tabla como parte de la definición de tabla. En determinados casos, el rendimiento puede mejorar si en su lugar se utilizan tablas temporales, las que proporcionan estadísticas y compatibilidad total del índice.
Si desea obtener una descripción completa de estas opciones, consulte CREATE TABLE.
Variables de tabla y estimaciones de fila
Las variables table no tienen estadísticas de distribución. En muchos casos, el optimizador compilará un plan de consulta sobre el supuesto de que la variable de tabla no tiene filas o una fila. Para más información, revise el tipo de datos de tabla: limitaciones y restricciones.
Por este motivo, las variables table deben usarse con precaución si se espera una gran cantidad de filas (más de 100). Use las siguientes alternativas:
- Las tablas temporales pueden ser una solución mejor que las variables de tabla cuando es posible que el recuento de filas sea mayor (mayor que 100).
- Para las consultas que se unen a la variable de tabla con otras tablas, use la sugerencia RECOMPILE, que hará que el optimizador use la cardinalidad correcta para esta variable.
- En Azure SQL Database y a partir de SQL Server 2019 (15.x), la característica de compilación diferida de variables de tabla propagará las estimaciones de cardinalidad basadas en recuentos reales de filas de variables de tabla, lo que proporciona un recuento de filas más preciso para optimizar el plan de ejecución. Para más información, consulte Procesamiento de consultas inteligente en bases de datos SQL.
Observaciones
Las variables se suelen utilizar en un proceso por lotes o procedimiento como contadores para WHILE, LOOP o un bloque IF…ELSE.
Las variables solo se pueden usar en expresiones y no en lugar de nombres de objeto o palabras clave. Para formar instrucciones SQL dinámicas, utilice EXECUTE.
El alcance de una variable local es el lote en el que está declarada.
Una variable de tabla no tiene que residir necesariamente en la memoria. En condiciones de presión de memoria, las páginas que pertenecen a una variable de tabla se pueden enviar a tempdb.
Puede definir un índice insertado en una variable de tabla.
Se puede hacer referencia como origen a una variable de cursor que actualmente tiene asignado un cursor en una instrucción:
- Instrucción CLOSE
- DEALLOCATE, instrucción
- FETCH, instrucción
- OPEN, instrucción
- Instrucción DELETE o UPDATE posicionada
- Instrucción de variable SET CURSOR (en el lado derecho)
En todas estas instrucciones, SQL Server genera un error si la variable de cursor a la que se hace referencia existe pero actualmente no tiene asignado un cursor. Si una variable de cursor a la que se hace referencia no existe, SQL Server genera el mismo error que para una variable de otro tipo no declarada.
Una variable de cursor:
Puede ser el destino de un tipo de cursor u otra variable de cursor. Para obtener más información, vea SET @local_variable (Transact-SQL).
Se puede hacer referencia a la variable de cursor como el destino de un parámetro de cursor de salida en una instrucción EXECUTE si la variable de cursor no tiene actualmente un cursor asignado.
Se debe considerar como un puntero al cursor.
Ejemplos
A. Usar DECLARE
En el ejemplo siguiente se utiliza una variable local denominada @find para recuperar información de contacto para todos los apellidos que comienzan por Man.
USE AdventureWorks2022;
GO
DECLARE @find VARCHAR(30);
/* Also allowed:
DECLARE @find VARCHAR(30) = 'Man%';
*/
SET @find = 'Man%';
SELECT p.LastName, p.FirstName, ph.PhoneNumber
FROM Person.Person AS p
JOIN Person.PersonPhone AS ph ON p.BusinessEntityID = ph.BusinessEntityID
WHERE LastName LIKE @find;
El conjunto de resultados es el siguiente:
LastName FirstName Phone
------------------- ----------------------- -------------------------
Manchepalli Ajay 1 (11) 500 555-0174
Manek Parul 1 (11) 500 555-0146
Manzanares Tomas 1 (11) 500 555-0178
(3 row(s) affected)
B. Usar DECLARE con dos variables
El ejemplo siguiente recupera los nombres de representantes de ventas de Adventure Works Cycles que se encuentran en la zona de ventas de Norteamérica y tienen, como mínimo, $2.000.000 en ventas anuales.
USE AdventureWorks2022;
GO
SET NOCOUNT ON;
GO
DECLARE @Group nvarchar(50), @Sales MONEY;
SET @Group = N'North America';
SET @Sales = 2000000;
SET NOCOUNT OFF;
SELECT FirstName, LastName, SalesYTD
FROM Sales.vSalesPerson
WHERE TerritoryGroup = @Group and SalesYTD >= @Sales;
C. Declarar una variable de tipo table
El ejemplo siguiente crea una variable table que almacena los valores especificados en la cláusula OUTPUT de la instrucción UPDATE. Las dos instrucciones SELECT que le siguen devuelven los valores en @MyTableVar y los resultados de la operación de actualización en la tabla Employee. Los resultados de la columna INSERTED.ModifiedDate son diferentes de los valores de la columna ModifiedDate de la tabla Employee. Esto se debe a que el desencadenador AFTER UPDATE, que actualiza el valor de ModifiedDate a la fecha actual, se define en la tabla Employee. Sin embargo, las columnas que devuelve OUTPUT reflejan los datos anteriores a la activación de los desencadenadores. Para más información, vea Cláusula OUTPUT (Transact-SQL).
USE AdventureWorks2022;
GO
DECLARE @MyTableVar TABLE (
EmpID INT NOT NULL,
OldVacationHours INT,
NewVacationHours INT,
ModifiedDate DATETIME);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25
OUTPUT INSERTED.BusinessEntityID,
DELETED.VacationHours,
INSERTED.VacationHours,
INSERTED.ModifiedDate
INTO @MyTableVar;
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--Display the result set of the table.
--Note that ModifiedDate reflects the value generated by an
--AFTER UPDATE trigger.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO
D. Declaración de una variable de tipo tabla, con índices insertados
En el ejemplo siguiente, se crea una variable table con un índice insertado agrupado y dos índices insertados no agrupados.
DECLARE @MyTableVar TABLE (
EmpID INT NOT NULL,
PRIMARY KEY CLUSTERED (EmpID),
UNIQUE NONCLUSTERED (EmpID),
INDEX CustomNonClusteredIndex NONCLUSTERED (EmpID)
);
GO
La consulta siguiente devuelve información sobre los índices creados en la consulta anterior.
SELECT *
FROM tempdb.sys.indexes
WHERE object_id < 0;
GO
E. Declarar una variable de tipo de tabla definido por el usuario
El ejemplo siguiente crea un parámetro con valores de tabla o una variable de tabla denominada @LocationTVP. Esto requiere un tipo de tabla definido por el usuario correspondiente denominado LocationTableType. Para más información sobre cómo crear un tipo de tabla definido por el usuario, vea CREATE TYPE (Transact-SQL). Para más información sobre los parámetros con valores de tabla, veaUsar parámetros con valores de tabla (motor de base de datos).
DECLARE @LocationTVP
AS LocationTableType;
Ejemplos: Azure Synapse Analytics y Sistema de la plataforma de análisis (PDW)
F. Usar DECLARE
En el ejemplo siguiente se utiliza una variable local denominada @find para recuperar información de contacto para todos los apellidos que comienzan por Walt.
-- Uses AdventureWorks
DECLARE @find VARCHAR(30);
/* Also allowed:
DECLARE @find VARCHAR(30) = 'Man%';
*/
SET @find = 'Walt%';
SELECT LastName, FirstName, Phone
FROM DimEmployee
WHERE LastName LIKE @find;
G. Usar DECLARE con dos variables
En el siguiente ejemplo se recuperan variables de usuario para especificar el nombre y el apellido de los empleados de la tabla DimEmployee.
-- Uses AdventureWorks
DECLARE @lastName VARCHAR(30), @firstName VARCHAR(30);
SET @lastName = 'Walt%';
SET @firstName = 'Bryan';
SELECT LastName, FirstName, Phone
FROM DimEmployee
WHERE LastName LIKE @lastName AND FirstName LIKE @firstName;
Vea también
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