こんにちは、あおいです。
業務でSQL Serverを触っているので、たまにはT-SQLの記事も書こうと思ったり。

さて、T-SQL (Transact-SQL) とは Microsoft と Sybaseによって 開発されたスタンダードの SQL (Structured Query Language) を拡張した言語で、Microsoft SQL Server で使われています。

T-SQLには集計関数・日付関数・文字列操作の関数など便利な関数が用意されています。
Transact-SQL リファレンス | Microsoft Docs
Microsoft SQL データベース関数 | Microsoft Docs

また、ユーザー定義で独自の関数、ストアドファンクションを作成することも可能です。

ストアドファンクションでは、入力パラメータに対して複雑な処理を行って、戻り値を結果として返します。
ストアドファンクションには一つの値を返すスカラー関数とテーブルを返すテーブル関数があります。

利点として、複数クエリの呼び出しをまとめることでカプセル化が可能、構文解析を完了させた状態のため実行速度が速い、等が挙げられます。

そこで、今回はSQL Serverでストアドファンクションのスカラー関数を作成する方法を紹介したいと思います。

まず、以下のスクリプトを実行します。

DROP TABLE IF EXISTS Customer

CREATE TABLE Customer(
	CustomerID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
	Name NVARCHAR(20),
	Job NVARCHAR(20),
	InvestmentHistory INT
)

INSERT INTO Customer
	(Name, Job, InvestmentHistory)
VALUES
(N'佐藤一郎', N'公務員', 1),
(N'鈴木二郎', N'会社員', 3),
(N'高橋三郎', N'大学生', 0),
(N'田中四郎', N'エンジニア', 5),
(N'伊藤五郎', N'経営者', 20)

SELECT文でテーブルを確認します。

スカラー関数の基本構文は以下の通りです。
戻り値を返すので、必ず最後にRETURNをします。

CREATE FUNCTION ［ファンクション名 ］ (
    ［＠パラメータ名］［パラメータ名のデータ型］
)  
RETURNS ［戻り値のデータ型］
AS
BEGIN

    ［処理内容］

    RETURN ［戻り値］

END

上記の基本構文を参考に、以下のスカラー関数を作成します。
以下のスカラー関数(getBestFinancialProducts)は顧客IDのパラメータを渡して、CASE文で投資歴の年数に応じた金融商品の値を返します。

CREATE FUNCTION getBestFinancialProducts (
	@CustomerID INT
)  
RETURNS NVARCHAR(20)
AS
BEGIN
	
	DECLARE @BestFinancialProducts NVARCHAR(20),
			@InvestmentHistory INT
	
	SELECT @InvestmentHistory = InvestmentHistory
	FROM Customer
	WHERE CustomerID = @CustomerID

	SET @BestFinancialProducts = 
		CASE WHEN @InvestmentHistory = 0 THEN 'FUND'
		     WHEN @InvestmentHistory >= 1 AND @InvestmentHistory <= 5 THEN 'STOCK'
			 WHEN @InvestmentHistory >= 6 AND @InvestmentHistory <= 10 THEN 'CFD'
			 WHEN @InvestmentHistory > 10 THEN 'FX'
			 ELSE ''
		END

    RETURN @BestFinancialProducts

END

スクリプトを実行後、［Database］⇒［Programmability］⇒［Functions］⇒［Scalar-valued Functions］を確認すると、ユーザー定義のスカラー関数が作成されています。

試しに、作成したスカラー関数を使ってみます。

また、既存のスカラ―関数を変更したい場合、ALTER FUNCTIONのクエリを流します。
変更したいスカラ―関数を右クリックで［Modify］を選択すると、クエリが画面に表示されます。

削除したい場合、DROP FUNCTIONのクエリを流します。

DROP FUNCTION ［ファンクション名］

今回はSQL Serverでストアドファンクションのスカラー関数を作成する方法を紹介させていただきました。
注意点として、ストアドファンクションではテーブルの値を挿入・変更・削除等のスコープ外のリソースを変更するような処理はできません。
スコープ外のリソースの変更処理をしたい場合、ストアドプロシージャを使いましょう。

次回はB-Treeインデックス、あるいはカーソルの記事でも書こうかな。

参考記事
ユーザー定義関数 | Microsoft Docs
ストアドプロシージャ | Microsoft Docs