Contents

1 Схема элементов синтаксиса MS SQL
2 Введение основы T-SQL и примеры, которые будут разобраны
3 Немного о циклах, условиях в T-SQL
- 3.1 IF … ELSE
- 3.2 Использование ELSE
4 Временная таблица и табличная переменная
5 Переменные
6 Использование TRY … CATCH — обработка ошибок
7 Использование цикла WHILE
8 Курсор
9 WITH конструкция в MS SQL
- 9.1 Схема использования конструкции WITH в VIEW (во вьюхе)
10 Триггеры
11 Хранимые процедуры
- 11.1 Структура хранимой процедуры
- 11.2 Пример хранимой процедуры
12 Функции, определенные пользователем (UDF)
13 Использование OPENQUERY с набором данных (RESULT SET)
14 «Фиктивный» SELECT запрос для вызова хранимой процедуры из табличной функции
15 Некоторые полезные скрипты для MS SQL
16 Дополнительные ресурсы

Схема элементов синтаксиса MS SQL

В данной статье описаны основы t-sql и примеры использования основных программируемых элементов, а именно: функций, определенных пользователем (UDF), триггеры, хранимые процедуры, описаны курсоры, циклы, описан принцип обработки ошибок.

Просто об объектах T-SQL:

Таблица — хранит данные.
Вьюха — содержит в себе сложный запрос к одной или нескольким таблицам, вьюхам, табличным функциям. В отличие от таблиц, не хранит в себе данные. Для хранения промежуточных результатов обработки данных можно использовать табличную переменную, локальную временную таблицу нельзя создавать в теле вьюхи. Над табличной переменной можно выполнять действия INSERT, UPDATE, DELETE. Для хранения промежуточных результатов обработки данных можно использовать табличную переменную. Временную таблицу нельзя создавать. Над табличной переменной можно выполнять действия INSERT, UPDATE, DELETE.
Табличная функция — похожа на вьюху, но основное отличие — принимает входные параметры и в зависимости от этих параметров запускается обработка данных, которая возращает тот или иной набор данных. Нельзя использовать внутри функции операции обработки данных (Insert, Update, Delete) над физическими таблицами. Для хранения промежуточных результатов обработки данных можно использовать табличную переменную. Временную таблицу нельзя создавать. Над табличной переменной можно выполнять действия INSERT, UPDATE, DELETE.
Скалярная функция — Принимает входные параметры, использует запросы к таблицам, вьюхам, табличным функциям и возращает одно значение (заданного типа). Нельзя использовать внутри функции операции обработки данных (Insert, Update, Delete) над физическими таблицами. Для хранения промежуточных результатов обработки данных можно использовать табличную переменную. Временную таблицу нельзя создавать. Над табличной переменной можно выполнять действия INSERT, UPDATE, DELETE.
Хранимая процедура — набор действий над данными. В основном требуется для того, чтобы взять данные из таблиц, вьюх, табличных функций, обработать данные и положить их в целевую таблицу. Можно использовать операции обработки данных (Insert, Update, Delete). Для хранения промежуточных результатов в хранимой процедуре используются как временные таблицы (в основном локальные), так и табличные переменные. Эффективней использовать временные таблицы.

Введение основы T-SQL и примеры, которые будут разобраны

В данной статье будут подробно рассмотрены основные объекты, создаваемые на языке T-SQL, которые покрывают 80% всех задач по обработке данных (триггеры, хранимые процедуры, табличные функции):

Таблицы базы данных, в которых хранятся собственно данные, содержат:

cтроки: каждая строка (или запись) представляет собой совокупность атрибутов (свойств) конкретного экземпляра объекта;
cтолбцы: каждый столбец (поле) представляет собой атрибут или совокупность атрибутов. Поле строки является минимальным элементом таблицы. Каждый столбец в таблице имеет определенное имя, тип данных и размер

Представления (виртуальные таблицы) или Views — создаются для отображения данных из таблиц.
Подобно реальным таблицам, Views содержат именованные столбцы и строки с данными. Для конечных пользователей представление выглядит как таблица, но в действительности оно не содержит данных, а лишь представляет данные, расположенные в одной или нескольких таблицах. Информация, которую видит пользователь через представление, не сохраняется в базе данных как самостоятельный объект

Хранимые процедуры (Stored Procedures) – это группа команд SQL, объединенных в один модуль. Такая группа команд компилируется и выполняется как единое целое
Хранимые процедуры (Stored Procedure) являются программами, хранящимися в базе данных и выполняющими различные действия, обычно с данными из базы данных, хотя процедуры могут и не осуществлять никаких обращений к базе. К хранимым процедурам могут обращаться любые программы, работающие с базой данных, к ним также могут обращаться и другие хранимые процедуры и триггеры. Допустима рекурсия, когда хранимая процедура обращается к самой себе. Хранимые процедуры выполняются на стороне сервера, а не на стороне клиента. Во многих случаях это может резко снизить сетевой трафик при решении различных задач работы с большой по объему базой данных и повысить производительность системы.

Триггеры (Triggers) – специальный класс хранимых процедур, автоматически запускаемых при добавлении, изменении или удалении данных из таблицы.
Триггеры (trigger) так же как и хранимые процедуры, являются программами, выполняющимися на стороне сервера. Однако напрямую обращение к триггерам невозможно. Они автоматически вызываются при наступлении некоторого события базы данных — при добавлении, изменении и или удалении строк конкретной таблицы. Триггеры могут вызываться при соединении с базой данных.

Функции, определенные пользователем (user defined functions, UDF) — это конструкции, содержащие исполняемый код. Функция выполняет какие-либо действия над данными и возвращает некоторое значение/набор данных. К функциям можно обращаться из триггеров, хранимых процедура и из других программных компонентов.

Немного о циклах, условиях в T-SQL

IF … ELSE

IF <condition> <statement>
	IF <condition>
		<statement>
	IF <condition>
		BEGIN
			<statement1>
			[<statement2>]
		END

IF <condition> <statement>

IF <condition>

IF <condition>

BEGIN

[<statement2>]

END

Пример использования IF

USE AdventureWorks2012;
GO
--1
DECLARE @Count INT;
SELECT @Count = COUNT(*)
FROM Sales.Customer;
IF @Count > 500 BEGIN
	PRINT 'The customer count is over 500.';
END;
GO
--2
DECLARE @Name VARCHAR(50);
SELECT @Name = FirstName + ' ' + LastName
FROM Person.Person
WHERE BusinessEntityID = 1;
--2.1
IF CHARINDEX('Ken',@Name) > 0 BEGIN
	PRINT 'The name for BusinessEntityID = 1 contains "Ken"';
END;
--2.2
IF CHARINDEX('Kathi',@Name) > 0 BEGIN
	PRINT 'The name for BusinessEntityID = 1 contains "Kathi"';
END;

USE AdventureWorks2012;

--1

DECLARE @Count INT;

SELECT @Count = COUNT(*)

FROM Sales.Customer;

IF @Count > 500 BEGIN

PRINT 'The customer count is over 500.';

END;

--2

DECLARE @Name VARCHAR(50);

SELECT @Name = FirstName + ' ' + LastName

FROM Person.Person

