Программирование на T-SQL. Хранимые процедуры. Создание хранимых процедур. Переменные, параметры и коды возврата

2.1.     Создание хранимых процедур 

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

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

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

Если вы хотите изменить содержимое хранимой процедуры или ее параметры, можно применить инструкцию ALTERPROCEDURE.  

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

2.2.      Переменные, параметры и коды возврата 

Переменные

Переменные обеспечивают манипулирование данными, их хранение и передачу как внутри процедуры, так и между хранимыми процедурами. 

В SQL Server есть два вида переменных: локальные и глобальные. 

Локальная переменная обозначается одним символом "эт" (@), а глобальная переменная — двумя (@@). Кроме того, локальные переменные вы можете создавать, читать и писать в них, а глобальные переменные можно только читать. 

Ниже перечислены некоторые самые популярные глобальные переменные.

Локальную переменную можно задать с помощью инструкции DECLARE, в которой указываете имя и тип данных переменной. Одна инструкция DECLARE может использоваться для задания нескольких переменных 

Переменной можно присвоить статическое значение или значение, возвращаемое инструкцией SELECT. Для присваивания значения можно применять инструкцию SET или инструкцию SELECT, но если вы выполняете запрос, для присвоения значения вы должны использовать инструкцию SELECT. Инструкция SELECT также применяется для извлечения значения переменной.

Помимо присваивания значения с помощью инструкций SET или SELECT вы можете также присвоить значение во время объявления переменной.

Примеры задания значений переменных разными способами

                          USE Sale

GO

-- объявление переменных declare @a int=(select max(price) from dbo.product)

 , @b int

 , @c int

 , @d int

-- присвоение при помощи команды select select @b= max(price) from dbo.product -- присвоение при помощи команды set set @c= (select max(price) from dbo.product) set @d=@a + @b

-- вывод значений переменных select @a,@b,@c, @d  Параметры

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

Объявление параметров

CREATE PROCEDURE Proc1

                                   @parm1 INT

                                   @parm2 VARCHAR(20)= 'Значение по умолчанию' 

AS 

--Блок кода

Можно задавать параметры двух типов: входные и выходные.  

Выходной параметр обозначается ключевым словом OUTPUT

CREATE PROCEDURE Proc2

                                      @parm1 INT

                                    , @parm2 VARCHAR(20)= 'Значение по умолчанию'

                                    , @orderid int OUTPUT 

AS 

--Блок кода