Python
SQLServer
ストアドプロシージャ

SQL Server ストアドプロシージャの書き方

SQL Server ストアドプロシージャの書き方

DBの値を加工するロジックについて、webアプリとデスクトップアプリの両方で必要となった。それぞれのプログラムで実装するよりも、一つのロジックを利用する方が保守性がよいため、ストアドプロシージャを使うことにした。
ストアドプロシージャは特殊な用途において強力な反面、使う機会が少ないため書き方をすぐに忘れてしまう。
そこで、書き方のポイントを記事にしておく。

環境

WIndow 10 64bit
SQL Server 2016(SP1)
SQL Server 2016 Management Studio

ストアドの作成方法

SQL Server Management Studioのオブジェクトエクスプローラーからデータベースを選択してから「新しいクエリ」をクリックし、入力ウィンドウを開く。以下のような空のCREATE文を書き、Testという名前のプロシージャを作る。

SQLQuery1.sql
CREATE PROCEDURE [dbo].[Test]


AS

RETURN

メニュー -> クエリ -> 実行 でストアドプロシージャが作成され、データベースに登録される。

ストアドの修正方法

メニュー -> データベース -> プログラミング -> ストアドプロシージャ から既存のストアドを探して右クリック -> 変更をクリックする。見付からない場合は、右クリック -> 最新の情報に更新 をクリックするか、F5キーを押す。
sutoado3PNG.PNG

変更をクリックすると、以下のようにALTER文が開かれる。修正を加えたら、メニュー -> クエリ -> 実行 でALTER文が実行され、ストアドの内容が更新される。

SQLQuery1.sql
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文、半角スペース、一つ目のパラメタ、カンマ、二つ目のパラメタ というふうに書いて実行する。

SQLQuery1.sql
EXEC Test 'mojiretsu', 26

実行結果はこんな感じ。
sutoado4.PNG
取得できた行の値のうち、一番目の列はNULLとなった。これは変数に値を入れていないため。二番目の列は、パラメタとして渡した26に、ストアドによって+1された値が返された。三番目はデータベースに格納されていたTestDataというデータの値。

Python

言語にかかわらず、上記と同様の命令文を呼び出す側のプログラムで書けばよい。例としてPythonでは以下のように実装する。

test.py
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()