WHERE BusinessEntityID = 1;

--2.1

IF CHARINDEX('Ken',@Name) > 0 BEGIN

PRINT 'The name for BusinessEntityID = 1 contains "Ken"';

END;

--2.2

IF CHARINDEX('Kathi',@Name) > 0 BEGIN

PRINT 'The name for BusinessEntityID = 1 contains "Kathi"';

END;

Использование ELSE

IF <condition>
	<statement>
ELSE
	<statement>
IF <condition>
	BEGIN
		<statement1>
		[<statement2>]
	END
ELSE <statement>
IF <condition>
	BEGIN
		<statement1>
		[<statement2>]
	END
ELSE
	BEGIN
		<statement1>
		[<statement2>]
	END

IF <condition>

ELSE

IF <condition>

BEGIN

[<statement2>]

END

ELSE <statement>

IF <condition>

BEGIN

[<statement2>]

END

ELSE

BEGIN

[<statement2>]

END

Пример

USE AdventureWorks2012;
GO
--1
DECLARE @Count INT;
SELECT @Count = COUNT(*)
FROM Sales.Customer;
IF @Count < 500 PRINT 'The customer count is less than 500.';
ELSE PRINT 'The customer count is 500 or more.';
GO


--2
DECLARE @Name NVARCHAR(101);
SELECT @Name = FirstName + ' ' + LastName
FROM Person.Person
WHERE BusinessEntityID = 1;

--2.1
IF CHARINDEX('Ken', @Name) > 0 BEGIN
	PRINT 'The name for BusinessEntityID = 1 contains "Ken"';
END;
ELSE BEGIN
	PRINT 'The name for BusinessEntityID = 1 does not contain "Ken"';
	PRINT 'The name is ' + @Name;
END;

--2.2
IF CHARINDEX('Kathi', @Name) > 0 BEGIN
	PRINT 'The name for BusinessEntityID = 1 contains "Kathi"';
END;
ELSE BEGIN
	PRINT 'The name for BusinessEntityID = 1 does not contain "Kathi"';
	PRINT 'The name is ' + @Name;
END;

USE AdventureWorks2012;

--1

DECLARE @Count INT;

SELECT @Count = COUNT(*)

FROM Sales.Customer;

IF @Count < 500 PRINT 'The customer count is less than 500.';

ELSE PRINT 'The customer count is 500 or more.';

--2

DECLARE @Name NVARCHAR(101);

SELECT @Name = FirstName + ' ' + LastName

FROM Person.Person

WHERE BusinessEntityID = 1;

--2.1

IF CHARINDEX('Ken', @Name) > 0 BEGIN

PRINT 'The name for BusinessEntityID = 1 contains "Ken"';

END;

ELSE BEGIN

PRINT 'The name for BusinessEntityID = 1 does not contain "Ken"';

PRINT 'The name is ' + @Name;

END;

--2.2

IF CHARINDEX('Kathi', @Name) > 0 BEGIN

PRINT 'The name for BusinessEntityID = 1 contains "Kathi"';

END;

ELSE BEGIN

PRINT 'The name for BusinessEntityID = 1 does not contain "Kathi"';

PRINT 'The name is ' + @Name;

END;

Временная таблица и табличная переменная

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

Локальная временная таблица имеет префикс в виде одной решетки ‘#local_temp_table’ — видна в текущем соединении пользователя и удаляется, как только пользователь отсоединился от экземпляра (instance) MS SQL Server.
Глобальная временная таблица имеет префикс в виде двух решеток ‘##global_temp_table’ — видна для любого пользователя после ее создания и удаляется, когда все пользователи, ссылающиеся на таблицу, отсоединятся от экземпляра MS SQL Server.

Создание локальной временной таблицы (Local Temp Tables)

CREATE TABLE #tableName (<col1> <data type>,<col2> <data type>)

1	CREATE TABLE #tableName (<col1> <data type>,<col2> <data type>)

Пример

USE AdventureWorks2012;
GO
CREATE TABLE #myCustomers(CustomerID INT, FirstName VARCHAR(25),
LastName VARCHAR(25));
GO

INSERT INTO #myCustomers(CustomerID,FirstName,LastName)
SELECT C.CustomerID, FirstName, LastName
FROM Person.Person AS P
INNER JOIN Sales.Customer AS C
ON P.BusinessEntityID = C.PersonID;

SELECT CustomerID, FirstName, LastName
FROM #myCustomers;
DROP TABLE #myCustomers;

USE AdventureWorks2012;

CREATE TABLE #myCustomers(CustomerID INT, FirstName VARCHAR(25),

LastName VARCHAR(25));

INSERT INTO #myCustomers(CustomerID,FirstName,LastName)

SELECT C.CustomerID, FirstName, LastName

FROM Person.Person AS P

INNER JOIN Sales.Customer AS C

ON P.BusinessEntityID = C.PersonID;

SELECT CustomerID, FirstName, LastName

FROM #myCustomers;

DROP TABLE #myCustomers;

Создание глобальной временной таблицы (Global Temp Tables)

USE AdventureWorks2012;
GO
CREATE TABLE ##myCustomers(CustomerID INT, FirstName VARCHAR(25),
LastName VARCHAR(25));
GO

INSERT INTO ##myCustomers(CustomerID,FirstName,LastName)
SELECT C.CustomerID, FirstName,LastName
FROM Person.Person AS P
INNER JOIN Sales.Customer AS C
ON P.BusinessEntityID = C.PersonID;

SELECT CustomerID, FirstName, LastName
FROM ##myCustomers;

--Run the drop statement when you are done
--DROP TABLE ##myCustomers;

USE AdventureWorks2012;

CREATE TABLE ##myCustomers(CustomerID INT, FirstName VARCHAR(25),

LastName VARCHAR(25));

INSERT INTO ##myCustomers(CustomerID,FirstName,LastName)

SELECT C.CustomerID, FirstName,LastName

FROM Person.Person AS P

INNER JOIN Sales.Customer AS C

ON P.BusinessEntityID = C.PersonID;

SELECT CustomerID, FirstName, LastName

FROM ##myCustomers;

--Run the drop statement when you are done

--DROP TABLE ##myCustomers;

Создание табличной переменной (Table Variables)

DECLARE @tableName TABLE (<col1> <data type>,<col2> <data type>)

1	DECLARE @tableName TABLE (<col1> <data type>,<col2> <data type>)

Пример

USE AdventureWorks2012;
DECLARE @myCustomers TABLE (
   CustomerID INT,
   FirstName VARCHAR(25),
   LastName VARCHAR(25)
 )

INSERT INTO @myCustomers(CustomerID,FirstName,LastName)
SELECT C.CustomerID, FirstName,LastName
FROM Person.Person AS P
INNER JOIN Sales.Customer AS C
ON P.BusinessEntityID = C.PersonID;

SELECT CustomerID, FirstName, LastName
FROM @myCustomers;

USE AdventureWorks2012;

DECLARE @myCustomers TABLE (

CustomerID INT,

FirstName VARCHAR(25),

LastName VARCHAR(25)

)

INSERT INTO @myCustomers(CustomerID,FirstName,LastName)

SELECT C.CustomerID, FirstName,LastName

