Базовая процедура SQL Server

Для разработки базовой процедуры приведена таблица с базовыми типами данных SQL Server и соответствующими внутренними типами платформы:

Базовый скалярный тип SQL Server	Псевдонимы для типа SQL Server	Внутренний тип платформы
int	integer	bigint
bigint	-	bigint
decimal[(p[, s]]	Dec[(p[, s]]	double
numeric[(p[, s]]	-	double
float[(n)]	-	double
datetimeoffset	-	text
varchar[(n \| max)]	char varying character varying (n)	text
text	-	text
nvarchar[(n \| max)]	national character varying (n) national char varying (n)	text
ntext	national text	text
varbinary[(n \| max)]	binary varying (n)	bytea

Подробное описание типов SQL Server можно найти в описании Data Types, Data Type Synonyms.

Базовый табличный тип

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

CREATE TYPE <наименование типа> AS TABLE (
<имя столбца> <тип столбца>
);

Базовая хранимая процедура

В SQL Server есть хранимые процедуры и функции, которые имеют ряд важных отличий:

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

Для реализации базовых хранимых процедур неудобно использовать функции, так как из-за их ограничения не получится реализовать сценарии с изменением данных. Требуется реализовывать базовые хранимые процедуры на основе процедур. Если есть процедура «some_proc», то должен быть определен базовый табличный тип с именем «some_proc_return_type» для определения структуры выходной таблицы.

Связанный табличный тип для процедуры – это базовый табличный тип, имя которого отличается от имени связанной процедуры суффиксом «_return_type». Этот тип обозначает тип выходной таблицы процедуры.

Базовая хранимая процедура в MS SQL – это совокупность хранимой процедуры и связанного табличного типа. Требования к процедуре повторяют требования к базовой хранимой процедуре, кроме пункта про возвращаемую структуру.

Пример

Структура таблицы, получаемая на выходе:

id (int). Идентификатор;
name (varchar). Название фрукта.

Создание типа:

CREATE TYPE [fruits_get_return_type] AS TABLE(
[id] [int] NULL,
[name] [nvarchar](max) NULL
)

Создание таблицы фруктов:

CREATE TABLE [fruits_struct](
   [id] [int] IDENTITY(1,1) NOT NULL,
   [name] [nvarchar](max) NULL
   ) ON [PRIMARY]
GO

Процедура, принимающая входной скалярный параметр «id» типа int, извлекает строку из таблицы фруктов:

CREATE PROCEDURE [fruits_get] @id INT
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM [fruits_struct] WHERE id = @id;
END
GO

См. также:

Настройка интеграции с табличными источниками данных | Базовая процедура 1С (SOAP)