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

Введение

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

В чем плюсы такого подхода? 

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

Во-вторых, они выполняются на стороне базы данных, а не в коде приложения. Т.е. минимум тратится ресурсов на дополнительные обращения к базе данных. 

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

Создание хранимой процедуры в SQL Server ManagementStudio

Для создания хранимой процедуры требуется выполнить следующие шаги:

выбираем базу данных, переходим на вкладуку "Программирование/Хранимые процедуры"

Создаем хранимую процедуру через контекстное меню:

Видим вот такой код:

Рассмотрим пример создания процедуры. Очищаем все и вставляем в поле следующий код:

CREATE PROCEDURE GetStudents
AS
     SELECT * FROM Students
GO

После этого нажимаем "Выполнить" (F5) и видим слева нашу хранимую процедуру:

Чтобы запустить на выполнениех хранимую процедуру, достаточно кликнуть по ней правой кнопкой мышки и выбрать пункт "выполнить хранимую процедуру". После чего отобразится вот такое окно, в котором нажимаем "ОК":

После чего увидим результат выполнения нашей хранимой процедуры:

Вызов нашей хранимой процедуры осуществляется с помощью кода (после чего нажимаем "выполнить" или F5):

USE sampledb;

EXEC GetStudents

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

1	str@gmail.com	Иван	Иванов	г. Рязань, ул. Ленина 54/2	50000,00
2	str@yandex.ru	Петр	Петров	г. Рязань, ул. Ленина 54/3	50000,00
3	ilya@gmail.com	Илья	Ильин	г. Рязань, ул. Ленина 54/4	40000,00
4	vp@gmail.com	Иван	Прохоров	г. Рязань, ул. Ленина 57/8	40000,00
5	bak@gmail.com	Борис	Акунин	г. Москва, ул. Лебедева 23/21	60000,00
6	el@gmail.com	Екатерина	Ларина	г. Шахты, ул. Пражская 4/9	60000,00
7	eb@gmail.com	Елизавета	Бродская	г. Рязань, ул. Ленина 54/2	90000,00

Внутрениие элементы хранимых процедур

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

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

CREATE PROCEDURE proc1
   @s1 nvarchar(128),
   @s2 int
AS
BEGIN
   select @s1 + cast(@s2 as nvarchar)

END
GO

Вызов процедуры: 

exec proc1 @s1='123', @s2 = 0

Параметры также могут быть выходными - т.е. их значение изменено в процедуре и возвращено в вызывающую сторону. 

CREATE PROCEDURE proc2
   @s1 nvarchar(128),
   @s2 int,
   @s3 nvarchar OUTPUT
AS
BEGIN
   set @s3 = @s1 + cast(@s2 as nvarchar)

END
GO

Вызов процедуры: 

declare @test nvarchar(max)=''
exec proc1 @s1='123', @s2 = 0, @s3 = @test

print @test

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

Пример хранимой процедуры с условием if:

CREATE PROCEDURE checkMaxAward
AS
BEGIN
	DECLARE @maxAward money
 
SELECT @maxAward = MAX(award) FROM Students
 
IF (@maxAward > 100000) begin
    PRINT 'максимальная сумма премии больше 100000';
end ELSE begin
    PRINT 'максимальная сумма премии меньше 100000';
end

END
GO

В данной процедуре выбираем максимальную сумму премии у студентов, в зависимости от результата выводим нужную строку. Также следует обратить внимание на выражение:

DECLARE @maxAward money

DECLARE применяется для определения переменных, после ключевого слова "DECLARE" указывается название и тип переменной. При этом название локальной переменной должно начинаться с символа @. В данном примере определяем переменную "@maxAward" с типом данных "money".

В нашем случае результат будет такой:

максимальная сумма премии меньше 100000

Циклы в хранимых процедурах SQL Server

Дальше разберем использование циклов. Разберем классический пример: вычисление факториала числа. Используем такой код:

DECLARE @number INT, @factorial INT
SET @factorial = 2;
SET @number = 10;
 
WHILE @number > 0
    BEGIN
        SET @factorial = @factorial * @number
        SET @number = @number - 2
    END;
 
PRINT @factorial

Пояснения к коду: пока переменная @number не будет равна 0, будет продолжаться цикл WHILE. Каждый проход цикла называется итерацией. В каждой итерации будет переустанавливаться значение переменных @factorial и @number.

Результатом выполнения данного кода будет:

7680

Также следует обратить внимание на ключевое слово "PRINT", которое  выводит результат нашего кода:

Инструкция OUTPUT

OUTPUT – это инструкция, возвращающая изменившиеся строки в результате выполнения инструкций INSERT, UPDATE, DELETE или MERGE.

OUTPUT может помочь в тех случаях, когда нужно проверить или узнать какие именно строки (записи) были добавлены, удалены или изменены, без дополнительных запросов на выборку (SELECT). Данная инструкция позволяет сохранить все изменения в отдельном месте, например, в таблице, благодаря этому мы можем узнать и работать, например, со списком всех идентификаторов, которые были сгенерированы и добавлены в таблицу.