FROM Person.Person AS P

INNER JOIN Sales.Customer AS C

ON P.BusinessEntityID = C.PersonID;

SELECT CustomerID, FirstName, LastName

FROM @myCustomers;

Переменные

SQL Server поддерживает два типа переменных: локальные и глобальные. Локальные переменные существуют только в пределах сеанса, во время которого они были созданы. Глобальные используются для получения информации о сервере в целом.

Локальные переменные

Локальные переменные служат для хранения временной информации. Локальные переменные объявляются командой DECLARE и существуют лишь на время выполнения пакета. После завершения пакета вы уже не сможете обратиться к ним. Значения переменных задаются командой SELECT или SET. Если значение присваивается одной переменной, эти команды эквивалентны. Команда SELECT может присвоить значения сразу нескольким переменным. В следующем примере команда DECLARE объявляет две переменные, одна из которых задается командой SELECT, а другая — командой SET. Затем происходит чтение и вывод значения обеих переменных:

DECLARE @MyDate datetime, @Number int, @MyString nvarchar(50)
SELECT @Number = 1, @MyString='My string'
SET @MyDate = GETDATE()
SELECT @MyDate, @Number, @MyString

DECLARE @MyDate datetime, @Number int, @MyString nvarchar(50)

SELECT @Number = 1, @MyString='My string'

SET @MyDate = GETDATE()

SELECT @MyDate, @Number, @MyString

Вы можете присваивать значения нескольким переменным в одной команде SELECT и объявлять несколько переменных в одной команде DECLARE. Начиная с SQL Server 2008, можно присваивать значение переменной прямо в команде DECLARE. Например:

DECLARE @Number int = 5

1	DECLARE @Number int = 5

А также вы можете использовать математические операции типа +=, -= и подобные, которые прибавляют, вычитают некое значение от переменной, которой потом и присваивается вычисленный результат. Например:

SET @myVar += 100

1	SET @myVar += 100

Глобальные переменные

Глобальные переменные используются сервером для отслеживания информации уровня сервера или базы данных, относящейся к конкретному сеансу. Для глобальных переменных невозможно явное присваивание или объявление. Некоторые глобальные переменные:

@@ERROR - Код ошибки для последней команды SQL
@@FETCH_STATUS - Статус предыдущей команды выборки для курсора
@@IDENTITY - Последнее значение счетчика, используемое в операции вставки
@@NESTLEVEL - Количество уровней вложенности для хранимой процедуры или триггера
@@ROWCOUNT - Количество записей, обработанных предыдущей командой
@@SERVERNAME - Имя локального сервера
@@SPID - Идентификатор текущего процесса
@@TRANCOUNT - Уровень вложенности транзакции
@@VERSION - Номер версии SQL Server, дата и тип процессора

@@ERROR - Код ошибки для последней команды SQL

@@FETCH_STATUS - Статус предыдущей команды выборки для курсора

@@IDENTITY - Последнее значение счетчика, используемое в операции вставки

@@NESTLEVEL - Количество уровней вложенности для хранимой процедуры или триггера

@@ROWCOUNT - Количество записей, обработанных предыдущей командой

@@SERVERNAME - Имя локального сервера

@@SPID - Идентификатор текущего процесса

@@TRANCOUNT - Уровень вложенности транзакции

@@VERSION - Номер версии SQL Server, дата и тип процессора

Пример вывода значения локальной или глобальной переменной:

SELECT @@VERSION
SELECT @MyDate

1 2	SELECT @@VERSION SELECT @MyDate

Объявление и инициализация переменной

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

DECLARE @variableName <type>[(size)] = <value1>
SET @variableName = <value2>

1 2	DECLARE @variableName <type>[(size)] = <value1> SET @variableName = <value2>

Использование TRY … CATCH — обработка ошибок

Конструкция TRY…CATCH позволяет перехватывать все ошибки исполнения кода. За блоком TRY сразу же должен следовать блок CATCH. Если ошибки в блоке TRY не возникают, то после выполнения последней инструкции в блоке TRY управление передается инструкции, расположенной сразу после инструкции END CATCH. Если же в коде, заключенном в блоке TRY, происходит ошибка, управление передается первой инструкции в соответствующем блоке CATCH. Если инструкция END CATCH является последней инструкцией хранимой процедуры или триггера, управление передается обратно инструкции, вызвавшей эту хранимую процедуру или триггер.

Когда код в блоке CATCH завершен, управление передается инструкции, стоящей сразу после инструкции END CATCH. Ошибки, обнаруженные в блоке CATCH, не передаются в вызывающее приложение. Если какие-либо сведения об ошибке должны быть возвращены в приложение, код в блоке CATCH должен выполнить передачу этой ошибки, используя любые доступные механизмы, такие как результирующие наборы инструкции SELECT либо инструкции RAISERROR и PRINT.

Синтаксис:

BEGIN TRY
	<statements that might cause an error>
END TRY
BEGIN CATCH
	<statements to access error information and deal with the error>
END CATCH

BEGIN TRY

END TRY

BEGIN CATCH

END CATCH

Функции по работе с ошибками:

ERROR_NUMBER() — возвращает номер ошибки вне зависимости от числа запусков и места запуска в пределах блока CATCH.
ERROR_SEVERITY() — При вызове в блоке CATCH возвращает серьезность сообщения об ошибке, вызвавшего запуск блока CATCH. Возвращает значение NULL в случае вызова вне блока CATCH.
ERROR_STATE() — При вызове в блоке CATCH возвращает номер состояния сообщения об ошибке, вызвавшей запуск блока CATCH.
ERROR_PROCEDURE() — Возвращает имя хранимой процедуры или триггера, в которых произошла ошибка, вызвавшая запуск блока CATCH конструкции TRY…CATCH.
ERROR_LINE() — Возвращает номер строки, в которой возникла ошибка, приведшая к активации блока CATCH конструкции TRY…CATCH.
ERROR_MESSAGE() — При вызове в блоке CATCH возвращает полный текст сообщения об ошибке, запустившей блок CATCH. Текст содержит значения подставляемых параметров, таких как длина, имена объектов или время.

Пример:

USE AdventureWorks2012;
GO

--1
BEGIN TRY
	PRINT 1/0;
END TRY
BEGIN CATCH
	PRINT 'Inside the Catch block';
	PRINT ERROR_NUMBER();
	PRINT ERROR_MESSAGE();
	PRINT ERROR_NUMBER();
END CATCH
	PRINT 'Outside the catch block';
	PRINT ERROR_NUMBER()
GO

--2
BEGIN TRY
	DROP TABLE testTable;
END TRY
BEGIN CATCH
	PRINT 'An error has occurred.'
	PRINT ERROR_NUMBER();
	PRINT ERROR_MESSAGE();
END CATCH;

USE AdventureWorks2012;

--1

BEGIN TRY

PRINT 1/0;

END TRY

BEGIN CATCH

PRINT 'Inside the Catch block';

PRINT ERROR_NUMBER();

PRINT ERROR_MESSAGE();

PRINT ERROR_NUMBER();

END CATCH

PRINT 'Outside the catch block';

PRINT ERROR_NUMBER()

--2

BEGIN TRY

