DECLARE @local_variable (Transact-SQL)

Переменные объявляются в теле пакета или процедуры при помощи инструкции DECLARE, а значения им присваиваются при помощи инструкций SET или SELECT. При помощи этой инструкции можно объявлять переменные курсоров для использования в других инструкциях. После объявления все переменные инициализируются значением NULL, если иное значение не предоставляется как часть объявления.

Значок ссылки на разделСинтаксические обозначения в Transact-SQL

Синтаксис

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 ) 
     } 

<table_constraint> ::= 
     { { PRIMARY KEY | UNIQUE } ( column_name [ ,... ] ) 
     | CHECK ( search_condition ) 
     } 

Аргументы

  • **@**local_variable
    Имя переменной. Имена переменных должны начинаться с символа @. Имена локальных переменных должны соответствовать правилам для идентификаторов.

  • data_type
    Любой системный тип данных, определяемый пользователем табличный тип CLR или псевдоним типа данных. Переменная не может принадлежать к типу данных text, ntext или image.

    Дополнительные сведения о системных типах данных см. в разделе Типы данных (Transact-SQL). Дополнительные сведения об определяемых пользователем типах данных CLR или о псевдонимах типов данных см. в разделе CREATE TYPE (Transact-SQL).

  • = value
    Подставляет значение переменной. Значение может быть константой или выражением, но должно совпадать с объявленным типом переменной или явно преобразовываться в этот тип.

  • **@**cursor_variable_name
    Имя переменной курсора. Имена переменных курсора должны начинаться с символа @ и должны соответствовать правилам именования идентификаторов.

  • CURSOR
    Указывает, что переменная является локальной переменной курсора.

  • **@**table_variable_name
    Имя переменной типа table. Имена переменных должны начинаться с символа @ и соответствовать правилам именования идентификаторов.

  • <table_type_definition>
    Определяет тип данных table. Объявление таблицы включает в себя определения столбцов, имен, типов данных и ограничений. Допустимы только ограничения PRIMARY KEY, UNIQUE, NULL и CHECK. Псевдоним типа данных не может использоваться как скалярный тип данных столбца, если с этим столбцом связано правило или значение по умолчанию.

    Аргумент <table_type_definition> представляет собой подмножество данных, используемых для определения таблицы в инструкции CREATE TABLE. Сюда включены элементы и наиболее существенные определения. Дополнительные сведения см. в разделе Инструкция CREATE TABLE (Transact-SQL).

  • n
    Заполнитель, указывающий на то, что могут быть заданы несколько переменных и им могут быть присвоены значения. При объявлении переменных типа table в инструкции DECLARE единственной объявляемой переменной должна быть переменная типа table.

  • column_name
    Имя столбца таблицы.

  • scalar_data_type
    Указывает, что столбец имеет скалярный тип данных.

  • computed_column_expression
    Выражение, определяющее значение вычисляемого столбца. Значение вычисляется из выражения при помощи других столбцов той же таблицы. Например, вычисляемый столбец может иметь определение cost AS price * qty. Выражение может быть именем невычисляемого столбца, константой, встроенной функцией, переменной или любым их сочетанием, созданным с помощью одного или нескольких операторов. Выражение не может быть вложенным запросом или пользовательской функцией. Выражение не может ссылаться на определяемый пользователем тип данных CLR.

  • [ COLLATE collation_name]
    Задает параметры сортировки для столбца. Аргумент collation_name может быть именем параметров сортировки Windows либо именем параметров сортировки SQL; применим он только к столбцам типов данных char, varchar, text, nchar, nvarchar и ntext. Если этот аргумент не указан, столбцу назначаются либо параметры сортировки определяемого пользователем типа данных (если столбец принадлежит к определяемому пользователем типу данных), либо параметры сортировки текущей базы данных.

    Дополнительные сведения об именах параметров сортировки Windows и SQL см. в разделе COLLATE (Transact-SQL).

  • DEFAULT
    Указывает значение, записывающееся в столбец, если оно не задано явно при вставке. Определения DEFAULT могут применяться к любым столбцам, кроме имеющих тип timestamp или обладающих свойством IDENTITY. Определения DEFAULT удаляются при удалении таблицы. По умолчанию могут использоваться только константные значения, например символьные строки, системные функции, например SYSTEM_USER(), или NULL. Для сохранения совместимости с более ранними версиями сервера SQL Server значению DEFAULT может быть присвоено имя ограничения.

  • constant_expression
    Константа, NULL или системная функция, используемые в качестве значения по умолчанию для столбца.

  • IDENTITY
    Указывает, что новый столбец является столбцом идентификаторов. При добавлении в таблицу новой строки сервер SQL Server сохраняет в этот столбец уникальное добавочное значение. Столбцы идентификаторов наиболее часто используются в сочетании с ограничениями PRIMARY KEY для выполнения функции уникального идентификатора строки таблицы. Свойство IDENTITY может быть присвоено столбцам, имеющим типы tinyint, smallint, int, decimal(p,0) и numeric(p,0). В каждой таблице может быть создан только один столбец идентификаторов. Связанные аргументы по умолчанию и ограничения DEFAULT со столбцом идентификаторов использоваться не могут. Необходимо указывать либо оба значения seed и increment, либо ни то, ни другое. Если ничего не указано, применяется значение по умолчанию (1,1).

  • seed
    Значение, используемое для самой первой строки, загружаемой в таблицу.

  • increment
    Значение, добавляемое к значению идентификатора предыдущей загруженной строки.

  • ROWGUIDCOL
    Указывает, что новый столбец является столбцом глобального уникального идентификатора строки. В каждой таблице свойство ROWGUIDCOL можно назначить только одному столбцу uniqueidentifier. Свойство ROWGUIDCOL может быть присвоено только столбцу uniqueidentifier.

  • NULL | NOT NULL
    Ключевые слова, определяющие, допустимы ли в столбце значения NULL.

  • PRIMARY KEY
    Ограничение, которое с помощью уникального индекса требует целостности сущностей для данного столбца или столбцов. В каждой таблице может быть создано только одно ограничение PRIMARY KEY.

  • UNIQUE
    Ограничение, которое с помощью уникального индекса обеспечивает целостность сущностей для данного столбца или столбцов. В таблице может быть несколько ограничений UNIQUE.

  • CHECK
    Ограничение, требующее целостности домена путем ограничения возможных значений, которые могут быть введены в столбец или столбцы.

  • logical_expression
    Логическое выражение, возвращающее значения TRUE или FALSE.

  • <определяемый пользователем табличный тип>
    Указывает, что переменная является определяемым пользователем табличным типом.

