システム開発備忘録 SQL Server テーブル値関数

テーブルにID列を作成する

テーブル値関数とは戻り値としてテーブルのようなものを返す関数
・使い方はACCSESSのクエリに近い。（SQL上でテーブルと同じように扱える）
・パラメータを上手く使うと、有効な使い方ができそう。

基本的な書き方

-- =============================================
-- テーブル値関数の作成
-- 社員マスタの指定した社員コードの社員を呼び出す関数
-- =============================================
CREATE FUNCTION [dbo].[TestFunction1] (@Cd char(7))
RETURNS TABLE
AS
RETURN
(
  SELECT 社員コード
  , 社員名漢字
  , 部門コード
  FROM dbo.社員マスタ
  WHERE dbo.社員マスタ.社員コード= @Cd
   and (削除フラグ!= '1')
);

-- =============================================
-- 関数の呼び出し
-- =============================================
/*社員コード'0000001'の社員の情報呼び出す*/
select * from TestFunction1('0000001')

-- =============================================
-- テーブル値関数の作成
-- 社員マスタの指定した部門コード、雇用区分で抽出した結果を呼び出す関数
--部門コード、雇用区分は’’を指定すると指定無しとなる
-- =============================================
CREATE FUNCTION [dbo].[TestFunction2] (
   @Cd1 char(9),@Cd2 char(1))
RETURNS @T TABLE (
  [社員コード]char(7)
  , [社員名漢字]varchar(40)
  , [部門コード]char(9)
  , [雇用区分]char(1)
)
AS
begin

  INSERT @T
  SELECT 社員コード
  , 社員名漢字
  , 部門コード
  , 雇用区分
  FROM dbo.社員マスタ
  WHERE 削除フラグ!= '1'
  /*パラメータが''の場合以外は部門コードを抽出条件とする*/
  and (case @Cd1
   when '' then ''
   else 社員マスタ.部門コード
   end) = @Cd1
  /*パラメータが''の場合以外は雇用区分を抽出条件とする*/
  and (case @Cd2
   when '' then ''
   else 社員マスタ.雇用区分
   end) = @Cd2
return
end

-- =============================================
-- 関数の呼び出し
-- =============================================
/*部門コード'00002'、雇用区分'2'の社員のリストを呼び出す*/
select * from TestFunction2('00002','2')

/*部門コードを指定せずに雇用区分'2'の社員のリストを呼び出す*/
select * from TestFunction2('','2')