DROP TABLE testTable;

END TRY

BEGIN CATCH

PRINT 'An error has occurred.'

PRINT ERROR_NUMBER();

PRINT ERROR_MESSAGE();

END CATCH;

Использование цикла WHILE

Инструкция WHILE повторяет инструкцию или блок инструкций до тех пор, пока указанное условие выполняется.

WHILE <condition>
BEGIN
	<statement1>
	[<statement2>]
END

WHILE <condition>

BEGIN

[<statement2>]

END

Пример:

USE AdventureWorks2012;
GO

--1
DECLARE @Count INT = 1;
WHILE @Count < 5 BEGIN
	PRINT @Count;
	SET @Count += 1;
END;
GO

--2
IF EXISTS (
	SELECT * FROM sys.objects
	WHERE object_id = OBJECT_ID(N'dbo.demoContactType')
	AND type in (N'U')
	)
	DROP TABLE dbo.demoContactType;
GO

CREATE TABLE dbo.demoContactType(ContactTypeID INT NOT NULL PRIMARY KEY,
Processed BIT NOT NULL);
GO

INSERT INTO dbo.demoContactType(ContactTypeID,Processed)
SELECT ContactTypeID, 0
FROM Person.ContactType;
DECLARE @Count INT = 1;
WHILE EXISTS(SELECT * From dbo.demoContactType WHERE Processed = 0) BEGIN
	UPDATE dbo.demoContactType SET Processed = 1
	WHERE ContactTypeID = @Count;
	SET @Count += 1;
END;
PRINT 'Done!';

USE AdventureWorks2012;

--1

DECLARE @Count INT = 1;

WHILE @Count < 5 BEGIN

PRINT @Count;

SET @Count += 1;

END;

--2

IF EXISTS (

SELECT * FROM sys.objects

WHERE object_id = OBJECT_ID(N'dbo.demoContactType')

AND type in (N'U')

)

DROP TABLE dbo.demoContactType;

CREATE TABLE dbo.demoContactType(ContactTypeID INT NOT NULL PRIMARY KEY,

Processed BIT NOT NULL);

INSERT INTO dbo.demoContactType(ContactTypeID,Processed)

SELECT ContactTypeID, 0

FROM Person.ContactType;

DECLARE @Count INT = 1;

WHILE EXISTS(SELECT * From dbo.demoContactType WHERE Processed = 0) BEGIN

UPDATE dbo.demoContactType SET Processed = 1

WHERE ContactTypeID = @Count;

SET @Count += 1;

END;

PRINT 'Done!';

Курсор

Курсор (current set of record) – временный набор строк, которые можно перебирать последовательно, с первой до последней. При работе с курсорами используются следующие команды.

Объявление курсора: Любой курсор создается на основе некоторого оператора SELECT.

DECLARE имя_курсора CURSOR FOR SELECT текст_запроса

1	DECLARE имя_курсора CURSOR FOR SELECT текст_запроса

Открытие курсора: Для того чтобы с помощью курсора можно было читать строки, его надо обязательно открыть.

OPEN имя_курсора

1	OPEN имя_курсора

Чтение следующей строки из курсора:

FETCH имя_курсора INTO список_переменных

1	FETCH имя_курсора INTO список_переменных

Пример использования курсора

CREATE PROCEDURE [dbo].[MyProcedure] AS

DECLARE @ID INT
DECLARE @QUA INT
DECLARE @VAL VARCHAR (500)
DECLARE @NAM VARCHAR (500)

/*Объявляем курсор*/
DECLARE @CURSOR CURSOR

/*Заполняем курсор*/
SET @CURSOR  = CURSOR SCROLL
FOR
SELECT  INDEX, QUANTITY, VALUE,  NAME  
  FROM  My_First_Table WHERE  QUANTITY > 1
/*Открываем курсор*/
OPEN @CURSOR
/*Выбираем первую строку*/
FETCH NEXT FROM @CURSOR INTO @ID, @QUA, @VAL, @NAM
/*Выполняем в цикле перебор строк*/
WHILE @@FETCH_STATUS = 0
BEGIN

        IF NOT EXISTS(SELECT VAL FROM My_Second_Table WHERE ID=@ID)
        BEGIN
/*Вставляем параметры в третью таблицу если условие соблюдается*/
                INSERT INTO My_Third_Table (VALUE, NAME) VALUE(@VAL, @NAM)
        END
/*Выбираем следующую строку*/
FETCH NEXT FROM @CURSOR INTO @ID, @QUA, @VAL, @NAM
END
CLOSE @CURSOR

CREATE PROCEDURE [dbo].[MyProcedure] AS

DECLARE @ID INT

DECLARE @QUA INT

DECLARE @VAL VARCHAR (500)

DECLARE @NAM VARCHAR (500)

/*Объявляем курсор*/

DECLARE @CURSOR CURSOR

/*Заполняем курсор*/

SET @CURSOR = CURSOR SCROLL

FOR

SELECT INDEX, QUANTITY, VALUE, NAME

FROM My_First_Table WHERE QUANTITY > 1

/*Открываем курсор*/

OPEN @CURSOR

/*Выбираем первую строку*/

FETCH NEXT FROM @CURSOR INTO @ID, @QUA, @VAL, @NAM

/*Выполняем в цикле перебор строк*/

WHILE @@FETCH_STATUS = 0

BEGIN

IF NOT EXISTS(SELECT VAL FROM My_Second_Table WHERE ID=@ID)

BEGIN

/*Вставляем параметры в третью таблицу если условие соблюдается*/

INSERT INTO My_Third_Table (VALUE, NAME) VALUE(@VAL, @NAM)

END

/*Выбираем следующую строку*/

FETCH NEXT FROM @CURSOR INTO @ID, @QUA, @VAL, @NAM

END

CLOSE @CURSOR

WITH конструкция в MS SQL

Для того, чтобы обработать данные обычным запросом SQL, необходимо приложить «умственные усилия», чтобы запрос оптимально работал, чтобы выдавал правильный результат, чтобы был понятен другим членам команды. Это требует значительных временных ресурсов, что при работе не всегда есть. На помощь может прийти конструкция WITH.
Суть: мы разбиваем единую задачу на блоки, каждый блок реализуется подзапросом. Созданные подзапросы можно использовать в последующих подзапросах. Созданные подзапросы (все или некоторые) используются в главном запросе, который и возращает результат.

Схема WITH конструкции:

Пример WITH конструкции для MS SQL:

WITH
--Первый подзапрос
Sales_CTE (SalesPersonID, TotalSales, SalesYear) AS
(  
    SELECT SalesPersonID
	      ,SUM(TotalDue) AS TotalSales
	      ,YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
    GROUP BY SalesPersonID, YEAR(OrderDate)
),
--Второй подзапрос
Sales_Quota_CTE (BusinessEntityID, SalesQuota, SalesQuotaYear) AS  
(  
    SELECT BusinessEntityID
	      ,SUM(SalesQuota) AS SalesQuota
		  ,YEAR(QuotaDate) AS SalesQuotaYear  
    FROM Sales.SalesPersonQuotaHistory
    GROUP BY BusinessEntityID, YEAR(QuotaDate)
)  
  
