【サンプル満載】SqlServer ストアド プロシージャ自由自在(stored procedure)

SqlServer、Azure SQL Database、Azure Synapse 上でストアドプロシージャを作成する場合、Transact-SQL と呼ばれる言語を使用します。

今回は、Transact-SQLを使ったストアドプロシージャの書き方について、サンプルプログラムを交えて解説します。

Transact-SQLは、他のデータベースと比べて少し特殊な書き方をするため、OracleやPostgreSqlの経験者にとっても役立つ記事になっています。

これから Transact-SQL を始めようという方は、是非ご一読ください。

ストアドプロシージャの概要
ストアドプロシージャの種類
最初に知っておきたい記述上のルール
事前準備
基本構文
制御構文
ファンクションについて
まとめ

ストアドプロシージャの概要

ストアドプロシージャとは、あらかじめデータベース内に登録しておき、必要に応じて呼び出して使うプログラムの事です。

通常は、アプリケーションからデータベースに対してSQLを実行する場合、実行したいSQLの数だけデータベースとの通信が発生します。

実行したいSQLをあらかじめデータベースに登録しておき、アプリケーションから呼び出すことで、データベースとの通信が１回で済みます。

数件程度のSQL実行であれば問題ありませんが、数千～数万件のSQLを発行するような場合には威力を発揮します。

また、ストアドプロシージャのメリットはこれ以外にも、

複雑な処理を共通化／部品化できる
あらかじめコンパイルされるため、処理が速くなる

などのメリットもあります。

ストアドプロシージャの種類

ストアドプロシージャには、「結果を返す」ものと、「結果を返さない」ものが存在します。また、「結果を返す」場合において、「複数の結果を返すもの」と「単一の結果を返すもの」が存在します。

「結果を返すもの」はストアドプロファンクション（stored function）と呼んでおり、「結果を返さない」ものはストアドプロシージャ（stored procedure）と呼んで区別します。

最初に知っておきたい記述上のルール

SQL Server や Azure SQL Databaseでストアドプロシージャ（以降はプロシージャと呼びます）を作成する場合、Transact-SQL と呼ばれる文法を使用します。

Transact-SQL は、Oracle や PostgreSQLなど、他のリレーショナルデータベースとは少し違った書き方をするため、最初にポイントを整理しておきます。

引数、変数は必ず@から始める
変数に配列は使えない
ループ処理はWHILEのみで、FOR文は存在しない
プロシージャから任意の値を返すことが出来る
ファンクションの中にSQLは記述できない
行末はセミコロンをつけることが推奨されているが、無くても動く
カラム名に特殊文字や予約語を含む場合、[ ] でくくる

上記のことを軽く念頭に置いて、以降の記事を読み進めてください。

事前準備

本記事では次のテーブルがあると仮定して説明しています。掲載しているソースコードの動作検証を行う場合、あらかじめ下記のSQLを実行し、テーブルを作成しておいて下さい。

CREATE TABLE Sales(
    StoreNumber INT NOT NULL, -- 店舗番号
    SalesDateTime DATETIME NOT NULL, -- 売上日時
    ProductID INT NOT NULL, -- 商品ID
    ProductName NVARCHAR(100), -- 商品名    
    Quantity INT NOT NULL, -- 個数
    UnitPrice DECIMAL(10,2) NOT NULL, -- 単価
    CONSTRAINT PK_Sales PRIMARY KEY (StoreNumber, SalesDateTime, ProductID)
);
INSERT INTO Sales VALUES (1000,'2023-04-01 12:10:00',10001,'ショートケーキ',10,300);
INSERT INTO Sales VALUES (1000,'2023-04-01 12:20:00',10002,'モンブラン',20,400);
INSERT INTO Sales VALUES (1000,'2023-04-01 12:30:00',10003,'ショコラケーキ',30,500);
INSERT INTO Sales VALUES (1000,'2023-04-02 12:10:00',10001,'ショートケーキ',10,300);
INSERT INTO Sales VALUES (1000,'2023-04-02 12:20:00',10002,'モンブラン',20,400);
INSERT INTO Sales VALUES (1000,'2023-04-02 12:30:00',10003,'ショコラケーキ',30,500);
INSERT INTO Sales VALUES (2000,'2023-04-01 12:10:00',10001,'ショートケーキ',15,300);
INSERT INTO Sales VALUES (2000,'2023-04-01 12:20:00',10002,'モンブラン',25,400);
INSERT INTO Sales VALUES (2000,'2023-04-01 12:30:00',10003,'ショコラケーキ',35,500);
INSERT INTO Sales VALUES (2000,'2023-04-02 12:10:00',10001,'ショートケーキ',15,300);
INSERT INTO Sales VALUES (2000,'2023-04-02 12:20:00',10002,'モンブラン',25,400);
INSERT INTO Sales VALUES (2000,'2023-04-02 12:30:00',10003,'ショコラケーキ',35,500);

