DECLARE @local_variable (Transact-SQL)
Las variables se declaran en el cuerpo de un lote 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.
.gif "Icono de vínculo a temas")
Sintaxis
DECLARE
{
{{ @local_variable [AS] data_type } | [ =value ] }
| { @cursor_variable_name CURSOR }
} [,...n]
| { @table_variable_name [AS] <table_type_definition> | <user-defined table type> }
<table_type_definition> ::=
TABLE ( { <column_definition> | <table_constraint> } [ ,... ]
) <column_definition> ::=
column_name { scalar_data_type | AS computed_column_expression }
[ COLLATE collation_name ]
[ [ DEFAULT constant_expression ] | IDENTITY [ (seed ,increment ) ] ]
[ ROWGUIDCOL ]
[ <column_constraint> ]
<column_constraint> ::=
{ [ NULL | NOT NULL ]
| [ PRIMARY KEY | UNIQUE ]
| CHECK (logical_expression )
| WITH ( < index_option > )
}
<table_constraint> ::=
{ { PRIMARY KEY | UNIQUE } ( column_name [ ,... ] )
| CHECK (search_condition )
}
<index_option> ::=
See CREATE TABLE for index option syntax.
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 se deben ajustar a las reglas de los identificadores.data_type
Es cualquier tipo de tabla definido por el usuario CLR (Common Language Runtime) o tipo de datos de alias, suministrado por el sistema. Una variable no puede ser del tipo de datos text, ntext ni image.Para obtener más información acerca de los tipos de datos del sistema, vea Tipos de datos (Transact-SQL). Para obtener 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.@cursor_variable_name
Es el 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 table. La declaración de tabla incluye definiciones de columna, nombres, tipos de datos y restricciones. Sólo se permiten los tipos de restricciones PRIMARY KEY, UNIQUE, NULL y CHECK. Un tipo de datos de alias no puede usarse como un tipo de datos de columna escalar si una regla o definición de valor predeterminado está enlazada al tipo.<table_type_definition> es un subconjunto de información que se utiliza para definir una tabla en CREATE TABLE. Aquí se incluyen los elementos y definiciones fundamentales. Para obtener más información, vea 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 declaran variables table, la variable table debe ser la única que se declare en la instrucción DECLARE.column_name
Es el nombre de la columna de 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 integrada, una variable o cualquier combinación de estos elementos conectados mediante uno o más operadores. La expresión no puede ser una subconsulta o 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 sólo 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 acerca de los nombres de intercalación de Windows y de SQL, vea 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. Sólo 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 el 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 se utilizan normalmente junto con restricciones PRIMARY KEY para que actúen como identificador exclusivo de fila para la tabla. La propiedad IDENTITY se puede asignar a columnas tinyint, smallint, int, decimal(p,0) o numeric(p,0). Sólo se puede crear una columna de identidad para cada tabla. Las restricciones DEFAULT y los valores predeterminados enlazados no se pueden utilizar en las 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 utiliza para la primera fila cargada en la tabla.increment
Se trata del valor incremental que se agrega al valor de identidad de la anterior fila cargada.ROWGUIDCOL
Indica que la nueva columna es una columna de identificador único global de fila. Sólo 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
Son palabras clave que determinan si se permiten o no valores NULL en la columna.PRIMARY KEY
Es una restricción que exige la integridad de entidad para una o varias columnas dadas a través de un índice único. Sólo se puede crear una restricción PRIMARY KEY para cada tabla.UNIQUE
Es una restricción que proporciona la integridad de entidad para una o varias columnas dadas a través de un índice único. Las tablas pueden tener varias restricciones UNIQUE.CHECK
Es una restricción que exige la integridad del dominio al limitar 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.<user-defined table type>
Especifica que la variable es un tipo de tabla definido por el usuario.
Comentarios
Las variables se suelen utilizar en un proceso por lotes o procedimiento como contadores para WHILE, LOOP o un bloque IF…ELSE.
Las variables sólo se pueden utilizar 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.
Se puede hacer referencia como origen a una variable de cursor que actualmente tiene asignado un cursor en una instrucción:
CLOSE.
DEALLOCATE.
FETCH.
OPEN.
DELETE o UPDATE por posición.
SET CURSOR variable (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 genera para una variable no declarada de otro tipo.
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. Para obtener más información sobre variables de cursor, vea Cursores de Transact-SQL.
Ejemplos
A. Utilizar DECLARE
En el ejemplo siguiente se emplea una variable local denominada @find para recuperar información de contacto para todos los apellidos que comienzan por Man.
USE AdventureWorks2008R2;
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 p
JOIN Person.PersonPhone ph
ON p.BusinessEntityID = ph.BusinessEntityID
WHERE LastName LIKE 'Man%';
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. Utilizar DECLARE con dos variables
En el ejemplo siguiente se recuperan los nombres de los representantes de ventas de Adventure Works Cycles que se encuentran en la zona de ventas de Norteamérica y tienen 2 millones de dólares en ventas anuales como mínimo.
USE AdventureWorks2008R2;
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. Tenga en cuenta que 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 obtener más información, vea OUTPUT (cláusula de Transact-SQL).
USE AdventureWorks2008R2;
GO
DECLARE @MyTableVar table(
EmpID int NOT NULL,
OldVacationHours int,
NewVacationHours int,
ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
ModifiedDate = GETDATE()
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.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO
D. 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 obtener más información acerca de cómo crear un tipo de tabla definido por el usuario, vea CREATE TYPE (Transact-SQL). Para obtener más información acerca de los parámetros con valores de tabla, vea Parámetros de valores de tabla (motor de base de datos).
DECLARE @LocationTVP
AS LocationTableType;