--Главный запрос
SELECT SalesPersonID  
      ,SalesYear  
      ,TotalSales AS TotalSales  
      ,SalesQuotaYear  
      ,SalesQuota AS SalesQuota  
      ,(TotalSales - SalesQuota) AS Amt_Above_or_Below_Quota
FROM Sales_CTE  
JOIN Sales_Quota_CTE
ON Sales_Quota_CTE.BusinessEntityID = Sales_CTE.SalesPersonID  
   AND Sales_CTE.SalesYear = Sales_Quota_CTE.SalesQuotaYear  
ORDER BY SalesPersonID, SalesYear;

WITH

--Первый подзапрос

Sales_CTE (SalesPersonID, TotalSales, SalesYear) AS

(

SELECT SalesPersonID

,SUM(TotalDue) AS TotalSales

,YEAR(OrderDate) AS SalesYear

FROM Sales.SalesOrderHeader

WHERE SalesPersonID IS NOT NULL

GROUP BY SalesPersonID, YEAR(OrderDate)

--Второй подзапрос

Sales_Quota_CTE (BusinessEntityID, SalesQuota, SalesQuotaYear) AS

(

SELECT BusinessEntityID

,SUM(SalesQuota) AS SalesQuota

,YEAR(QuotaDate) AS SalesQuotaYear

FROM Sales.SalesPersonQuotaHistory

GROUP BY BusinessEntityID, YEAR(QuotaDate)

)

--Главный запрос

SELECT SalesPersonID

,SalesYear

,TotalSales AS TotalSales

,SalesQuotaYear

,SalesQuota AS SalesQuota

,(TotalSales - SalesQuota) AS Amt_Above_or_Below_Quota

FROM Sales_CTE

JOIN Sales_Quota_CTE

ON Sales_Quota_CTE.BusinessEntityID = Sales_CTE.SalesPersonID

AND Sales_CTE.SalesYear = Sales_Quota_CTE.SalesQuotaYear

ORDER BY SalesPersonID, SalesYear;

Схема использования конструкции WITH в VIEW (во вьюхе)

Триггеры

Триггеры – это предварительно определенное действие или последовательность действий, автоматически осуществляемых при выполнении операций обновления, добавления или удаления данных.
Исключительно важно в этом определении слово «автоматически». Ни пользователь, ни приложение не могут активизировать триггер, он выполняется автоматически, когда пользователь или приложение выполняют с базой данных определенные действия.
Триггер – это специальный вид хранимой процедуры. Триггеры обеспечивают проверку любых изменений на корректность, прежде чем эти изменения будут приняты.
Каждый триггер привязывается к конкретной таблице. Все производимые им модификации данных рассматриваются как одна транзакция. В случае обнаружения ошибки или нарушения целостности данных происходит откат этой транзакции. Тем самым внесение изменений запрещается. Отменяются также все изменения, уже сделанные триггером.
Создать триггер может только владелец базы данных. Это ограничение позволяет избежать случайного изменения структуры таблиц, способов связи с ними других объектов и т.п.

Компоненты триггера

Ограничения, для реализации которых создается триггер.
Событие, которое будет характеризовать возникновение ситуации, требующей проверки ограничений. Триггерные события чаще всего связаны с изменением состояния базы данных и состоят из вставки, удаления и обновления строк в таблице. События могут учитываться и дополнительные условия (например, добавление записи только с отрицательным значением).
Предусмотренное действие осуществляется за счет выполнения процедуры или последовательности процедур, с помощью которых реализуется логика, требуемая для реализации ограничений.

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

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

Типы триггеров

Существует три типа триггеров:

Insert – определяет действия, которые будут выполняться после добавления новой записи в таблицу.
Update – определяет действия, которые будут выполняться после изменения записи таблицы.
Delete – определяет действия, которые будут выполняться после удаления записи из таблиц.

Часто в СУБД определяется большее число событий, с которыми можно связать триггеры. Например, до вставки, после вставки, до изменения, после изменения и т.д.

Структура триггера

Примеры триггеров

CREATE TRIGGER trg_T1_insert_audit
ON dbo.T1
AFTER INSERT
AS
SET NOCOUNT ON;
INSERT INTO dbo.T1_Audit(keycol, datacol)
   SELECT keycol, datacol
   FROM inserted;
GO

CREATE TRIGGER trg_T1_insert_audit

ON dbo.T1

AFTER INSERT

SET NOCOUNT ON;

INSERT INTO dbo.T1_Audit(keycol, datacol)

SELECT keycol, datacol

FROM inserted;

CREATE TRIGGER [dbo].[T2]
ON [dbo].[Table]
AFTER INSERT 
AS 
BEGIN
	EXEC [database].[dbo].[Stored_Procedure_for_T2]
END
GO

CREATE TRIGGER [dbo].[T2]

ON [dbo].[Table]

AFTER INSERT

BEGIN

EXEC [database].[dbo].[Stored_Procedure_for_T2]

END

Хранимые процедуры

Хранимая процедура (Stored procedure) – программа, которая выполняется внутри базы данных и может предпринимать сложные действия на основе информации, задаваемой пользователем. Поскольку хранимые процедуры выполняются непосредственно на сервере базы данных, обеспечивается более высокое быстродействие, чем при выполнении тех же операций средствами клиента базы данных.
Хранимая процедура объединяет запросы и процедурную логику (операторы присваивания, логического ветвления и т.п.) и хранится в базе данных.
Одна процедура может быть использована в любом количестве клиентских приложений, что позволяет существенно сэкономить трудозатраты на создание прикладного программного обеспечения и эффективно применять стратегию повторного использования кода. Так же, как и любые процедуры в стандартных языках программирования, хранимые процедуры могут иметь входные и выходные параметры или не иметь их.
Преимущества выполнения в базе данных хранимых процедур вместо отдельных команд Transact SQL:

необходимые команды уже содержатся в базе данных;
все они прошли этап синтаксического анализа и находятся в исполняемом формате;
хранимые процедуры поддерживают модульное программирование, так как позволяют разбивать большие задачи на самостоятельные, более мелкие и удобные в управлении части;
хранимые процедуры могут вызывать другие хранимые процедуры и функции;
хранимые процедуры могут быть вызваны из прикладных программ других типов;
как правило, хранимые процедуры выполняются быстрее, чем последовательность отдельных команд;
хранимые процедуры проще использовать: они могут состоять из десятков и сотен команд, но для их запуска достаточно указать всего лишь имя нужной хранимой процедуры. Это позволяет уменьшить размер запроса, посылаемого от клиента на сервер, а значит, и нагрузку на сеть.

Хранимые процедуры вызываются клиентской программой, другой хранимой процедурой или триггером. Разработчик может управлять правами доступа к хранимой процедуре, разрешая или запрещая ее выполнение. Изменять код хранимой процедуры разрешается только ее владельцу или члену фиксированной роли базы данных. При необходимости можно передать права владения ею от одного пользователя к другому.

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

Структура хранимой процедуры

Пример хранимой процедуры

CREATE PROCEDURE [schemadb].[stored_procedure]
AS
   --Если временная таблица существует, то удаляем (в случае ошибки может остаться)
   IF OBJECT_ID('tempdb.dbo.#rawData', 'U') IS NOT NULL
      DROP TABLE #rawData;

   --Если временная таблица существует, то удаляем (в случае ошибки может остаться)
   IF OBJECT_ID('tempdb.dbo.#groupedData', 'U') IS NOT NULL
      DROP TABLE #groupedData;

   --Вставляем данные из CSV во временную таблицу
   SELECT
      csvtbl.field1
      csvtbl.field2,
      csvtbl.field3,
      csvtbl.field4,
      csvtbl.field5,
      csvtbl.field6,
      csvtbl.field7,
      csvtbl.field8,
      csvtbl.field9,
      csvtbl.field10,
      csvtbl.DataValue
   INTO #rawData
   FROM OPENROWSET(
               BULK 'D:\Data_CSV\Exported_Data.csv',        --Источник с данными в формате CSV
               FORMATFILE = 'D:\Data_CSV\Data_format.fmt'   --Формат данных, который забирается из csv
   ) AS csvtbl;

   SELECT field1
         field2,
         field3,
         SUM(DataValue) DataValue
   FROM #rawData
   INTO #groupedData
   GROUP BY field1,field2,field3;

   --Удаляем данные
   truncate table [schemadb].[target_table]

         INSERT INTO [database].[schemadb].[target_table]
         SELECT  tbl1.field1
               tbl2.field_name,
               tbl1.field3,
               tbl1.DataValue
         FROM #groupedData tbl1
         LEFT JOIN [database].[schemadb].[mapping_table] tbl2
            ON tbl1.field2 = tbl2.id_field;

   DROP Table #rawData;
   DROP Table #groupedData;
GO

CREATE PROCEDURE [schemadb].[stored_procedure]

--Если временная таблица существует, то удаляем (в случае ошибки может остаться)

IF OBJECT_ID('tempdb.dbo.#rawData', 'U') IS NOT NULL

DROP TABLE #rawData;

--Если временная таблица существует, то удаляем (в случае ошибки может остаться)

IF OBJECT_ID('tempdb.dbo.#groupedData', 'U') IS NOT NULL

DROP TABLE #groupedData;

--Вставляем данные из CSV во временную таблицу

SELECT

csvtbl.field1

csvtbl.field2,

csvtbl.field3,

csvtbl.field4,

csvtbl.field5,

csvtbl.field6,

csvtbl.field7,

csvtbl.field8,

csvtbl.field9,

csvtbl.field10,

csvtbl.DataValue

INTO #rawData

FROM OPENROWSET(

BULK 'D:\Data_CSV\Exported_Data.csv', --Источник с данными в формате CSV

FORMATFILE = 'D:\Data_CSV\Data_format.fmt' --Формат данных, который забирается из csv

) AS csvtbl;

SELECT field1

field2,

field3,

SUM(DataValue) DataValue

FROM #rawData

INTO #groupedData

GROUP BY field1,field2,field3;

--Удаляем данные

truncate table [schemadb].[target_table]

INSERT INTO [database].[schemadb].[target_table]

SELECT tbl1.field1

tbl2.field_name,

tbl1.field3,

tbl1.DataValue

FROM #groupedData tbl1

LEFT JOIN [database].[schemadb].[mapping_table] tbl2

ON tbl1.field2 = tbl2.id_field;

DROP Table #rawData;

DROP Table #groupedData;

Функции, определенные пользователем (UDF)

Функции, определенные пользователем — подпрограммы, которые принимают параметры, выполняют действие, например, сложные вычисления, и возвращают результат этого действия в виде значения. Возвращаемое значение может быть либо единичным скалярным значением, либо результирующим набором (таблицей).
В языках программирования обычно существуют два типа подпрограмм:

хранимые процедуры
функции, определенные пользователем (User Defined Functions, UDF)

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

Функции, определенные пользователем, могут быть скалярными или табличными. Скалярная функция возращает скалярное значение (число). Это означает, что в предложении RETURNS скалярной функции вы задаете один из стандартных типов данных. Функции являются табличными, если предложение RETURNS возращает набор строк.

Определяемые пользователем функции предоставляют следующие возможности:

Делают возможным использование модульного программирования;
Позволяют ускорить выполнение;
Позволяют уменьшить сетевой трафик.

Виды табличных функций

Табличная функция — функция, в которой задается структура результирующей таблицы. В теле функции выполняется запрос, который наполняет эту результирующую таблицу данными. После чего, возвращается эта таблица.
Inline-табличная функция — данный вид функции содержит комбинированный запрос (SELECT), который возвращает результирующий набор данных (таблицу). Обычно строится в формате WITH … SELECT …
Скалярная функция — Функция, которая возвращает 1 значение. Значение может иметь различный формат данных.

Структура табличной функции

Пример табличной функции

CREATE FUNCTION dbo.GetProductPullList()
RETURNS @result table
   (
      SalesOrderID int NOT NULL,
      ProductID int NOT NULL,
      LocationID smallint NOT NULL,
      Shelf nvarchar(10) NOT NULL,
      Bin tinyint NOT NULL,
      QuantityInBin smallint NOT NULL,
      QuantityOnOrder smallint NOT NULL,
      QuantityToPull smallint NOT NULL,
      PartialFillFlag nchar(1) NOT NULL,
      PRIMARY KEY (SalesOrderID, ProductID, LocationID, Shelf, Bin)
   )
AS
   BEGIN
      INSERT INTO @result
      (
         SalesOrderID,
         ProductID,
         LocationID,
         Shelf,
         Bin,
         QuantityInBin,
         QuantityOnOrder,
         QuantityToPull,
         PartialFillFlag
      )
      SELECT
         Order_Details.SalesOrderID,
         Order_Details.ProductID,
         Inventory_Details.LocationID,
         Inventory_Details.Shelf,
         Inventory_Details.Bin,
         Inventory_Details.Quantity,
         Order_Details.OrderQty,
         COUNT(*) AS PullQty,
         CASE WHEN COUNT(*) < Order_Details.OrderQty
         THEN N'Y'
         ELSE N'N'
         END AS PartialFillFlag
      FROM
         (
            SELECT ROW_NUMBER() OVER
            (
               PARTITION BY p.ProductID
               ORDER BY p.ProductID,
               p.LocationID,
               p.Shelf,
               p.Bin
            ) AS Num,
            p.ProductID,
            p.LocationID,
            p.Shelf,
            p.Bin,
            p.Quantity
            FROM Production.ProductInventory p
            INNER JOIN dbo.Numbers n
            ON n.Num BETWEEN 1 AND Quantity
         ) Inventory_Details
      INNER JOIN
      (
         SELECT ROW_NUMBER() OVER
         (
            PARTITION BY o.ProductID
            ORDER BY o.ProductID,
            o.SalesOrderID
         ) AS Num,
         o.ProductID,
         o.SalesOrderID,
         o.OrderQty
      FROM Sales.SalesOrderDetail o
      INNER JOIN dbo.Numbers n
      ON n.Num BETWEEN 1 AND o.OrderQty
      ) Order_Details
      ON Inventory_Details.ProductID = Order_Details.ProductID
      AND Inventory_Details.Num = Order_Details.Num
      GROUP BY
         Order_Details.SalesOrderID,
         Order_Details.ProductID,
         Inventory_Details.LocationID,
         Inventory_Details.Shelf,
         Inventory_Details.Bin,
         Inventory_Details.Quantity,
         Order_Details.OrderQty;
         
      RETURN;
   END;
GO

CREATE FUNCTION dbo.GetProductPullList()

RETURNS @result table

(

SalesOrderID int NOT NULL,

ProductID int NOT NULL,

LocationID smallint NOT NULL,

Shelf nvarchar(10) NOT NULL,

Bin tinyint NOT NULL,

QuantityInBin smallint NOT NULL,

QuantityOnOrder smallint NOT NULL,

QuantityToPull smallint NOT NULL,

PartialFillFlag nchar(1) NOT NULL,

PRIMARY KEY (SalesOrderID, ProductID, LocationID, Shelf, Bin)

)

BEGIN

INSERT INTO @result

(

SalesOrderID,

ProductID,

LocationID,

Shelf,

Bin,

QuantityInBin,

QuantityOnOrder,

QuantityToPull,

PartialFillFlag

)

SELECT

Order_Details.SalesOrderID,

Order_Details.ProductID,

Inventory_Details.LocationID,

Inventory_Details.Shelf,

Inventory_Details.Bin,

Inventory_Details.Quantity,

Order_Details.OrderQty,

COUNT(*) AS PullQty,

CASE WHEN COUNT(*) < Order_Details.OrderQty

THEN N'Y'

ELSE N'N'

END AS PartialFillFlag

FROM

(

SELECT ROW_NUMBER() OVER

(

PARTITION BY p.ProductID

ORDER BY p.ProductID,

p.LocationID,

p.Shelf,

p.Bin

) AS Num,

p.ProductID,

p.LocationID,

p.Shelf,

p.Bin,

p.Quantity

FROM Production.ProductInventory p

INNER JOIN dbo.Numbers n

ON n.Num BETWEEN 1 AND Quantity

) Inventory_Details

INNER JOIN

(

SELECT ROW_NUMBER() OVER

(

PARTITION BY o.ProductID

ORDER BY o.ProductID,

o.SalesOrderID

) AS Num,

o.ProductID,

o.SalesOrderID,

o.OrderQty

FROM Sales.SalesOrderDetail o

INNER JOIN dbo.Numbers n

ON n.Num BETWEEN 1 AND o.OrderQty

) Order_Details

ON Inventory_Details.ProductID = Order_Details.ProductID

AND Inventory_Details.Num = Order_Details.Num

GROUP BY

Order_Details.SalesOrderID,

Order_Details.ProductID,

Inventory_Details.LocationID,

Inventory_Details.Shelf,

Inventory_Details.Bin,

Inventory_Details.Quantity,

Order_Details.OrderQty;

RETURN;

END;

Структура Inline табличной функции

Пример Inline табличной функции

CREATE FUNCTION dbo.GetCommaSplit (@String nvarchar(max))
RETURNS table
AS RETURN
   (
      WITH Splitter (Num, String) AS
      (
         SELECT
            Num, SUBSTRING(@String,
            Num,
            CASE CHARINDEX(N',', @String, Num)
               WHEN 0 THEN LEN(@String) - Num + 1
               ELSE CHARINDEX(N',', @String, Num) - Num
            END
            ) AS String
         FROM dbo.Numbers
         WHERE Num <= LEN(@String)
         AND (SUBSTRING(@String, Num - 1, 1) = N',' OR Num = 0)
      )
		
      SELECT
         ROW_NUMBER() OVER (ORDER BY Num) AS Num,
         RTRIM(LTRIM(String)) AS Element
      FROM Splitter
      WHERE String <> ''
   );
GO

CREATE FUNCTION dbo.GetCommaSplit (@String nvarchar(max))

RETURNS table

AS RETURN

(

WITH Splitter (Num, String) AS

(

SELECT

Num, SUBSTRING(@String,

Num,

CASE CHARINDEX(N',', @String, Num)

WHEN 0 THEN LEN(@String) - Num + 1

ELSE CHARINDEX(N',', @String, Num) - Num

END

) AS String

FROM dbo.Numbers

WHERE Num <= LEN(@String)

AND (SUBSTRING(@String, Num - 1, 1) = N',' OR Num = 0)

)

SELECT

ROW_NUMBER() OVER (ORDER BY Num) AS Num,

RTRIM(LTRIM(String)) AS Element

FROM Splitter

WHERE String <> ''

);

Создание User-Defined Scalar Functions (скалярных функций)

Структура скалярной функции

Синтаксис

CREATE FUNCTION <scalar function Name> (<@param1> <data type1>, <@param2> <data type2>)
RETURNS <data type> AS
BEGIN
	<statements>
	RETURN <value>
END

CREATE FUNCTION <scalar function Name> (<@param1> <data type1>, <@param2> <data type2>)

RETURNS <data type> AS

BEGIN

RETURN <value>

END

Пример

CREATE FUNCTION dbo.udf_Product(@num1 INT, @num2 INT)
RETURNS INT AS
BEGIN
	DECLARE @Product INT;
	SET @Product = ISNULL(@num1,0) * ISNULL(@num2,0);
	RETURN @Product;
END;
GO

CREATE FUNCTION dbo.udf_Product(@num1 INT, @num2 INT)

RETURNS INT AS

BEGIN

DECLARE @Product INT;

SET @Product = ISNULL(@num1,0) * ISNULL(@num2,0);

RETURN @Product;

END;

Использование OPENQUERY с набором данных (RESULT SET)

CREATE  view [database].[dbschema].[view_example] as 
 select
	 field1 collate Cyrillic_General_CI_AS field1
	,field2 collate Cyrillic_General_CI_AS field2
	,field3 collate Cyrillic_General_CI_AS field3
	,field4 collate Cyrillic_General_CI_AS field4
	,field5 collate Cyrillic_General_CI_AS field5
	,field6 collate Cyrillic_General_CI_AS field6
	,field7 collate Cyrillic_General_CI_AS field7
	,field8 collate Cyrillic_General_CI_AS field8
 from 
 OPENQUERY (LOCALSERVER ,' EXEC [database].[dbschema].[stored_procedure_example]
  WITH RESULT SETS (
          (  field1 varchar(80)
			,field2 varchar(80)
			,field3 varchar(80)
			,field4 varchar(80)
			,field5 varchar(80) 
			,field6 varchar(80) 
			,field7 varchar(80)
		  )
		 );
   ') 
GO

CREATE view [database].[dbschema].[view_example] as

select

field1 collate Cyrillic_General_CI_AS field1

,field2 collate Cyrillic_General_CI_AS field2

,field3 collate Cyrillic_General_CI_AS field3

,field4 collate Cyrillic_General_CI_AS field4

,field5 collate Cyrillic_General_CI_AS field5

,field6 collate Cyrillic_General_CI_AS field6

,field7 collate Cyrillic_General_CI_AS field7

,field8 collate Cyrillic_General_CI_AS field8

from

OPENQUERY (LOCALSERVER ,' EXEC [database].[dbschema].[stored_procedure_example]

WITH RESULT SETS (

( field1 varchar(80)

,field2 varchar(80)

,field3 varchar(80)

,field4 varchar(80)

,field5 varchar(80)

,field6 varchar(80)

,field7 varchar(80)

)

);

«Фиктивный» SELECT запрос для вызова хранимой процедуры из табличной функции

Если Вам каким-то чудом потребовалось вызвать хранимую процедуру из табличной функции, то можно создать некоторый фиктивный запрос к хранимой процедуре, а на выход процедуры после завершения всех действий над таблицами (INSERT, UPDATE, DELETE) подать 1 техническую строку. Данный метод является вынужденным и не совсем корректным, но в критических ситуациях может помочь. А так, лучше всегда избегайте OPENQUERY для вызова объектов, т.к. теряется прозрачность зависимостей объекта.

select tbl.field1
      ,tbl.field2 
      ,tbl.field3
      ,tbl.field4
      ,tbl.field5
      ,tbl.field6
      ,tbl.field7
      ,tbl.field8
      ,tbl.field9
FROM OPENQUERY (LOCALSERVER ,'SET NOCOUNT ON; SET FMTONLY OFF EXEC [database].[dbschema].[Stored_Procedure_For_UDF]') tbl

select tbl.field1

,tbl.field2

,tbl.field3

,tbl.field4

,tbl.field5

,tbl.field6

,tbl.field7

,tbl.field8

,tbl.field9

FROM OPENQUERY (LOCALSERVER ,'SET NOCOUNT ON; SET FMTONLY OFF EXEC [database].[dbschema].[Stored_Procedure_For_UDF]') tbl

Некоторые полезные скрипты для MS SQL

Получить выполняемые скрипты на сервере MS SQL

select DB_NAME(pr.dbid) AS 'DB'
	   ,pr.spid
	   ,pr.status
	   ,RTRIM(pr.loginame) AS 'Login'
	   ,pr.program_name AS 'Program'
	   ,txt.[text] AS 'sql_query'
from master.dbo.sysprocesses pr
outer apply sys.[dm_exec_sql_text](pr.[sql_handle]) as txt;

select DB_NAME(pr.dbid) AS 'DB'

,pr.spid

,pr.status

,RTRIM(pr.loginame) AS 'Login'

,pr.program_name AS 'Program'

,txt.[text] AS 'sql_query'

from master.dbo.sysprocesses pr

outer apply sys.[dm_exec_sql_text](pr.[sql_handle]) as txt;

Получить дату и время модификации объектов в MS SQL

 SELECT SCHEMA_NAME(schema_id) + '.' + name, type_desc, create_date, modify_date
 FROM sys.objects
 WHERE type IN (
 'V'
 ,'IF'
 ,'TF'
 ,'U'
 ,'P'
 ,'FN'
 )
 order by modify_date DESC;

SELECT SCHEMA_NAME(schema_id) + '.' + name, type_desc, create_date, modify_date

FROM sys.objects

WHERE type IN (

'V'

,'IF'

,'TF'

,'U'

,'P'

,'FN'

)

order by modify_date DESC;

Получить настроенные Linked Servers

SELECT @@SERVERNAME AS Server
       ,Server_Id AS LinkedServerID
       ,name AS LinkedServer
	   ,product
	   ,provider
	   ,data_source
	   ,location
	   ,catalog
	   ,connect_timeout
	   ,query_timeout
	   ,modify_date
FROM    sys.servers
ORDER BY name;

SELECT @@SERVERNAME AS Server

,Server_Id AS LinkedServerID

,name AS LinkedServer

,product

,provider

,data_source

,location

,catalog

,connect_timeout

,query_timeout

,modify_date

FROM sys.servers

ORDER BY name;

Настроить права доступа к объекту MS SQL

Общий синтаксис:

GRANT SELECT ON {Объект БД} TO {Пользователь}

1	GRANT SELECT ON {Объект БД} TO {Пользователь}

Рабочий пример:

GRANT SELECT ON [DB].[dbo].[Name_of_table] TO "DOMAIN\IVShamaev"

1	GRANT SELECT ON [DB].[dbo].[Name_of_table] TO "DOMAIN\IVShamaev"

Получить «негодяев», которые держат таблицу/вьюху и т.п. объекты

select DB_NAME(pr.dbid) AS 'DB'
	   ,pr.spid
	   ,pr.status
	   ,RTRIM(pr.loginame) AS 'Login'
	   ,pr.program_name AS 'Program'
	   ,txt.[text] AS 'sql_query'
from master.dbo.sysprocesses pr
outer apply sys.[dm_exec_sql_text](pr.[sql_handle]) as txt
where txt.[text] like '%D02_Axapta_OPEX_CAPEX_t%';

select DB_NAME(pr.dbid) AS 'DB'

,pr.spid

,pr.status

,RTRIM(pr.loginame) AS 'Login'

,pr.program_name AS 'Program'

,txt.[text] AS 'sql_query'

from master.dbo.sysprocesses pr

outer apply sys.[dm_exec_sql_text](pr.[sql_handle]) as txt

where txt.[text] like '%D02_Axapta_OPEX_CAPEX_t%';

«Убить» сессии

kill 84; -- убиваем spid = 84
kill 139; -- убиваем spid = 139

1 2	kill 84; -- убиваем spid = 84 kill 139; -- убиваем spid = 139

Объекты, которые используют OPENQUERY

USE [ess_xmla]
SELECT * from sys.sql_modules
where definition like '%OPENQUERY%'

USE [ess_xmla]

SELECT * from sys.sql_modules

where definition like '%OPENQUERY%'

Схема элементов синтаксиса MS SQL

Введение основы T-SQL и примеры, которые будут разобраны

Немного о циклах, условиях в T-SQL

IF … ELSE

Использование ELSE

Временная таблица и табличная переменная

Создание локальной временной таблицы (Local Temp Tables)

Создание глобальной временной таблицы (Global Temp Tables)

Создание табличной переменной (Table Variables)

Переменные

Локальные переменные

Глобальные переменные

Объявление и инициализация переменной

Использование TRY … CATCH — обработка ошибок

Использование цикла WHILE

Курсор

WITH конструкция в MS SQL

Схема использования конструкции WITH в VIEW (во вьюхе)

Триггеры

Компоненты триггера

Типы триггеров

Структура триггера

Примеры триггеров

Хранимые процедуры

Структура хранимой процедуры

Пример хранимой процедуры

Функции, определенные пользователем (UDF)

Виды табличных функций

Структура табличной функции

Пример табличной функции

Структура Inline табличной функции

Пример Inline табличной функции

Создание User-Defined Scalar Functions (скалярных функций)

Пример

Использование OPENQUERY с набором данных (RESULT SET)

«Фиктивный» SELECT запрос для вызова хранимой процедуры из табличной функции

Некоторые полезные скрипты для MS SQL

Получить выполняемые скрипты на сервере MS SQL

Получить дату и время модификации объектов в MS SQL

Получить настроенные Linked Servers

Настроить права доступа к объекту MS SQL

Получить «негодяев», которые держат таблицу/вьюху и т.п. объекты

«Убить» сессии

Объекты, которые используют OPENQUERY

Дополнительные ресурсы