CREATE TABLE Sales(

StoreNumber INT NOT NULL, -- 店舗番号

SalesDateTime DATETIME NOT NULL, -- 売上日時

ProductID INT NOT NULL, -- 商品ID

ProductName NVARCHAR(100), -- 商品名

Quantity INT NOT NULL, -- 個数

UnitPrice DECIMAL(10,2) NOT NULL, -- 単価

CONSTRAINT PK_Sales PRIMARY KEY (StoreNumber, SalesDateTime, ProductID)

);

INSERT INTO Sales VALUES (1000,'2023-04-01 12:10:00',10001,'ショートケーキ',10,300);

INSERT INTO Sales VALUES (1000,'2023-04-01 12:20:00',10002,'モンブラン',20,400);

INSERT INTO Sales VALUES (1000,'2023-04-01 12:30:00',10003,'ショコラケーキ',30,500);

INSERT INTO Sales VALUES (1000,'2023-04-02 12:10:00',10001,'ショートケーキ',10,300);

INSERT INTO Sales VALUES (1000,'2023-04-02 12:20:00',10002,'モンブラン',20,400);

INSERT INTO Sales VALUES (1000,'2023-04-02 12:30:00',10003,'ショコラケーキ',30,500);

INSERT INTO Sales VALUES (2000,'2023-04-01 12:10:00',10001,'ショートケーキ',15,300);

INSERT INTO Sales VALUES (2000,'2023-04-01 12:20:00',10002,'モンブラン',25,400);

INSERT INTO Sales VALUES (2000,'2023-04-01 12:30:00',10003,'ショコラケーキ',35,500);

INSERT INTO Sales VALUES (2000,'2023-04-02 12:10:00',10001,'ショートケーキ',15,300);

INSERT INTO Sales VALUES (2000,'2023-04-02 12:20:00',10002,'モンブラン',25,400);

INSERT INTO Sales VALUES (2000,'2023-04-02 12:30:00',10003,'ショコラケーキ',35,500);

基本構文

では、Tracsact-SQLの基本構文について解説したいと思います。

プロシージャの基本フォーマット

プロシージャの基本的なフォーマットは次の通りです。

プロシージャ内でSELECT文で値を取得し、変数に格納して使うケースが多々ありますが、Transact-SQLの場合はSELECT内で直接変数に値を代入することが可能です。

OracleやPostgreSQLでは、SELECTの値を取得する場合に INTO 変数1,・・・を記述する必要があったので、Transact-SQLにおけるこの記述は、他のデータベースから比べると斬新です。

ちなみに、引数にはデフォルト値を指定することが可能で、デフォルト値を指定した引数は省略可能になります。

以上の内容を踏まえたサンプルは次のようになります。

CREATE PROCEDURE  dbo.MyProcedure
    -- 引数の定義
    @ProductID INT,
    @StoreNumber INT = 1000
AS
BEGIN
    --変数の定義
    DECLARE @avg DECIMAL(10,2);
	
    --処理（指定した店舗番号における全商品単価の平均を求める）
    SELECT @avg = AVG(UnitPrice)
    FROM Sales 
    WHERE StoreNumber = @StoreNumber;

    --指定した店舗番号、商品IDの単価を上記で求めた平均値の2倍で書き換える
    UPDATE Sales
    SET UnitPrice = @avg * 2 
    WHERE StoreNumber = @StoreNumber AND ProductID = @ProductID;
END

CREATE PROCEDURE dbo.MyProcedure

-- 引数の定義

@ProductID INT,

@StoreNumber INT = 1000

BEGIN

--変数の定義

DECLARE @avg DECIMAL(10,2);

