В SQL Server 2008 Microsoft представила параметры с таблицами, которые позволяют передавать таблицу в качестве параметра в хранимую процедуру. Эта функция предоставляет несколько преимуществ, включая улучшенную производительность и снижение повторной компиляции операторов.

Шаги по созданию и использованию параметров с таблицами

Вот шаги, необходимые для создания и использования параметров с таблицами:

1. Создайте тип таблицы, который определяет структуру переменной таблицы.
2. Создайте хранимую процедуру или функцию, которая принимает тип таблицы в качестве параметра.
3. Создайте переменную таблицы и вставьте данные в нее.
4. Вызовите хранимую процедуру или функцию и передайте переменную таблицы в качестве параметра.

Пример

Давайте рассмотрим пример, чтобы проиллюстрировать, как использовать параметры с таблицами.

Сначала мы создаем базу данных с именем “TestDB” и таблицу с именем “TestLocationTable” с использованием предоставленных SQL-инструкций DDL.

USE [master]
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'TestDB')
    DROP DATABASE TestDB
GO

CREATE DATABASE TestDB
GO

USE [TestDB]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestLocationTable]') AND type in (N'U'))
    DROP TABLE [dbo].[TestLocationTable]
GO

CREATE TABLE [dbo].[TestLocationTable](
    [Id] [int] NULL,
    [shortname] [char](3) NULL,
    [name] [varchar](100) NULL
) ON [PRIMARY]
GO

Затем мы вставляем некоторые данные в “TestLocationTable” с использованием предоставленных SQL-инструкций DML.

USE [TestDB]
GO

INSERT INTO TestLocationTable (Id, shortname, Name)
SELECT 1, 'NA1', 'NewYork'
INSERT INTO TestLocationTable (Id, shortname, Name)
SELECT 2, 'NA2', 'NewYork'
INSERT INTO TestLocationTable (Id, shortname, Name)
SELECT 3, 'NA3', 'NewYork'
INSERT INTO TestLocationTable (Id, shortname, Name)
SELECT 4, 'EU1', 'London'
INSERT INTO TestLocationTable (Id, shortname, Name)
SELECT 5, 'EU2', 'London'
INSERT INTO TestLocationTable (Id, shortname, Name)
SELECT 6, 'AS1', 'Tokyo'
INSERT INTO TestLocationTable (Id, shortname, Name)
SELECT 7, 'AS2', 'HongKong'
GO

Теперь давайте создадим тип таблицы с именем “OfficeLocation_Tabetype” с той же структурой, что и “TestLocationTable”.

USE [TestDB]
GO

IF EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'OfficeLocation_Tabetype' AND ss.name = N'dbo')
    DROP TYPE [dbo].[OfficeLocation_Tabetype]
GO

CREATE TYPE [dbo].[OfficeLocation_Tabetype] AS TABLE(
    [Id] [int] NULL,
    [shortname] [char](3) NULL,
    [name] [varchar](100) NULL
)
GO

Затем мы создаем хранимую процедуру с именем “usp_InsertProdLocation”, которая принимает тип таблицы в качестве параметра и вставляет данные в “TestLocationTable”, если они еще не существуют.

USE [TestDB]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_InsertProdLocation]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[usp_InsertProdLocation]
GO

CREATE PROCEDURE usp_InsertProdLocation @TVP OfficeLocation_Tabetype READONLY
AS
SET NOCOUNT ON

INSERT INTO TestLocationTable
SELECT ID, shortname, name
FROM @TVP
WHERE CONVERT(varchar(10), id) + shortname + name NOT IN (SELECT CONVERT(varchar(10), id) + shortname + name FROM TestLocationTable)
GO

Теперь давайте создадим переменную таблицы с именем “@TV” и немедленно выполним хранимую процедуру “usp_InsertProdLocation”.

USE [TestDB]
GO

DECLARE @TV AS [OfficeLocation_Tabetype]

INSERT INTO @TV (Id, Shortname, Name)
SELECT 12, 'ME1', 'Dubai'
INSERT INTO @TV (Id, Shortname, Name)
SELECT 13, 'ME2', 'Tehran'
INSERT INTO @TV (Id, Shortname, Name)
SELECT 17, 'EA1', 'Bombay'
INSERT INTO @TV (Id, Shortname, Name)
SELECT 18, 'EA2', 'Karachi'
INSERT INTO @TV (Id, Shortname, Name)
SELECT 3, 'NA3', 'NewYork'
INSERT INTO @TV (Id, Shortname, Name)
SELECT 4, 'EU1', 'London'

EXEC usp_InsertProdLocation @TV
GO

Наконец, мы можем запросить все данные из “TestLocationTable” с использованием оператора Transact-SQL.

USE [TestDB]
GO

SELECT * FROM TestLocationTable
GO

Результат отобразит все строки в “TestLocationTable”, включая те, которые были вставлены хранимой процедурой.

Заключение

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