Как объявить скалярную переменную в sql

Как объявить скалярную переменную в sql

Для создания сложных запросов, выполняемых в несколько этапов, в SQL предусмотрены переменные.

В переменных могут сохраняться результаты вычисления встроенных функций и констант.

Для использования переменной, ее надо предварительно « объявить » , выполнив команду DECLARE языка SQL. Команда DECLARE имеет следующий формат: DECLARE , где — это название объявляемой переменной, а — тип данных, хранимых в ней. Названия переменных начинаются с символа « @ » , после которого могут использоваться буквы латинского алфавита, цифры и знаки « @ » , « $ » , « % » , « & » , « ~ » , « _ » .

Поддерживаются следующие основные типы данных:

• Geometry — пространственный объект;
• BigInt — целочисленное значение в диапазоне от -2 63 -1 до 2 63 (от — 9,223,372,036,854,775,808 по +9,223,372,036,854,775,807 );
• Int — целочисленное значение в диапазоне от -2 31 ( -2,147,483,648 ) to 2 31 -1 ( 2,147,483,647 );
• SmallInt — целочисленное значение в диапазоне от -2 15 ( -32,768 ) to 2 15 -1 ( 32,767 );
• Double , Float , Real — число с плавающей точкой;
• char , varchar — строковое значение.

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

Использование переменных

DECLARE @i Int; SET @i = 2+2; SELECT @i*3

В примере объявляется переменная @g типа Int . Для переменной задается значение — результат вычисления выражения 2+2 , после чего значение переменной умноженной на 3 выводится в область ответа с помощью ключевого слова SELECT .

Переменные (Transact-SQL)

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

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

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

Следующий скрипт создает небольшую тестовую таблицу из 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 AdventureWorks2022; 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 AdventureWorks2022; 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 AdventureWorks2022; GO DECLARE @EmpIDVariable INT; SELECT @EmpIDVariable = MAX(EmployeeID) FROM HumanResources.Employee; GO 

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

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

USE AdventureWorks2022; GO DECLARE @EmpIDVariable INT; SELECT @EmpIDVariable = BusinessEntityID FROM HumanResources.Employee ORDER BY BusinessEntityID DESC; SELECT @EmpIDVariable; GO 

Как правильно написать SQL запрос с использованием переменной?

Задача: разделить данные за сегодня и вчера по столбцам. Если использовать этот запрос без переменных то все работает. Анализ синтаксиса в excel пишет "необходимо объявить скалярную переменную @today" хотя я вроде его объявил в начале Используется MSSQL 2016

DECLARE @today as Date, @yesterday as Date; Set @today = convert(date, getdate()); Set @yesterday = convert(date, dateadd(day, -1, getdate())); SELECT n.Name, o.Created, Count(DISTINCT(CASE WHEN Status = 'N' And o.Date = @today Then ID END)) as NewQ, Count(DISTINCT(CASE WHEN Status = 'N' And o.Date = @yesterday Then ID END)) as YdNewQ, Count(DISTINCT(CASE WHEN Status = 'W' And o.Date = @today Then ID END)) as WaitingQ, Count(DISTINCT(CASE WHEN Status = 'W' And o.Date = @yesterday Then ID END)) as YDWaitingQ, Count(DISTINCT(CASE WHEN Status = 'U' And o.Date = @today Then ID END)) as ProblemQ, Count(DISTINCT(CASE WHEN Status = 'U' And o.Date = @yesterday Then ID END)) as YdProblemQ, Count(DISTINCT(CASE WHEN Status = 'Z' And o.Date = @today Then ID END)) as CancelledQ, Count(DISTINCT(CASE WHEN Status = 'Z' And o.Date = @yesterday Then ID END)) as YdCancelledQ FROM Orders i LEFT JOIN OrderItems o ON o.OrderID = i.ID LEFT JOIN NomenclUS m ON m.ID = o.ProductID WHERE i.Status <> 'Z' AND o.Created >= dateadd(day, -2, getdate()) GROUP BY o.Created, n.CatID, n.CatName ORDER BY o.Created, n.CatID

• Вопрос задан более трёх лет назад
• 985 просмотров

Комментировать
Решения вопроса 1

Senior Developer