--処理（指定した店舗番号における全商品単価の平均を求める）

SELECT @avg = AVG(UnitPrice)

FROM Sales

WHERE StoreNumber = @StoreNumber;

--指定した店舗番号、商品IDの単価を上記で求めた平均値の2倍で書き換える

UPDATE Sales

SET UnitPrice = @avg * 2

WHERE StoreNumber = @StoreNumber AND ProductID = @ProductID;

END

呼び出し方法は2通りの記述方法があります。１つ目はプロシージャの引数定義に順番を合わせる方法、もう１つは引数名を指定する方法です。引数名を指定すれば、引数の順番は無視できます。

--引数を全て指定する場合
EXEC dbo.MyProcedure 1000,10002
--EXEC dbo.MyProcedure @StoreNumber = 1000,@ProductID = 10002

--第2引数を省略する場合
EXEC dbo.MyProcedure 1000
--EXEC dbo.MyProcedure @ProductID = 10002

--引数を全て指定する場合

EXEC dbo.MyProcedure 1000,10002

--EXEC dbo.MyProcedure @StoreNumber = 1000,@ProductID = 10002

--第2引数を省略する場合

EXEC dbo.MyProcedure 1000

--EXEC dbo.MyProcedure @ProductID = 10002

値を返すストアドの基本フォーマット

戻り値の返し方は２つあり、１つは引数に OUT 句を指定する方法、もう１つはプロシージャの最後に SELECT を記述する方法です。

引数で返す場合は、単に引数に値を代入するだけでOKです。SELECTを使う場合は、返したい値を続けて記述するだけですが、返す個数が１個の場合と２個以上の場合では、受け取り方が違ってきます。

尚、引数を使う方法とSELECTを使う方法は、同時に使うことが可能です。

引数とSELECTを使ったサンプル（SELECTで値を１個だけ返す場合）

下記は引数とSELECTを使って呼び出し元に値を返すサンプルです。

CREATE PROCEDURE  dbo.MyProcedure
    -- 引数の定義
    @ProductName nvarchar(100),
    @StoreNumber INT,
    @Total DECIMAL(10,2) OUT
AS
BEGIN
    --変数の定義
    DECLARE @count INT;
    DECLARE @sum DECIMAL(10,2);

    --処理
    SELECT @count = SUM(Quantity),
           @sum = SUM(UnitPrice * Quantity)
    FROM Sales 
    WHERE StoreNumber = @StoreNumber AND ProductName = @ProductName;

    -- 引数に値を返す
    SET @Total = @sum;

    -- 戻り値として値を返す
    SELECT @count ;
END

CREATE PROCEDURE dbo.MyProcedure

-- 引数の定義

@ProductName nvarchar(100),

@StoreNumber INT,

@Total DECIMAL(10,2) OUT

BEGIN

--変数の定義

DECLARE @count INT;

DECLARE @sum DECIMAL(10,2);

--処理

SELECT @count = SUM(Quantity),

@sum = SUM(UnitPrice * Quantity)

FROM Sales

WHERE StoreNumber = @StoreNumber AND ProductName = @ProductName;

-- 引数に値を返す

SET @Total = @sum;

-- 戻り値として値を返す

SELECT @count ;

END

SELECT で返す値が１個の場合は、変数に直接代入できます。引数で値を受け取るためには、変数の後に OUT を記述します。

DECLARE @count INT;
DECLARE @total DECIMAL(10,2);
--EXEC @count =  dbo.MyProcedure 'ショートケーキ',1000,@total OUT
EXEC @count =  dbo.MyProcedure @ProductName='ショートケーキ', @StoreNumber=1000,@total OUT

DECLARE @count INT;

DECLARE @total DECIMAL(10,2);

--EXEC @count = dbo.MyProcedure 'ショートケーキ',1000,@total OUT

EXEC @count = dbo.MyProcedure @ProductName='ショートケーキ', @StoreNumber=1000,@total OUT

SELECTで値を２個以上返す場合

下記のサンプルは、前述のサンプルでは引数@Total で呼び出し元に返していた値を、SELECTで返すよう修正したものです。

CREATE PROCEDURE  dbo.MyProcedure
    -- 引数の定義
    @ProductName nvarchar(100),
    @StoreNumber INT
