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

SQL-Ex blog

Переменные SQL в скриптах, функциях, хранимых процедурах, SQLCMD и т.д.

• Что такое переменные?
• Что такое типы переменных?
• Как объявить переменную?
• Как установить переменную?
• Зачем использовать переменные?
• Как использовать переменные в хранимых процедурах?
• Как использовать переменные в функциях?
• Как использовать переменные с SQLCMD?
• Как использовать переменные в скриптах?
• Как использовать переменные с SSIS?

Что такое переменные?

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

Что такое типы переменных?

• Числовые (int, smallint, money, decimal, bigint, numeric, bit, smallmoney, tinyint, float, real)
• Дата и/или время (date, datetime2, datetime, datetimeoffset, smalldatetime, time)
• Символы (char, varchar, text, nchar, nvarchar и ntext)
• Двоичные типы данных, например, image
• XML
• sql_variant, который может поддерживать различные типы данных
• Uniqueidentifier
• Переменные пространственной геометрии и пространственной географии
• Курсоры
• Предложение WHERE
• а также табличные переменные

Как объявить переменную SQL?

Базовый синтаксис объявления переменной следующий:

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

В следующем примере показано, как объявить переменную типа smallint.

Следующий пример показывает, как объявить и установить значение переменной, и использовать ее в операторе SELECT.

Следующий пример объявляет переменную @mytext типа varchar и присваивает ей значение Hello.

Обратите внимание, что результат отображает только первую букву слова Hello. Это происходит потому, что значением по умолчанию типа данных nvarchar или varchar является единственный символ. Если вам нужно больше символов, укажите длину. Следующий пример использует длину 10 символов.

Следующий пример показывает, как объявить переменную типа даты и присвоить ей значение. Затем мы отображаем дату в формате dd-MM-yyyy.

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

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

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

Второй пример присваивает значение непосредственно в операторе DECLARE, как в примерах выше.

Третий пример показывает как объявить переменную, а затем присвоить ей значение с помощью оператора SET во второй строке кода.

Результаты для всех трех примеров будут одинаковы.

Географические переменные

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

Ниже показан результат.

Чтобы больше узнать о типах данных Geography и Geometry, обратитесь к следующей статье.

Табличные переменные

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

Следующий пример создает переменную с именем @TestTabe, а затем вставляет значения и выполняет выборку.

Более подробно о табличных переменных вы можете прочитать в статье.

Зачем использовать переменные SQL?

Переменные в T-SQL позволяют создавать интеллектуальный и динамичный код. Это дает больше возможностей пользователю для получения информации удобным способом, автоматизировать задачи и повторно использовать код. Все языки программирования используют переменные, и T-SQL не является исключением.

Как использовать переменные в хранимых процедурах?

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

В следующем примере показывается, как создать хранимую процедуру с именем dbo.uspGetAddress. Входным параметром является @City, и хранимая процедура выведет все столбцы таблицы Person.address, когда город равен городу, переданному через аргумент процедуры. Выполните этот код в своей базе данных AdventureWorks.

В этом примере значением входного параметра будет New York.

Более подробно о хранимых процедурах можно узнать в статье.

Как использовать переменные в функциях SQL?

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

Следующий пример показывает вызов этой функции.

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

SQLCMD — это командная строка SQL Server. Если вам нравится автоматизировать задачи с помощью bat-файлов, SQLCMD — это то, что вам нужно. Если вы не знакомы с SQLCMD, но хотите познакомиться, обратитесь к следующей статье.

В командной строке Windows выполните эту команду для подключения к SQL, используя аккаунт Windows.

В sqlcmd перейдем к базе данных AdventureWorks, как показано ниже.

1> use adventureworks2019
2> go

Следующий пример установит переменную с именем tablename и значением humanresources.department, а затем сделает выборку из этой переменной.

1> :setvar tablename humanresources.department
1> select * from $(tablename)
2> go

Если все правильно, вы увидите данные в этой таблице.

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

Вы можете также передать переменные в скрипт. Следующий пример создаст переменную с именем columnname1. Мы присвоим ей значение DepartmentID.

В примере мы создадим скрипт с именем myquery.sqlЮ который сохраним в папке sqlcmd.

USE AdventureWorks2019
GO
SELECT
$(columnname1)
from
[HumanResources].[Department]

Выйдите из sqlcmd, если вы еще находитесь там, и в командной строке Windows выполните команду:

sqlcmd -v columnname1 =DepartmentID -i c:\sqlcmd\myquery.sql

В командной строке будет выполнен оператор «select departmentid from humanresources.department».

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

Переменные SSIS совершенно отличаются от переменных T-SQL. SSIS — это графическая среда, используемая для интеграции данных и которая имеет свои собственные переменные. Чтобы увидеть переменные в проекте SSIS, пройдите в Extensions > SSIS >Variables или для более ранних версий VS — SSIS > Variables.

