T-SQL で関数またはストアド プロシージャからテーブルを返す

T-SQL では、関数またはストアド プロシージャからテーブルを返すことができます。これは、複数の値を返す必要がある場合や、複雑なクエリをカプセル化したい場合に便利です。

方法

テーブルを返す関数またはストアド プロシージャを作成するには、次の手順が必要です。

1. テーブル型変数 を宣言します。この変数は、返されるテーブルを格納します。
2. SELECT ステートメント を使用して、テーブル型変数にデータを入力します。

例

次の例では、GetCustomers という名前の関数を作成します。この関数は、Customers テーブルからすべてのレコードを返します。

CREATE FUNCTION GetCustomers()
RETURNS TABLE
AS
BEGIN
  DECLARE @Customers TABLE (
    CustomerID int,
    FirstName varchar(50),
    LastName varchar(50)
  )

  INSERT INTO @Customers
  SELECT *
  FROM Customers

  RETURN @Customers
END

この関数は、次のように呼び出すことができます。

SELECT *
FROM GetCustomers()

ストアド プロシージャを使用してテーブルを返すこともできます。ストアド プロシージャは、複数の T-SQL ステートメントを含む一連の命令です。

CREATE PROCEDURE GetCustomers
AS
BEGIN
  DECLARE @Customers TABLE (
    CustomerID int,
    FirstName varchar(50),
    LastName varchar(50)
  )

  INSERT INTO @Customers
  SELECT *
  FROM Customers

  SELECT *
  FROM @Customers
END

EXEC GetCustomers

メリット

関数またはストアド プロシージャからテーブルを返すことには、次のようなメリットがあります。

• コードの再利用性：複雑なクエリをカプセル化することで、コードを再利用することができます。
• コードの読みやすさ：複数の値を返す必要がある場合、コードをより読みやすくすることができます。
• パフォーマンス：テーブル型変数を使用することで、パフォーマンスを向上させることができます。

注意事項

• テーブル型変数は、ローカル変数である：テーブル型変数は、関数またはストアド プロシージャ内でローカル変数として宣言する必要があります。
• テーブル型変数は、呼び出し元に戻される：テーブル型変数は、呼び出し元に戻されます。

CREATE FUNCTION GetCustomersByCountry(@Country varchar(2))
RETURNS TABLE
AS
BEGIN
  DECLARE @Customers TABLE (
    CustomerID int,
    FirstName varchar(50),
    LastName varchar(50)
  )

  INSERT INTO @Customers
  SELECT *
  FROM Customers
  WHERE Country = @Country

  RETURN @Customers
END

この関数は、Country パラメータで指定された国籍を持つすべての顧客を返します。

CREATE PROCEDURE GetCustomersByCountry
  @Country varchar(2)
AS
BEGIN
  DECLARE @Customers TABLE (
    CustomerID int,
    FirstName varchar(50),
    LastName varchar(50)
  )

  INSERT INTO @Customers
  SELECT *
  FROM Customers
  WHERE Country = @Country

  SELECT *
  FROM @Customers
END

テーブル型変数を使用したストアド プロシージャ

CREATE PROCEDURE GetCustomersByCountry
  @Country varchar(2)
AS
BEGIN
  DECLARE @Customers TABLE (
    CustomerID int,
    FirstName varchar(50),
    LastName varchar(50)
  )

  INSERT INTO @Customers
  SELECT *
  FROM Customers
  WHERE Country = @Country

  SET @Customers = @Customers

END

OUTPUT パラメータを使用したストアド プロシージャ

CREATE PROCEDURE GetCustomersByCountry
  @Country varchar(2),
  @Customers OUTPUT TABLE (
    CustomerID int,
    FirstName varchar(50),
    LastName varchar(50)
  )
AS
BEGIN
  INSERT INTO @Customers
  SELECT *
  FROM Customers
  WHERE Country = @Country
END

複数テーブルを返す関数

CREATE FUNCTION GetCustomersAndOrders()
RETURNS TABLE
AS
BEGIN
  DECLARE @Customers TABLE (
    CustomerID int,
    FirstName varchar(50),
    LastName varchar(50)
  )

  DECLARE @Orders TABLE (
    OrderID int,
    CustomerID int,
    OrderDate datetime
  )

  INSERT INTO @Customers
  SELECT *
  FROM Customers

  INSERT INTO @Orders
  SELECT *
  FROM Orders

  SELECT *
  FROM @Customers c
  INNER JOIN @Orders o ON c.CustomerID = o.CustomerID

END

この関数は、Customers テーブルと Orders テーブルのすべてのレコードを結合して返します。

T-SQL でテーブルを返すその他の方法

テーブル スキャン

SELECT *
FROM Customers

ビュー

CREATE VIEW vw_Customers
AS
SELECT *
FROM Customers

SELECT *
FROM vw_Customers

インライン テーブル値関数

SELECT *
FROM (
  SELECT *
  FROM Customers
) AS t

外部テーブル

CREATE EXTERNAL TABLE Customers
(
  CustomerID int,
  FirstName varchar(50),
  LastName varchar(50)
)
WITH (
  LOCATION = 'C:\Data\Customers.csv',
  DATA_SOURCE = 'CSV'
)

SELECT *
FROM Customers

この外部テーブルは、C:\Data\Customers.csv ファイルのデータを返します。

XML

SELECT *
FROM Customers
FOR XML PATH('')

JSON

SELECT *
FROM Customers
FOR JSON PATH('')

• 複数の値を返す必要がある場合や、複雑なクエリをカプセル化したい場合は、関数またはストアド プロシージャを使用します。
• すべてのレコードを返す必要がある場合は、テーブル スキャンを使用します。
• 特定の列のみを返す必要がある場合は、SELECT ステートメントで列を指定します。
• 頻繁に使用するクエリを高速化したい場合は、ビューを使用します。
• 外部ソースのデータにアクセスする必要がある場合は、外部テーブルを使用します。
• データを XML 形式で返す必要がある場合は、FOR XML PATH('') を使用します。