AS
BEGIN
    --変数の定義
    DECLARE @count INT;
    DECLARE @sum DECIMAL(10,2);

    --処理
    SELECT @count = SUM(Quantity),
           @sum = SUM(UnitPrice * Quantity)
    FROM Sales 
    WHERE StoreNumber = @StoreNumber AND ProductName = @ProductName;

    -- 戻り値として値を返す
    SELECT @count,@sum ;
END

CREATE PROCEDURE dbo.MyProcedure

-- 引数の定義

@ProductName nvarchar(100),

@StoreNumber INT

BEGIN

--変数の定義

DECLARE @count INT;

DECLARE @sum DECIMAL(10,2);

--処理

SELECT @count = SUM(Quantity),

@sum = SUM(UnitPrice * Quantity)

FROM Sales

WHERE StoreNumber = @StoreNumber AND ProductName = @ProductName;

-- 戻り値として値を返す

SELECT @count,@sum ;

END

SELECT で値を2個以上返す場合は、単純に変数で受け取ることが出来ません。

EXEC dbo.MyProcedure 'ショートケーキ',1000

1	EXEC dbo.MyProcedure 'ショートケーキ',1000

この場合は TABLE形式のデータタイプ（データ型）を作成し、 INSERT INTO 変数名～と記述する必要があります。

以下の通り CREATE TYPE で TABLE 型の変数を定義し、INSERT INTO　変数名 EXEC ストアド名～　を実行することで、戻り値を変数に格納できます。

TABLE 型は名前が示す通り表形式のデータを保持するデータ型です。SELECTで2個の値を指定するということは、「２個のカラムを持つテーブルから、1行だけ値が返ってきた」という扱いになります。

CREATE TYPE SALES_TYPE AS TABLE( 
   
   SalesCount INT,
   Total DECIMAL(10,2)
);

DECLARE @sales SALES_TYPE;
INSERT INTO @sales EXEC dbo.MyProcedure 'ショートケーキ',1000

CREATE TYPE SALES_TYPE AS TABLE(

SalesCount INT,

Total DECIMAL(10,2)

);

DECLARE @sales SALES_TYPE;

INSERT INTO @sales EXEC dbo.MyProcedure 'ショートケーキ',1000

TALBE型から値を取り出すときは、後述するFETCH文を使います。

次は、SELECTを使ってテーブル丸ごと返すプロシージャを考えてみます。

例えば、複数のテーブルを結合し、かつ任意の項目を加工してテーブル形式で返す場合は、次の様に記述できます。

CREATE PROCEDURE  dbo.MyProcedure
AS
BEGIN
    SELECT '[' + t1.name+ ']' AS name,t1.age,t1.address  FROM  MY_DATA t1
　　LEFT JOIN MY_MASTER t2 ON t1.id = t2.id; 
END

CREATE PROCEDURE dbo.MyProcedure

BEGIN

SELECT '[' + t1.name+ ']' AS name,t1.age,t1.address FROM MY_DATA t1

　　LEFT JOIN MY_MASTER t2 ON t1.id = t2.id;

END

プロシージャの中でCreate Tableにより一時テーブルを作成すれば、 SELECT 文でその中身を丸ごと返すことも可能です。

一時テーブルの先頭に # を付けていますが、これを付けるとセッション内でのみ有効な（セッションが終わると自動で削除される）一時テーブルを作成することができます。

CREATE PROCEDURE  dbo.MyProcedure
AS
BEGIN
    -- 一時テーブルの作成
    CREATE TABLE #Temp(
        name NVARCHAR(50),
        age INT,
        address NVARCHAR(200)
    )

    -- 一時テーブルにデータを登録
    INSERT INTO #Temp
    SELECT '[' + t1.name+ ']' AS name,t1.age,t1.address  FROM  MY_DATA t1
　　LEFT JOIN MY_MASTER t2 ON t1.id = t2.id;
　  WHERE t1.id < 10000

    -- 一時テーブルを削除
    DROP TABLE #Temp
END

CREATE PROCEDURE dbo.MyProcedure

BEGIN

-- 一時テーブルの作成

CREATE TABLE #Temp(

name NVARCHAR(50),

age INT,

address NVARCHAR(200)

)

-- 一時テーブルにデータを登録

INSERT INTO #Temp

