Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

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()

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした