Поделиться через


Переменные языка Transact-SQL

Локальная переменная Transact-SQL представляет собой объект, содержащий некоторое значение определенного типа. Переменные обычно используются в пакетах и скриптах:

  • в качестве счетчика цикла;

  • для хранения значения, которое необходимо проверить инструкцией управления потоком;

  • для хранения значения, возвращенного функцией или хранимой процедурой.

ПримечаниеПримечание

Имена некоторых системных функций Transact-SQL начинаются с двух символов «@» (@@). Хотя в предыдущих версиях сервера Microsoft SQL Server @@-функции называются глобальными переменными, они не являются переменными и ведут себя по-другому. Функции, помеченные символами @@, являются системными, и синтаксис их использования соответствует правилам вызова функций.

Следующий скрипт создает небольшую тестовую таблицу из 26 строк. Переменная используется в скрипте в качестве:

  • счетчика цикла для управления количеством вставляемых строк;

  • значения, вставляемого в столбец целочисленного типа;

  • аргумента функции, формирующей строку, которая вставляется в столбец символьного типа:

-- Create the table.
CREATE TABLE TestTable (cola int, colb char(3));
GO
SET NOCOUNT ON;
GO
-- Declare the variable to be used.
DECLARE @MyCounter int;

-- Initialize the variable.
SET @MyCounter = 0;

-- Test the variable to see if the loop is finished.
WHILE (@MyCounter < 26)
BEGIN;
   -- Insert a row into the table.
   INSERT INTO TestTable VALUES
       -- Use the variable to provide the integer value
       -- for cola. Also use it to generate a unique letter
       -- for each row. Use the ASCII function to get the
       -- integer value of 'a'. Add @MyCounter. Use CHAR to
       -- convert the sum back to the character @MyCounter
       -- characters after 'a'.
       (@MyCounter,
        CHAR( ( @MyCounter + ASCII('a') ) )
       );
   -- Increment the variable to count this iteration
   -- of the loop.
   SET @MyCounter = @MyCounter + 1;
END;
GO
SET NOCOUNT OFF;
GO
-- View the data.
SELECT cola, colb
FROM TestTable;
GO
DROP TABLE TestTable;
GO

Объявление переменных в языке Transact-SQL

Инструкция DECLARE инициализирует переменную Transact-SQL следующим образом:

  • Назначение имени. Первым символом имени должен быть одиночный символ @.

  • Назначение длины и типа данных, определяемого системой или пользователем. Для числовых переменных задаются также точность и масштаб. Для переменных типа XML может быть дополнительно задана коллекция схем.

  • Присваивает созданной переменной значение NULL.

Например, следующая инструкция DECLARE создает локальную переменную @mycounter типа данных int.

DECLARE @MyCounter int;

Инструкция DECLARE позволяет объявить несколько локальных переменных одинакового или разного типов через запятую.

Например, следующая инструкция DECLARE создает три локальные переменные, @LastName, @FirstName и @StateProvince, и присваивает каждой из них значение NULL:

DECLARE @LastName nvarchar(30), @FirstName nvarchar(20), @StateProvince nchar(2);

Областью переменной называют диапазон инструкций Transact-SQL, которые могут к ней обращаться. Областью переменной являются все инструкции между ее объявлением и концом пакета или хранимой процедуры, где она объявлена. Например, следующий скрипт содержит синтаксическую ошибку, поскольку переменная объявлена в одном пакете, а используется в другом:

USE AdventureWorks2008R2;
GO
DECLARE @MyVariable int;
SET @MyVariable = 1;
-- Terminate the batch by using the GO keyword.
GO 
-- @MyVariable has gone out of scope and no longer exists.

-- This SELECT statement generates a syntax error because it is
-- no longer legal to reference @MyVariable.
SELECT BusinessEntityID, NationalIDNumber, JobTitle
FROM HumanResources.Employee
WHERE BusinessEntityID = @MyVariable;

Переменные имеют локальную область и доступны только внутри пакета или процедуры, где они объявлены. В следующем примере вложенная область, созданная для выполнения процедуры sp_executesql, не имеет доступа к переменной, объявленной в более высокой области видимости, и возвращает ошибку:

DECLARE @MyVariable int;
SET @MyVariable = 1;
EXECUTE sp_executesql N'SELECT @MyVariable'; -- this produces an error

Присвоение значения переменной в языке Transact-SQL

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

Чтобы присвоить значение переменной при помощи инструкции SET, необходимо указать ее имя и присваиваемое значение. Этот способ присвоения значений переменным является предпочтительным. Например, следующий пакет объявляет две переменные, присваивает им значения и использует их в предложении WHERE инструкции SELECT:

USE AdventureWorks2008R2;
GO
-- Declare two variables.
DECLARE @FirstNameVariable nvarchar(50),
   @PostalCodeVariable nvarchar(15);

-- Set their values.
SET @FirstNameVariable = N'Amy';
SET @PostalCodeVariable = N'BA5 3HX';

-- Use them in the WHERE clause of a SELECT statement.
SELECT LastName, FirstName, JobTitle, City, StateProvinceName, CountryRegionName
FROM HumanResources.vEmployee
WHERE FirstName = @FirstNameVariable
   OR PostalCode = @PostalCodeVariable;
GO

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

USE AdventureWorks2008R2;
GO
DECLARE @EmpIDVariable int;

SELECT @EmpIDVariable = MAX(EmployeeID)
FROM HumanResources.Employee;
GO
ПредупреждениеВнимание!

Когда при выполнении инструкции SELECT переменной присваивается несколько значений, сервер SQL Server не гарантирует порядок вычисления выражений. Обратите внимание, что этот эффект проявляется, только если инструкция присваивает значение переменной.

Если инструкция SELECT возвращает более одной строки, и переменная ссылается на нескалярное выражение, ей присваивается значение, которое возвращается для выражения в последней строке результирующего набора. Например, в следующем пакете переменной @EmpIDVariable присваивается значение идентификатора BusinessEntityID последней возвращенной строки, равное 1:

USE AdventureWorks2008R2;
GO
DECLARE @EmpIDVariable int;

SELECT @EmpIDVariable = BusinessEntityID
FROM HumanResources.Employee
ORDER BY BusinessEntityID DESC;

SELECT @EmpIDVariable;
GO