SELECT '[' + t1.name+ ']' AS name,t1.age,t1.address FROM MY_DATA t1

　　LEFT JOIN MY_MASTER t2 ON t1.id = t2.id;

　 WHERE t1.id < 10000

-- 一時テーブルを削除

DROP TABLE #Temp

END

通常はBIGIN～ENDで明示的なトランザクション制御が掛かるので、DROP TABLE ＃TEMPをわざわざ記述しなくても勝手に消えてくれますが、念のため最後に実行しています。

理由は、＃TEMPはセッション中はずっと存在し続けるため、別のストアド内で同じ名前（＃TEMP）でCREATEするとエラーになるからです。単純なテーブル名で作成する場合は、使い終わった時点でDROPしておく方が安心です。

RETURNで整数を返す

OracleやPostgreSQLのプロシージャでは、RETURN文を使って呼び出し元に値を返せませんでしたが、Transact-SQLでは値を返すことが可能です。

ただし、返せる値は整数値が1個だけです。浮動小数点を記述することは可能ですが、INT型にキャストされます。

この方法は、プロシージャの実行結果（成功、失敗、エラーコードなど）を呼び出し側に返したいときによく利用されます。

CREATE PROCEDURE  dbo.MyProcedure
    -- 引数の定義
    @ProductID INT,
    @StoreNumber INT
AS
BEGIN
    --処理
    --指定した店舗番号、商品IDの単価を0で書き換える
    UPDATE Sales
    SET UnitPrice = 0
    WHERE StoreNumber = @StoreNumber AND ProductID = @ProductID;
　　
　　RETURN 1
END

CREATE PROCEDURE dbo.MyProcedure

-- 引数の定義

@ProductID INT,

@StoreNumber INT

BEGIN

--処理

--指定した店舗番号、商品IDの単価を0で書き換える

UPDATE Sales

SET UnitPrice = 0

WHERE StoreNumber = @StoreNumber AND ProductID = @ProductID;

　　RETURN 1

END

DECLARE @status INT;
EXEC @status = dbo.MyProcedure 1000,10002
--EXEC  @status = dbo.MyProcedure @StoreNumber = 1000,@ProductID = 10002

DECLARE @status INT;

EXEC @status = dbo.MyProcedure 1000,10002

--EXEC @status = dbo.MyProcedure @StoreNumber = 1000,@ProductID = 10002

SELECT の直後に RETURN を記述した場合、SELECTの値が優先されます。

RETURNの後にSELECTを記述した場合、RETURNの値が返され、SELECTは実行されません。

制御構文

ここからは、制御構文について解説していきます。

条件分岐

条件分岐は次のように記述します。

下記は引数で渡された商品IDの値によって、文字列をPRINTするサンプルです。

CREATE PROCEDURE MyProcedure
    @productID INT
AS
BEGIN
    IF @productID = 10001
    BEGIN
        PRINT '売れ筋'
    END
    ELSE IF @productID = 10002
    BEGIN
        PRINT '格安'
    END
    ELSE IF @productID = 10003
    BEGIN
        PRINT '絶品'
    END
    ELSE
    BEGIN
        PRINT 'その他'
    END
END

CREATE PROCEDURE MyProcedure

@productID INT

BEGIN

IF @productID = 10001

BEGIN

PRINT '売れ筋'

END

ELSE IF @productID = 10002

BEGIN

PRINT '格安'

END

ELSE IF @productID = 10003

BEGIN

PRINT '絶品'

END

ELSE

BEGIN

PRINT 'その他'

END

実行すると次の様になります。

EXEC dbo.MyProcedure 10001

1	EXEC dbo.MyProcedure 10001

ループ処理

FOR文が用意されていないため全てのループはWHILEを使うことになります。

下記は引数で指定された件数だけループして、SalesテーブルにダミーデータをINSERTするサンプルです。

CREATE PROCEDURE dbo.MyProcedure
    @Count INT