Вы можете добавлять, удалять переменные на панели variables.

Существуют различные типы переменных в SSIS, например, Int32, int64, Object и Sbyte.

Declare Variables SQL Server

В SQL Server (Transact-SQL) переменная позволяет программисту временно хранить данные во время выполнения кода.

Синтаксис

Синтаксис объявления переменных в SQL Server с помощью оператора DECLARE:

Параметры или аргументы

variable_name — имя для назначения переменной.
datatype — тип данных для назначения переменной.
initial_value — необязательный. Это значение, которое первоначально было присвоено переменной при ее объявлении.

SQL Variables: SQL Server Declare, Set and Select Variable

In MS SQL, variables are the object which acts as a placeholder to a memory location. Variable hold single data value.

In this tutorial you will learn:

Variable Types in SQL: Local, Global

MS SQL has two types of variables:

1. Local variable
2. Global variable.

However, the user can only create a local variable.

Below figure explain two types of variable available in MS SQL server.

Local variable:

• A user declares the local variable.
• By default, a local variable starts with @.
• Every local variable scope has the restriction to the current batch or procedure within any given session.

Global variable:

• The system maintains the global variable. A user cannot declare them.
• The global variable starts with @@
• It stores session related information.

How to DECLARE a variable in SQL

• Before using any variable in batch or procedure, you need to declare the variable.
• DECLARE command is used to DECLARE variable which acts as a placeholder for the memory location.
• Only once the declaration is made, a variable can be used in the subsequent part of batch or procedure.

TSQL Syntax:

Rules:

• Initialization is an optional thing while declaring.
• By default, DECLARE initializes variable to NULL.
• Using the keyword ‘AS’ is optional.
• To declare more than one local variable, use a comma after the first local variable definition, and then define the next local variable name and data type.

Examples of Declaring a variable:

Query: With ‘AS’

Query: Without ‘AS’

Query: DECLARE two variables

Assigning a value to SQL Variable

You can assign a value to a variable in the following three ways:

1. During variable declaration using DECLARE keyword.
2. Using SET
3. Using SELECT

Let’s have a look at all three ways in detail:

During variable declaration using DECLARE keyword

T-SQL Syntax:

Here, after datatype we can use ‘=’ followed by value to be assigned

Query:

Using SQL SET VARIABLE

Sometimes we want to keep declaration and initialization separate. SET can be used to assign values to the variable, post declaring a variable.Below are the different ways to assign values using SET:

Example: Assigning a value to a variable using SET

Syntax:

Query:

Example: Assign a value to multiple variables using SET.

Syntax:

Rule: One SET Keyword can be used to assign a value to only one variable.

Query:

Example: Assigning a value to a variable with a Scalar Subquery using SET

Syntax:

Rules:

• Enclose the query in parenthesis.
• The query should be a scalar query. A scalar query is a query with results as just one row and one column. Otherwise, the query will throw an error.
• If the query returns zero rows, then the variable is set to EMPTY, i.e., NULL.

Assumption: Assume that we have the table as ‘Guru99’ with two columns as displayed below:

We will use ‘Guru99’ table in the further tutorials

Example 1: When subquery return one row as a result.

Example 2: When subquery returns zero row as a result

In this particular case, the variable value is EMPTY, i.e., NULL.

Using SQL SELECT VARIABLE

Just like SET, we can also use SELECT to assign values to the variables, post declaring a variable using DECLARE. Below are different ways to assign a value using SELECT:

Example: Assigning a value to a variable using SELECT

Syntax:

Query:

Example: Assigning a value to multiple variable using SELECT

Syntax:

Rules: Unlike SET, SELECT can be used to assign a value to multiple variables separated by the comma.

Example: Assigning the value to a variable with a Subquery using SELECT

Syntax:

Rules:

• Enclose the query in Parenthesis.
• The query should be a scalar query. The scalar query is the query with the result as one row and one column. Otherwise, the query will throw an error.
• If the query returns zero rows, then the variable is EMPTY, i.e., NULL.
• Reconsider our ‘Guru99’ table

Example 1: When subquery return one row as a result.

Example 2: When subquery return zero row as a result

In this particular case, the variable is to EMPTY, i.e., NULL.

Example 3: Assign a value to a variable with a regular SELECT statement.

Syntax:

Rules:

• Unlike SET, if the query results in multiple rows then the variable value is set to the value of the last row.
• If the query returns zero rows, then the variable is set to EMPTY, i.e., NULL.

Query 1: The query returns one row.

Query 2: The query returns multiple rows.

In this special case, variable value is set to the value of the last row.

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

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

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

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

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

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

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

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

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