Принцип работы OUTPUT: все изменения, которые производят инструкции INSERT, UPDATE, DELETE и MERGE, фиксируются, условно говоря, во временных таблицах Inserted и Deleted. Они имеют такую же структуру, как и целевая таблица. Для того чтобы посмотреть изменения, нам необходимо в инструкции OUTPUT указать соответствующий префикс и название нужного столбца, примерно так же, как мы это делаем в инструкции SELECT, перечисляя названия столбцов, тем самым мы извлечем данные из этих таблиц.

Преобразование типов данных для переменных

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

CAST(выражение AS тип_данных)

Пример. Есть такой код:

SELECT 'Средняя премия = '+ CAST(AVG(award) AS CHAR(15)) 
FROM Students;

Преобразуем числовое значение "award".

Результатом данного кода будет:

Средняя премия =   55714.29

Также есть функция TRY_CAST для преобразования данных. Функция TRY_CAST пытается преобразовать выражение из одного типа данных в другой тип данных. Если преобразование не удалось, функция вернет NULL. В противном случае вернет преобразованное значение.

Пример, следующий код пытается преобразовать строку "test" к типу float:

SELECT   
    CASE WHEN TRY_CONVERT(float, 'test') IS NULL   
    THEN 'Cast failed'  
    ELSE 'Cast succeeded'  
END AS Result;  
GO  

Результатом данного кода будет:

Cast failed

Отличие TRY_CAST от CAST заключается в том, что если преобразование не удалось, то TRY_CAST вернет NULL, а CAST вызовет исключение.

Конкатенация строк

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

SELECT  email, CONCAT(firstName, lastName) from Students

Результат данного кода будет следующий:

str@gmail.com	ИванИванов
str@yandex.ru	ПетрПетров
ilya@gmail.com	ИльяИльин
vp@gmail.com	ИванПрохоров
bak@gmail.com	БорисАкунин
el@gmail.com	ЕкатеринаЛарина
eb@gmail.com	ЕлизаветаБродская

Данная функция склеивает firstName и lastName в один столбец.

Стандартные функции для работы с датой и временем

Рассмотрим стандартную функцию GETDATE(), которая возвращает текущую локальную дату и время на основе системных часов в виде объекта datetime:

SELECT  getdate()

Вернет результат:

2021-11-08 21:19:58.843

Функция dateadd добавляет к дате некое значение (месяцы, дни, недели, минуты т.д.)

select dateadd(day, 1, getdate())

Работа с NULL через ISNULL, NULLIF

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

select * from table1 where a1 is null

Для замены NULL на какое-то значение используйте функцию ISNULL. Если оно равно NULL, то функция возвращает значение, которое передается в качестве второго параметра:

ISNULL(выражение, значение)

Выберем всех студентов из таблицы Students, а у которых значение email NULL, заменим на надпись "неизвестно":

SELECT firstName, lastName,
              ISNULL(email, 'неизвестно') AS Email
FROM Students

Результат этого запроса ниже:

Иван	Иванов	str@gmail.com
Петр	Петров	str@yandex.ru
Илья	Ильин	ilya@gmail.com
Иван	Прохоров	vp@gmail.com
Борис	Акунин	bak@gmail.com
Екатерина	Ларина	el@gmail.com
Елизавета	Бродская	eb@gmail.com
Семен	Зюзин	неизвестно

Последняя строка поле email было заменено на "неизвестно", т.к. имеет значение NULL.

Рассмотрим другую функцию: NULLIF. Она возвращает нулевое значение, если два указанных выражения равны. Например:

SELECT NULLIF (4,4) AS Same, NULLIF (5,7) AS Different;

 Результат:

NULL	5

возвращает NULL для первого столбца (4 и 4), потому что два входных значения одинаковы. Второй столбец возвращает первое значение (5), потому что два входных значения различны.

Полезно знать о некоторых важных системных процедурах. А именно: 

  • sp_help SP_Name : используется для получения информации о названиях параметров процедуры, их типах и т.д. Эта процедура может быть применена к любому объекту БД (таблица, триггер и т.п.)
  • sp_helptext SP_Name : используется для получения текста хранимой процедуры

Пример первая функция sp_help. Используем вот такой код:

sp_help Students

Результат выполнения данной функции ниже:

Здесь мы видим подробную информацию о таблице Students.

Пример использования второй функции: 

sp_helptext GetStudents

Результат ее выполнения:

Что еще посмотреть по SQL Server

SQL-инструмент для создания личных кабинетов на сайте

Суть подхода и история создания Falcon Space
Веб-платформа для создания личных кабинетов

Платформа Falcon Space

Это снижение стоимости владения

за счет меньшего количества людей для поддержки

Это быстрое внесение изменений

по ходу эксплуатации программы

Это современный интерфейс

полная адаптация под мобильные устройства

Веб-приложения на MS SQL. Партнерская программа для разработчиков и веб-студий

Вы можете разрабатывать самостоятельно или сотрудничать с нами в плане веб-разработки на платформе Falcon Space, используя только SQL и HTML.
Смотреть примеры с кодом SQL
Документация по платформе
Работа на MS SQL Server
Демо-стенд компонентов
На стенде можно посмотреть различные компоненты в действии: таблицы, формы, модальные окна, диаграммы, карта и т.д.
Демо-сайт решений
Базисные решения, которые можно гибко адаптировать под себя: менять внешний вид, бизнес-логику и даже структуру базы данных.
Получить оценку проекта
Создайте концепцию проекта на основе нашего шаблона и получите оценку проекта в виде КП.