AS
BEGIN
    DECLARE @counter INT = 1
    DECLARE @storeId INT = 3000

    WHILE @counter <= @Count
    BEGIN
        -- ダミーデータの生成
        DECLARE @salesDateTime DATETIME = GETDATE()
        DECLARE @productId INT = @counter
        DECLARE @productName NVARCHAR(100) = 'DUMMY_' + CAST(@counter AS NVARCHAR(10))
        DECLARE @quantity INT = 10
        DECLARE @unitPrice DECIMAL(10, 2) = 100.00

        -- データの挿入
        INSERT INTO Sales
        VALUES (@storeId, @salesDateTime, @productId, @productName, @quantity, @unitPrice)

        -- カウンターと店舗IDの更新
        SET @counter +=  1
        SET @storeId = @storeId + 1000
    END
END

CREATE PROCEDURE dbo.MyProcedure

@Count INT

BEGIN

DECLARE @counter INT = 1

DECLARE @storeId INT = 3000

WHILE @counter <= @Count

BEGIN

-- ダミーデータの生成

DECLARE @salesDateTime DATETIME = GETDATE()

DECLARE @productId INT = @counter

DECLARE @productName NVARCHAR(100) = 'DUMMY_' + CAST(@counter AS NVARCHAR(10))

DECLARE @quantity INT = 10

DECLARE @unitPrice DECIMAL(10, 2) = 100.00

-- データの挿入

INSERT INTO Sales

VALUES (@storeId, @salesDateTime, @productId, @productName, @quantity, @unitPrice)

-- カウンターと店舗IDの更新

SET @counter += 1

SET @storeId = @storeId + 1000

END

実行した結果、次の様になります。

EXEC dbo.InsertDummySalesData 20

1	EXEC dbo.InsertDummySalesData 20

FETCH 処理

テーブル（TABLE型含む）から値を順番に取り出すには FETCH 文を使います。

事前にカーソル型の変数を用意し、そこにSELECT文を登録しておきます。そして、OPEN文でカーソルを開き、FETCH　NEXT～でレコードを順番に取り出します。

@@FETCH_STATUS は取り出すレコードが無くなると0以外になるため、ループ条件に使います。＠が２つ連続しているのはTransact-SQL の予約変数だからです。

処理が終わったら、CLOSEでカーソルを閉じ、DEALLOCATEでカーソルが使っていたメモリを解放します。

下記はSalseテーブルから1レコードづつ取り出し、PRINT文でコンソールに出力するサンプルです。

CREATE PROCEDURE dbo.MyProcedure
AS
BEGIN
    -- カーソルの定義
    DECLARE @cursor CURSOR;

    -- SELECT文の実行とカーソルの設定
    SET @cursor = CURSOR FOR
    SELECT StoreNumber, SalesDateTime, ProductID, ProductName, Quantity, UnitPrice
    FROM Sales;

    -- 変数の宣言
    DECLARE @StoreNumber INT;
    DECLARE @SalesDateTime DATETIME;
    DECLARE @ProductID INT;
    DECLARE @ProductName NVARCHAR(100);
    DECLARE @Quantity INT;
    DECLARE @UnitPrice DECIMAL(10, 2);

    -- FETCHのループ
    OPEN @cursor
    FETCH NEXT FROM @cursor
    INTO @StoreNumber, @SalesDateTime, @ProductID, @ProductName, @Quantity, @UnitPrice

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 取得したデータの処理
        -- ここで取得したデータを使って何か処理を行うことができます
        PRINT 'StoreNumber: ' + CONVERT(VARCHAR(10), @StoreNumber)
        PRINT 'SalesDateTime: ' + CONVERT(VARCHAR(20), @SalesDateTime, 120)
        PRINT 'ProductID: ' + CONVERT(VARCHAR(10), @ProductID)
        PRINT 'ProductName: ' + ISNULL(@ProductName, '')
        PRINT 'Quantity: ' + CONVERT(VARCHAR(10), @Quantity)
        PRINT 'UnitPrice: ' + CONVERT(VARCHAR(20), @UnitPrice, 2)
        PRINT '----------------------------------------'

        -- 次の行をFETCH
        FETCH NEXT FROM @cursor
        INTO @StoreNumber, @SalesDateTime, @ProductID, @ProductName, @Quantity, @UnitPrice
    END

    -- カーソルをクローズして解放
    CLOSE @cursor
    DEALLOCATE @cursor
END

CREATE PROCEDURE dbo.MyProcedure

BEGIN

-- カーソルの定義

DECLARE @cursor CURSOR;

-- SELECT文の実行とカーソルの設定

SET @cursor = CURSOR FOR