Замечания

Переменные часто используются в пакете или процедуре в качестве счетчиков для циклов WHILE, LOOP или в блоке IF…ELSE.

Переменные могут использоваться только в выражениях, но не вместо имен объектов или ключевых слов. Для построения динамических инструкций SQL используйте EXECUTE.

Областью локальной переменной является пакет, в котором она объявлена.

На переменную курсора, которая в настоящее время содержит назначенный ей курсор, можно ссылаться в качестве источника из:

  • инструкции CLOSE;

  • инструкции DEALLOCATE;

  • инструкции FETCH;

  • инструкции OPEN;

  • позиционированных инструкций DELETE или UPDATE;

  • инструкции SET CURSOR с использованием переменных (в правой части).

Во всех этих инструкциях SQL Server формирует ошибку, если переменная курсора, на которую они ссылаются, существует, но не содержит курсор, назначенный ей в настоящее время. Если переменная курсора, на которую производится ссылка, не существует, сервер SQL Server формирует ту же ошибку, что и для необъявленной переменной другого типа.

Переменная курсора:

  • Может быть целью типа курсора или другой переменной курсора. Дополнительные сведения см. в разделе SET @local\_variable (Transact-SQL).

  • Может быть объектом ссылки в качестве цели выходного параметра курсора в инструкции EXECUTE, если эта переменная не содержит курсора, назначенного ей в настоящее время.

  • Должна рассматриваться в качестве указателя на курсор. Дополнительные сведения о переменных курсора см. в разделе Курсоры языка Transact-SQL.

Примеры

A. Использование инструкции DECLARE

В следующем примере локальная переменная с именем @find используется для получения контактных данных для лиц с фамилией, начинающейся на Man.

USE AdventureWorks;
GO
DECLARE @find varchar(30);
/* Also allowed: 
DECLARE @find varchar(30) = 'Man%';
*/
SET @find = 'Man%';
SELECT LastName, FirstName, Phone
FROM Person.Contact
WHERE LastName LIKE @find;

Ниже приводится результирующий набор.

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)

Б. Использование инструкции DECLARE с двумя переменными

В следующем примере возвращаются имена коммерческих представителей компании Adventure Works Cycles, находящихся в Северной Америке и имеющих объемы продаж на сумму не менее 2 000 000 долларов в год.

USE AdventureWorks;
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;

В. Объявление переменной типа table

В следующем примере создается переменная типа table, в которой хранятся значения, задаваемые в предложении OUTPUT инструкции UPDATE. Затем следуют две инструкции SELECT, которые возвращают значения в таблицу @MyTableVar, а результаты операции обновления — в таблицу Employee. Заметьте, что результаты в столбце INSERTED.ModifiedDate отличны от значений в столбце ModifiedDate таблицы Employee . Это связано с тем, что для таблицы Employee определен триггер AFTER UPDATE, обновляющий значение ModifiedDate до текущей даты. Однако столбцы, возвращаемые из OUTPUT, отражают данные перед срабатыванием триггеров. Дополнительные сведения см. в разделе Предложение OUTPUT (Transact-SQL).

USE AdventureWorks;
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.EmployeeID,
       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) EmployeeID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

Г. Объявление переменной определяемого пользователем табличного типа

Следующий пример демонстрирует создание параметра, возвращающего табличное значение, или табличной переменной с именем @LocationTVP. Здесь требуется соответствующий определяемый пользователем табличный тип с именем LocationTableType. Дополнительные сведения о создании определяемого пользователем табличного типа см. в разделе CREATE TYPE (Transact-SQL). Дополнительные сведения о возвращающих табличное значение параметрах см. в разделе Возвращающие табличное значение параметры (компонент Database Engine).

DECLARE @LocationTVP 
AS LocationTableType;