Я хочу використовувати одне і те ж значення для різних запитів з різних БД

DECLARE @ GLOBAL_VAR_1 INT \u003d Value_1 DECLARE @ GLOBAL_VAR_2 INT \u003d Value_2 USE "DB_1" GO SELECT * FROM "TABLE" WHERE "COL_!" \u003d @ GLOBAL_VAR_1 AND "COL_2" \u003d @ GLOBAL_VAR_2 USE "DB_2" GO SELECT * FROM "TABLE" WHERE "COL_!" \u003d @ GLOBAL_VAR_2

але це дає помилку.

Необхідно оголосити скалярную змінну "@ GLOBAL_VAR_2".

Хто-небудь може запропонувати будь-який спосіб зробити це ...?

11 відповідей

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

: Setvar myvar 10

А потім використовуйте їх в такий спосіб:

$ (Myvar)

Щоб використовувати режим SSQ SQLCMD:

ви не можете оголосити глобальні змінні в SQLServer.

Якщо ви використовуєте Management Studio, ви можете використовувати режим SQLCMD, як зазначено @Lanorkin.

В іншому випадку ви можете використовувати CONTEXT_INFO для зберігання однієї змінної, яку видно під час сеансу і з'єднання, але після цього вона зникне.

Тільки по-справжньому глобальним буде створити глобальну тимчасову таблицю (з ім'ям ## yourTableName) і зберегти там свої змінні, але це також зникне, коли всі з'єднання будуть закриті.

Ви можете спробувати глобальну таблицю:

Create table ## global_var (var1 int, var2 int) USE "DB_1" GO SELECT * FROM "TABLE" WHERE "COL_!" \u003d (Select var1 from ## global_var) AND "COL_2" \u003d @ GLOBAL_VAR_2 USE "DB_2" GO SELECT * FROM "TABLE" WHERE "COL_!" \u003d (Select var2 from ## global_var)

У цьому конкретному прикладі помилка пов'язана з GO після операторів використання. Оператори GO скидають середу, тому для користувача змінних не існує. Вони повинні бути оголошені знову. І відповідь на питання про глобальних змінних - Ні, глобальних змінних не існує, принаймні версії сервера Sql, рівні або до 2008 року. Я не можу гарантувати те ж саме для новіших версій сервера SQL.

З повагою, HINI

Починаючи з SQL Server 2016, новий спосіб обміну інформацією в сеансі представлений через SESSION_CONTEXT і sp_set_session_context.

Ви можете використовувати їх як альтернативу CONTEXT_INFO (), в якій зберігається тільки двійкове значення, обмежене 128 байтами. Крім того, користувач може переписати значення в будь-який час, і використовувати його для перевірки безпеки не дуже добре.

Наступні проблеми вирішуються за допомогою нових утиліт. Ви можете зберігати дані в більш зручному для користувача форматі:

EXEC sp_set_session_context "language", "English"; SELECT SESSION_CONTEXT (N "language");

Крім того, ми можемо помітити його як read-only:

EXEC sp_set_session_context "user_id", 4, @read_only \u003d 1;

Якщо ви спробуєте змінити контекст сеансу read-only, ви отримаєте щось на зразок цього:

Повідомлення 15664, рівень 16, стан 1, процедура sp_set_session_context, рядок 10 Неможливо встановити ключ "user_id" в контексті сеансу. Ключ був встановлений як тільки для цієї сесії.

Спробуйте використовувати; замість GO. У мене це працювало на 2008 R2 версії

DECLARE @ GLOBAL_VAR_1 INT \u003d Value_1; DECLARE @ GLOBAL_VAR_2 INT \u003d Value_2; USE "DB_1"; SELECT * FROM "TABLE" WHERE "COL_!" \u003d @ GLOBAL_VAR_1 AND "COL_2" \u003d @ GLOBAL_VAR_2; USE "DB_2"; SELECT * FROM "TABLE" WHERE "COL_!" \u003d @ GLOBAL_VAR_2;

Подібний результат можна отримати, створивши скалярні функції, які повертають значення змінних. Звичайно, виклики функцій можуть бути дорогими, якщо ви використовуєте їх в запитах, які повертають велику кількість результатів, але якщо ви обмежуєте набір результатів, все повинно бути в порядку. Тут я використовую базу даних, створену тільки для зберігання цих полустатіческіх значень, але ви також можете створювати їх для кожної бази даних. Як бачите, тут немає вхідних змінних, тільки функція з добре названим ім'ям, яка повертає статична значення: якщо ви зміните це значення у функції, воно миттєво зміниться скрізь, де вона використовується (при наступному виклику). \u003e

USE GO CREATE FUNCTION dbo.global_GetStandardFonts () RETURNS NVARCHAR (255) AS BEGIN RETURN "font-family:" Calibri Light "," sans-serif ";" END GO - Usage: SELECT " ... "- Result: ...

Завдання значень змінних

В даний час в мові SQL передбачені два способи завдання значення змінної - для цієї мети можна використовувати оператор SELECT або SET. З точки зору виконуваних функцій ці оператори діють майже однаково, не рахуючи того, що оператор SELECT дозволяє отримати вихідне привласнюється значення з таблиці, зазначеної в операторі SELECT.

Оператор SET зазвичай використовується для завдання значень змінних в такій формі, яка більш часто зустрічається в процедурних мовах. Як типових прикладів застосування цього оператора можна вказати наступні:

SET @b \u003d @a * 1.5

Зверніть увагу на те, що у всіх цих операторах безпосередньо здійснюються операції привласнення, в яких використовуються або явно задані значення, або інші змінні. За допомогою оператора SET неможливо привласнити змінної значення, отримане за допомогою запиту; запит повинен бути виконаний окремо і тільки після цього отриманий результат може бути присвоєно за допомогою оператора SET. Наприклад, спроба виконання такого оператора призводить до помилки:

SET @c \u003d COUNT (*) FROM City

а наступний оператор виконується цілком успішно:

SET @c \u003d (SELECT COUNT (*) FROM City)

Оператор SELECT зазвичай використовується для присвоювання значень змінним, якщо джерелом інформації, яка повинна бути збережена в змінної, є запит. Наприклад, дії, здійснювані в наведеному вище коді, набагато частіше реалізуються за допомогою оператора SELECT:

SELECT @c \u003d COUNT (*) FROM City

Зверніть увагу на те, що даний код трохи зрозуміліше (зокрема, він більш лаконічний, хоча і виконує ті ж дії).

Таким чином, можна, сформулювати наступне загальноприйняте угоду по використанню того чи іншого оператора.

Оператор SET використовується, якщо повинна бути виконана проста операція присвоювання значення змінної, тобто якщо привласнюється значення вже задано явно в формі певного значення або у вигляді якоїсь іншої змінної.

Оператор SELECT застосовується, якщо присвоювання значення змінної повинно бути засноване на запиті.

Використання змінних в запитах SQL

Одним з корисних властивостей мови T-SQL є те, що змінні можуть використовуватися в запитах без необхідності створення складних динамічних рядків, що вбудовуються змінні в програмний код. Динамічний SQL продовжує своє існування, але одиночне значення можна змінити простіше - за допомогою змінної.

Скрізь, де в запиті може використовуватися вираз, може використовуватися і змінна. У наступному прикладі продемонстровано використання змінної в реченні WHERE:

DECLARE @IdProd int;

SET @IdProd \u003d 1;

SELECT

Локальна змінна Transact-SQL є об'єктом, який містить деяке значення певного типу. Змінні зазвичай використовуються в пакетах і скриптах:

в якості лічильника циклу;

для зберігання значення, яке необхідно перевірити інструкцією управління потоком;

для зберігання значення, поверненого функцією або збереженої процедурою.

Наступний скрипт створює невелику тестову таблицю з 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 \u003d 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 \u003d 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 \u003d @MyVariable;

Змінні мають локальну область і доступні тільки всередині пакету або процедури, де вони оголошені. У наступному прикладі вкладена область, створена для виконання процедури sp_executesql, не має доступу до змінної, оголошеної в більш високій області видимості, і повертає помилку:

DECLARE @MyVariable int; SET @MyVariable \u003d 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 \u003d N "Amy"; SET @PostalCodeVariable \u003d 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 \u003d @FirstNameVariable OR PostalCode \u003d @PostalCodeVariable; GO

Змінної можна привласнити значення, вказавши її в списку вибору. Якщо список вибору посилається на змінну, то їй повинно бути присвоєно скалярний значення, або інструкція SELECT повинна повертати тільки один рядок. наприклад:

USE AdventureWorks2008R2; GO DECLARE @EmpIDVariable int; SELECT @EmpIDVariable \u003d MAX (EmployeeID) FROM HumanResources.Employee; GO

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

USE AdventureWorks2008R2; GO DECLARE @EmpIDVariable int; SELECT @EmpIDVariable \u003d BusinessEntityID FROM HumanResources.Employee ORDER BY BusinessEntityID DESC; SELECT @EmpIDVariable; GO

Останнє оновлення: 14.08.2017

Через змінні ми можемо передавати дані в запити. І також ми можемо отримувати дані, які є результатом запитів, в змінні. Наприклад, при вибірці з таблиць за допомогою команди SELECT ми можемо витягувати дані в змінну за допомогою наступного синтаксису:

SELECT @ переменная_1 \u003d спеціфікація_столбца_1, @ переменная_2 \u003d спеціфікація_столбца_2, ...................................... @ переменная_N \u003d спеціфікація_столбца_N

Крім того, в вираженні SET значення, що привласнюється змінної, також може бути результатом команди SELECT.

Наприклад, нехай у нас будуть такі таблиці:

CREATE TABLE Products (Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR (30) NOT NULL, Manufacturer NVARCHAR (20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL); CREATE TABLE Customers (Id INT IDENTITY PRIMARY KEY, FirstName NVARCHAR (30) NOT NULL); CREATE TABLE Orders (Id INT IDENTITY PRIMARY KEY, ProductId INT NOT NULL REFERENCES Products (Id) ON DELETE CASCADE, CustomerId INT NOT NULL REFERENCES Customers (Id) ON DELETE CASCADE, CreatedAt DATE NOT NULL, ProductCount INT DEFAULT 1, Price MONEY NOT NULL );

Використовуємо змінні при отриманні даних:

DECLARE @maxPrice MONEY, @minPrice MONEY, @dif MONEY, @count INT SET @count \u003d (SELECT SUM (ProductCount) FROM Orders); SELECT @ minPrice \u003d MIN (Price), @maxPrice \u003d MAX (Price) FROM Products SET @dif \u003d @maxPrice - @minPrice; PRINT "Всього продано:" + STR (@count, 5) + "товарa (ів)"; PRINT "Різниця між максимальною і мінімальною ціною:" + STR (@dif)

В даному випадку змінна @count буде містити суму всіх значень з шпальти ProductCount таблиці Orders, тобто загальна кількість проданих товарів.

Змінні @min і @max зберігають відповідно мінімальне і максимальне значення стовпця Price з таблиці Products, а змінна @dif - різницю між цими значеннями. І подібно простим значенням, змінні також можуть брати участь в операціях.

Інший приклад:

DECLARE @sum MONEY, @id INT, @prodid INT, @name NVARCHAR (20); SET @ id \u003d 2; SELECT @sum \u003d SUM (Orders.Price * Orders.ProductCount), @ name \u003d Products.ProductName, @prodid \u003d Products.Id FROM Orders INNER JOIN Products ON ProductId \u003d Products.Id GROUP BY Products.ProductName, Products.Id HAVING [Email protected] PRINT "Товар" + @name + "проданий на суму" + STR (@sum)

Тут добуваються дані з двох таблиць Products і Orders групуються за стовпцями Id і ProductName з таблиці Products. Потім дані фільтруються за стовпцем Id з Products. А витягнуті дані потрапляють в змінні @sum, @name, @prodid.

Напевно, одним з перших питань, що виникають у початківців програмістів на T-SQL, це питання "А як отримати вибірку з таблиці, ім'я якої занесено в змінну?"
Оскільки в T-SQL немає можливості використовувати в деяких частинах запиту значення змінних, то єдиним доступним рішенням є використання динамічного запиту. Ідея дуже проста: в спеціально определнной змінної "збирається" рядок запиту, яка повинна виконуватися. Далі цей рядок запускається на виконання. Запуск можна здійснити двома способами
- за допомогою команди EXECUTE
- за допомогою системної процедури sp_executesql.

Виглядає це приблизно так

DECLARE @SQL varchar ( 8000 ), @Table_name varchar ( 10 ) SET @SQL \u003d "SELECT * FROM" + @table_name exec (@SQL) --ілі exec sp_executesql @SQL Зазвичай динамічні запиту формуються всередині збережених процедур, в яких по вхідним параметром складається конкретна рядок виконання.

I.Особенності динамічного запиту
1. Динамічний запит ЗАВЖДИ виконується В ТОМ-Ж Коннект і ЯК ОКРЕМИЙ ПАКЕТ (batch). Іншими словами при використанні такого запиту,
- ви не маєте доступу до локальних змінних, оголошених до виклику динамічного запиту (проте можливий доступ до Створена раніше тимчасовим таблиць)
- локальні тимчасові таблиці і змінні, створені під час виконання команди exec, будуть недоступні в викликає процедурі, тому що будуть видалені після закінчення пакета, в якому виконується exec.

2. Динамічний запит ЗАВЖДИ виконується з ПРАВАМИ КОРИСТУВАЧА, викликаних ПРОЦЕДУРУ, а не з правами власника процедури. Іншими словами, якщо власником процедури Procedure1 є User1, який має права до таблиці Table1, то для користувача User2 мало призначити права на виконання процедури Procedure1, якщо звернення в ній до таблиці Table1 йде через динамічний запит. Доведеться давати йому відповідні права і безпосередньо для Table1.

3. Компіляція запиту відбуваються безпосередньо перед його викликом. Тобто про всі синтаксичних помилках ви дізнаєтеся тільки в цей момент.

II.Особенності використання команда exec
1. Команда exec підтримує до якості аргументу Конкатенация строк і / або змінних. ПЗ не підтримуєте конкатенація результатів виконання функцій, тобто конструкції виду
exec ( "SELECT * FROM" + LEFT (@TableName, 10 )) Заборонені до використання.
2. У команді немає вхідних / вихідних параметрів.

III.Особенності використання процедури sp_executesql
1. Процедура НЕ підтримує в якості параметрів конкатенацію рядків і / або змінних.
2. Текст запиту повинен бути або змінної типу NVARCHAR / NCHAR, або такого ж типу стрінговой константою.
3. Є можливість передачі параметрів в виконуваний скрипт і отримання вихідних значень
Останнє явно в документації не описано, тому ось кілька прикладів

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

declare @ var1 int, @ var2 varchar ( 100 ), @ Var3 varchar ( 100 ), @ Var4 int declare @mysql nvarchar ( 4000 ) Set @mysql \u003d "Set @ var1 \u003d @ var1 + @ var4; set @ var2 \u003d""CCCC" "; set @ var3 \u003d @ var3 +" "dddd" "" set @ var1 \u003d 0 set @ var2 \u003d "BBBB" set @ var3 \u003d "AAAA" set @ var4 \u003d 10 select @ var1, @ var2, @ var3 exec sp_executesql @mysql, N "@ Var1 int out, @ var2 varchar (100) out, @ var3 varchar (100) out, @ var4 int", @ Var1 \u003d @ var1 out, @ var2 \u003d @ var2 out, @ var3 \u003d @ var3 out, @ var4 \u003d @ var4 select @ var1, @ var2, @ var3

В даному прикладі в динамічному запиті відкривається курсор, який доступний в викликає процедурі через вихідну змінну

USE pubs declare @cur cursor exec sp_executesql N "Set @ curvar \u003d cursor local for select top 10 au_id, au_lname, au_fname from authors open @curvar" , N "@curvar cursor output", @ [Email protected] output FETCH NEXT FROM @cur WHILE @@ FETCH_STATUS \u003d 0 BEGIN FETCH NEXT FROM @cur END

Резюме (більш IMHO, ніж обов'язкові вимоги)
Динамічний запит дуже корисна і іноді просто незамінна річ, АЛЕ способи його реалізації і конкретно через виклик в окремому пакеті з правами користувача, який викликав процедуру, принижують його практичне МАСОВЕ застосування.