SELECT StoreNumber, SalesDateTime, ProductID, ProductName, Quantity, UnitPrice

FROM Sales;

-- 変数の宣言

DECLARE @StoreNumber INT;

DECLARE @SalesDateTime DATETIME;

DECLARE @ProductID INT;

DECLARE @ProductName NVARCHAR(100);

DECLARE @Quantity INT;

DECLARE @UnitPrice DECIMAL(10, 2);

-- FETCHのループ

OPEN @cursor

FETCH NEXT FROM @cursor

INTO @StoreNumber, @SalesDateTime, @ProductID, @ProductName, @Quantity, @UnitPrice

WHILE @@FETCH_STATUS = 0

BEGIN

-- 取得したデータの処理

-- ここで取得したデータを使って何か処理を行うことができます

PRINT 'StoreNumber: ' + CONVERT(VARCHAR(10), @StoreNumber)

PRINT 'SalesDateTime: ' + CONVERT(VARCHAR(20), @SalesDateTime, 120)

PRINT 'ProductID: ' + CONVERT(VARCHAR(10), @ProductID)

PRINT 'ProductName: ' + ISNULL(@ProductName, '')

PRINT 'Quantity: ' + CONVERT(VARCHAR(10), @Quantity)

PRINT 'UnitPrice: ' + CONVERT(VARCHAR(20), @UnitPrice, 2)

PRINT '----------------------------------------'

-- 次の行をFETCH

FETCH NEXT FROM @cursor

INTO @StoreNumber, @SalesDateTime, @ProductID, @ProductName, @Quantity, @UnitPrice

END

-- カーソルをクローズして解放

CLOSE @cursor

DEALLOCATE @cursor

END

実行結果は次のようになります。

EXEC dbo.MyProcedure

1	EXEC dbo.MyProcedure

トランザクションと例外処理

例外を補足し、ロールバックを実行するには次のように記述します。

正常終了すると自動でコミットされますので、明示的にCOMMITを書く必要はありません。

XACT＿STATE() 関数はトランザクションの状態を取得する関数で、トランザクションが完了している場合は0が返されます。従って、0以外の場合、ロールバックするようにします。

以下は、例外処理とロールバックを使ったサンプルです。

CREATE PROCEDURE  dbo.MyProcedure
    -- 引数の定義
    @ProductID INT,
    @StoreNumber INT = 1000
AS
BEGIN
    --変数の定義
    DECLARE @avg DECIMAL(10,2);
	
    --処理
    --指定した店舗番号における全商品単価の平均を求める
    SELECT @avg = AVG(UnitPrice)
    FROM Sales 
    WHERE StoreNumber = @StoreNumber;

    --指定した店舗番号、商品IDの単価を上記で求めた平均値の2倍で書き換える
    BEGIN TRY
        UPDATE Sales
        SET UnitPrice = @avg * 2 
        WHERE StoreNumber = @StoreNumber AND ProductID = @ProductID;
    END TRY
    --例外処理
    BEGIN CATCH
        IF XACT_STATE() <> COMMITTED
        BEGIN
            ROLLBACK TRANSACTION;
            RETURN 1;
        END
    END CATCH

    RETURN 0;
END

CREATE PROCEDURE dbo.MyProcedure

-- 引数の定義

@ProductID INT,

@StoreNumber INT = 1000

BEGIN

--変数の定義

DECLARE @avg DECIMAL(10,2);

--処理

--指定した店舗番号における全商品単価の平均を求める

SELECT @avg = AVG(UnitPrice)

FROM Sales

WHERE StoreNumber = @StoreNumber;

--指定した店舗番号、商品IDの単価を上記で求めた平均値の2倍で書き換える

BEGIN TRY

UPDATE Sales

SET UnitPrice = @avg * 2

WHERE StoreNumber = @StoreNumber AND ProductID = @ProductID;

END TRY

--例外処理

BEGIN CATCH

IF XACT_STATE() <> COMMITTED

BEGIN

ROLLBACK TRANSACTION;

RETURN 1;

END

END CATCH

RETURN 0;

END

動的SQL

動的SQLとは、SQL文を文字列として定義したもので、EXEC文によりSQLとして実行できます。

EXCELはプロシージャを呼ぶときにも使いましたが、動的SQLを実行する場合は ( ) で括る必要があります。