У вас в запросе пропущена секция from, т.е. select с полями есть и where с условиями есть, а вот из каких таблиц
это все выбрать нет.

Переменные у вас объявлены правильно и если выполнить первые три строки запроса, то ошибок не будет.

Ответ написан более трёх лет назад
WebAnalytics1 @WebAnalytics1 Автор вопроса
From я удалил когда создавал вопрос
WebAnalytics1 @WebAnalytics1 Автор вопроса
Если сделать

Count(DISTINCT(CASE WHEN Status = 'N' And o.Date = convert(date, getdate()) Then ID END)) as NewQ, Count(DISTINCT(CASE WHEN Status = 'N' And o.Date = convert(date, dateadd(day, -1, getdate())) Then ID END)) as YdNewQ,

То все нормально работает

WebAnalytics1, странно все это, но вот если в вашем запросе правильно расставить алиасы у полей, то все работает, я вот такой запрос проверял:

DECLARE @today as Date, @yesterday as Date; Set @today = convert(date, getdate()); Set @yesterday = convert(date, dateadd(day, -1, getdate())); SELECT m.Name, o.Created, Count(DISTINCT(CASE WHEN Status = 'N' And o.Date = @today Then i.ID END)) as NewQ, Count(DISTINCT(CASE WHEN Status = 'N' And o.Date = @yesterday Then i.ID END)) as YdNewQ, Count(DISTINCT(CASE WHEN Status = 'W' And o.Date = @today Then i.ID END)) as WaitingQ, Count(DISTINCT(CASE WHEN Status = 'W' And o.Date = @yesterday Then i.ID END)) as YDWaitingQ, Count(DISTINCT(CASE WHEN Status = 'U' And o.Date = @today Then i.ID END)) as ProblemQ, Count(DISTINCT(CASE WHEN Status = 'U' And o.Date = @yesterday Then i.ID END)) as YdProblemQ, Count(DISTINCT(CASE WHEN Status = 'Z' And o.Date = @today Then i.ID END)) as CancelledQ, Count(DISTINCT(CASE WHEN Status = 'Z' And o.Date = @yesterday Then i.ID END)) as YdCancelledQ FROM Orders i LEFT JOIN OrderItems o ON o.OrderID = i.ID LEFT JOIN NomenclUS m ON m.ID = o.ProductID WHERE i.Status <> 'Z' AND o.Created >= dateadd(day, -2, getdate()) GROUP BY o.Created, m.CatID, m.CatName,m.Name ORDER BY o.Created, m.CatID

Для эксперимента вот такие таблицы создал:
create table Orders (ID int, Status varchar(1))
go
create table OrderItems (Created datetime, Date datetime, OrderID int, ProductID int)
go
create table NomenclUS(ID int, CatID int, CatName varchar(20), Name varchar(20))
go

Переменные и управляющие конструкции

Переменная представляет именованный объект, который хранит некоторое значение. Для определения переменных применяется выражение DECLARE , после которого указывается название и тип переменной. При этом название локальной переменной должно начинаться с символа @ :

DECLARE @название_переменной тип_данных

Например, определим переменную name, которая будет иметь тип NVARCHAR:

DECLARE @name NVARCHAR(20)

Также можно определить через запятую сразу несколько переменных:

DECLARE @name NVARCHAR(20), @age INT

С помощью выражения SET можно присвоить переменной некоторое значение:

DECLARE @name NVARCHAR(20), @age INT; SET @name='Tom'; SET @age = 18;

Так как @name предоставляет тип NVARCHAR, то есть строку, то этой переменной соответственно и присваивается строка. А переменной @age присваивается число, так как она представляет тип INT.

Выражение PRINT возвращает сообщение клиенту. Например:

PRINT 'Hello World'

И с его помощью мы можем вывести значение переменной:

DECLARE @name NVARCHAR(20), @age INT; SET @name='Tom'; SET @age = 18; PRINT 'Name: ' + @name; PRINT 'Age: ' + CONVERT(CHAR, @age);

При выполнении скрипта внизу SQL Server Management Studio отобразится значение переменных:

Также можно использовать для получения значения команду SELECT :

DECLARE @name NVARCHAR(20), @age INT; SET @name='Tom'; SET @age = 18; SELECT @name, @age;