複数値(テーブル)を返すストアドファンクション

以下のようなテーブルを考えます。このテーブルのうち、Salary列が100000以上の行のName列とSalary列を取得したいと思います。

Id	Name	Salary
0001	Tom	48000
0002	Ada	2506070
0003	Jacob	406800
0004	Scott	20
0005	Tiger	0
0006	Hoge	123456

当然、こんな感じで簡単にかけます。

SELECT Name, Salary 
FROM   Test 
WHERE  Salary >= 100000 
ORDER BY Name

元テーブルの順番はId列が主キーなのでAda->Jacob->Hogeですが、Nameの辞書順はAda->Hoge->Jacobです。Order Byをつけたり外したりすれば順番が変わることがわかるでしょう。

Name	Salary
Ada	2506070
Hoge	123456
Jacob	406800

では、これをあえて下限値を受け取って該当データを返すストアドファンクションFindOverSalaryとした場合、ストアドの定義はどうなるでしょうか。しかも、SQLはSELECT * FROM ストアド名(下限値);で固定とし、Where句やORDER BY句の指定を不可能とします。

間違った答え

以下のように単純にストアド定義するだけではうまくいきません。

CREATE FUNCTION FindOverSalary(@lowerlimit INT)
RETURNS @ret TABLE (
  Name nvarchar(8) NOT NULL,
  Salary INT NOT NULL
) BEGIN 
INSERT INTO @ret 
  SELECT Name, Salary 
  FROM   Test 
  WHERE  Salary >= 100000 
  ORDER BY Name
RETURN END

このストアドを使ってSELECT * FROM FindOverSalary(100000);とした結果は以下になります。JacobとHogeの順番が逆になっており、Order Byがない場合のSELECTクエリと同じ結果になってしまいます。

Name	Salary
Ada	2506070
Jacob	406800
Hoge	123456

正しい答え

テーブル値を返すストアドファンクションは、無名のテーブルを構築してその中にデータをセットすることで値を返します。ストアドの定義としてもSELECT-INSERT文になっていますよね。ということは、データをセットするときにOrder Byしても、Insertされるテーブルが順番を持っていないと無意味ということです。
テーブルに順番を制御させるにはどうしたらよいか？答えは簡単です、Indexを貼ればよいのです。
よって正しくは以下となります。

CREATE FUNCTION FindOverSalary(@lowerlimit INT)
RETURNS @ret TABLE (
  Name nvarchar(8) NOT NULL,
  Salary INT NOT NULL,
  INDEX index_for_orderby CLUSTERED(Name)
) BEGIN 
INSERT INTO @ret 
  SELECT Name, Salary 
  FROM   Test 
  WHERE  Salary >= 100000 
RETURN END

これでSELECT * FROM ストアド名(値);でも、並び替えされた結果を得ることができました。

実例

そもそもSELECT文でOrder By句を使えるならそうしたほうがよいです。並び替えするかどうかはデータを見る人が制御するべきであり、ストアドファンクションの役割は引数に従って所定のデータを返すことだけであるからです。
しかし、抽出するストアドが動的に変わるうえ、取得結果の構造も違うみたいな要件（例えばDBにストアド名をもたせて検索条件と表示内容を動的に変えるようなケース）だと**「SQLはSELECT * FROM ストアド名(下限値);で固定とし、Where句やORDER BY句の指定を不可能とします。」**という仕様にするしかないこともあります。そのようなケースにおいて、ストアドファンクションでの無名テーブルへのINDEX貼りというテクニックを覚えておくと役に立つことがあるかもしれません。
~~もっといい仕様を考えたほうがいいかもしれません~~

デモデータのクエリ

CREATE TABLE Test( 
  Id nvarchar(4) NOT NULL
  , Name nvarchar(8) NOT NULL
  , Salary INT NOT NULL
  , PRIMARY KEY (Id)
);
INSERT INTO Test
VALUES ('0001', 'Tom', 48000)
, ('0002', 'Ada', 2506070)
, ('0003', 'Jacob', 406800)
, ('0004', 'Scott', 20)
, ('0005', 'Tiger', 0)
, ('0006', 'Hoge', 123456)