下記は、引数で指定された、カンマ区切りのカラム名を使ってSalesテーブルから値を取得するサンプルです。

CREATE PROCEDURE  dbo.MyProcedure
    -- 引数の定義
    @ProductID INT,
    @condition NVARCHAR(MAX)
AS
BEGIN
    --変数の定義
    DECLARE  @sql NVARCHAR(MAX) = 'SELECT ' + @condition + ' FROM SALES';
    EXEC (@sql);

    RETURN 0;
END

CREATE PROCEDURE dbo.MyProcedure

-- 引数の定義

@ProductID INT,

@condition NVARCHAR(MAX)

BEGIN

--変数の定義

DECLARE @sql NVARCHAR(MAX) = 'SELECT ' + @condition + ' FROM SALES';

EXEC (@sql);

RETURN 0;

END

結果は次の通りです。第2引数で指定された ProductID と ProductName のみが抽出されています。

EXEC dbo.MyProcedure 1000,'ProductID,ProductName'

1	EXEC dbo.MyProcedure 1000,'ProductID,ProductName'

今回の様に短い動的SQLであれば、EXECの中に直接書くことも可能です。

EXEC ('SELECT ' + @condition + ' FROM SALES');

1	EXEC ('SELECT ' + @condition + ' FROM SALES');

ファンクションについて

Transact-SQL でいうファンクションは、OracleやPostgreSQLなどで使われるファンクションと少々異なります。

一般的なデータベースでは、値を返さないものをプロシージャ、値を返すものをファンクションと呼んで区別しますが、値を返す部分以外は両者に違いはありません。

一方、Transact-SQL のプロシージャは、プロシージャとファンクションを併せ持った存在です。

では、Transact-SQL で用意されているプロシージャは何かというと、「単純な処理を関数化したもの」という位置づけで、処理にSQL文（SELECT、INSERT、DELETE、UPDATE、MERGE）が記述できません。

従って、下記のような短い処理を関数化する用途で使用します。

CREATE FUNCTION AddDaysToDate
(
    @daysToAdd INT
)
RETURNS DATE
AS
BEGIN
    DECLARE @resultDate DATE

    -- 現在時刻に引数で指定された日数を加算
    SET @resultDate = DATEADD(DAY, @daysToAdd, GETDATE())

    RETURN @resultDate
END

CREATE FUNCTION AddDaysToDate

(

@daysToAdd INT

)

RETURNS DATE

BEGIN

DECLARE @resultDate DATE

-- 現在時刻に引数で指定された日数を加算

SET @resultDate = DATEADD(DAY, @daysToAdd, GETDATE())

RETURN @resultDate

END

呼び出し方は、組み込み関数であるlen()やreplace()と同じように直接変数で受け取ったり、SELECTの中で使うことが可能です。

-- 変数で受ける
DECLARE @today DATETIME;
SET @today = dbo.AddDaysToDate(5)

--SELECTの中で使う
SELECT dbo.AddDaysToDate(5,0)

-- 変数で受ける

DECLARE @today DATETIME;

SET @today = dbo.AddDaysToDate(5)

--SELECTの中で使う

SELECT dbo.AddDaysToDate(5,0)

まとめ

今回はマイクロソフト系のデータベースエンジン（SQL Sever、SQL Database、Azure Synapse）で利用可能な Transact-SQL について、ストアドとファンクションの書き方について解説しました。

OracleやPostgreSQLなど、他のデータベースでは戻り値を返さないプロシージャと、戻り値を返すプロシージャが区別されており、後者はファンクションと呼ばれています。

一方、Transact-SQLのプロシージャは、プロシージャとファンクションの両方を兼ね備えた存在です。

そして、Transact-SQLで用意されているファンクションは、組み込み関数である len()やreplace()のような単一機能の自作関数を作るために用意されており、関数内部でSQL文が記述できません。

これ以外にも、FORが用意されていない、配列変数が使えない、変数や引数は必ず@から始まる、SELECTの中で直接イコールで変数に代入できるなど、他のデータベースとは少し異なる点が特徴です。

他のデータベース経験者であれば、最初は戸惑うかもしれませんが、慣れると結構便利で書きやすいと思います。

もしTransact-SQLで何か作る機会があれば、是非この記事を参考にしてください。