SQL Server ストアドプロシージャの書き方
DBの値を加工するロジックについて、webアプリとデスクトップアプリの両方で必要となった。それぞれのプログラムで実装するよりも、一つのロジックを利用する方が保守性がよいため、ストアドプロシージャを使うことにした。
ストアドプロシージャは特殊な用途において強力な反面、使う機会が少ないため書き方をすぐに忘れてしまう。
そこで、書き方のポイントを記事にしておく。
環境
WIndow 10 64bit
SQL Server 2016(SP1)
SQL Server 2016 Management Studio
ストアドの作成方法
SQL Server Management Studioのオブジェクトエクスプローラーからデータベースを選択してから「新しいクエリ」をクリックし、入力ウィンドウを開く。以下のような空のCREATE文を書き、Testという名前のプロシージャを作る。
CREATE PROCEDURE [dbo].[Test]
AS
RETURN
メニュー -> クエリ -> 実行 でストアドプロシージャが作成され、データベースに登録される。
ストアドの修正方法
メニュー -> データベース -> プログラミング -> ストアドプロシージャ から既存のストアドを探して右クリック -> 変更をクリックする。見付からない場合は、右クリック -> 最新の情報に更新 をクリックするか、F5キーを押す。
変更をクリックすると、以下のようにALTER文が開かれる。修正を加えたら、メニュー -> クエリ -> 実行 でALTER文が実行され、ストアドの内容が更新される。
USE [ProductMng]
GO
/****** Object: StoredProcedure [dbo].[Test] Script Date: 2018/03/27 17:40:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Test]
@INPUT_STR VARCHAR,
@INPUT_INT INT
AS
DECLARE @TMP_DATE DATETIME
DECLARE @TMP_INT INT
SET @TMP_INT = @INPUT_INT + 1
SELECT @TMP_DATE, @TMP_INT, TestData FROM TestTable;
パラメタの渡し方
ALTER文の下の2行がパラメタ。一つ目はINPUT_STRがパラメタ名で、VARCHARは可変長文字列型であることを表している。二つ目はINPUT_INTがパラメタ名で、INTは数値型を表す。もっと増やしたいときは、カンマで区切って行を追加するとよい。AS句以降でアットマーク+パラメタ名で変数として使える。
変数の使い方
DECLARE文の下の2行で宣言しているのが変数。一つ目はTMP_DATEが変数名で、DATETIMEは日付型を示す。二つ目はTMP_INTが変数名で、INTは数値型を示す。その下のSET文のように、値を代入できる。
プログラムからの呼び出し方
Transact-SQL
Management Studio からSQLを使って呼び出すには、「新しいクエリ」を開き、データベースを選択してから以下のようにEXEC文、半角スペース、一つ目のパラメタ、カンマ、二つ目のパラメタ というふうに書いて実行する。
EXEC Test 'mojiretsu', 26
実行結果はこんな感じ。
取得できた行の値のうち、一番目の列はNULLとなった。これは変数に値を入れていないため。二番目の列は、パラメタとして渡した26に、ストアドによって+1された値が返された。三番目はデータベースに格納されていたTestDataというデータの値。
Python
言語にかかわらず、上記と同様の命令文を呼び出す側のプログラムで書けばよい。例としてPythonでは以下のように実装する。
import pyodbc
def GetTestData(str_param = "mojiretsu", int_param = 26):
cnxn = pyodbc.connect(connectionString)
cursor = cnxn.cursor()
qry = "EXEC Test '" + str_param + "', " + str(int_param)
cursor.execute(qry)
row = cursor.fetchone()
ストアドをステップ実行する方法
SQL Server Management Studioでステップ実行してデバッグができる。方法は以下を参照。