🗂️

【DB】ストアドプロシージャ（STORED PROCEDURE）について

に公開
Docker
Database
DB
storedprocedure
tech

ストアドプロシージャとは

ストアドプロシージャを作ってみよう

１.準備

準備するもの

https://www.docker.com/ja-jp/products/docker-desktop/
https://learn.microsoft.com/ja-jp/ssms/install/install

２．【Docker】データベースを作成

サーバーコンテナの起動（コマンドプロンプトで以下を実行し、 SQL Serverデータベースサーバーをコンテナとして起動する。）
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=YourPassword123!" -p 1433:1433 --name sqlserver -d mcr.microsoft.com/mssql/server:2019-latest
コマンドの詳細
環境変数の設定
-e "ACCEPT_EULA=Y" → マイクロソフトのライセンス契約に同意
-e "MSSQL_SA_PASSWORD=Password123" → 管理者(sa)のパスワードを設定
ネットワーク設定（1433はSQL Serverの標準ポート。これでSSMSから localhost
-p 1433:1433 → コンテナの1433番ポートをホストの1433番ポートに接続
コンテナ設定
--name sqlserver → コンテナに「sqlserver」という名前を付与
-d → バックグラウンドで実行（デタッチモード）
イメージ
mcr.microsoft.com/mssql/server:2019-latest → マイクロソフト公式のSQL Server 2019イメージを使用
サーバーが起動したか確認
docker ps
コマンドの詳細
出力
COMMAND                   CREATED         STATUS         PORTS                    NAMES
60c9beb7e72c   mcr.microsoft.com/mssql/server:2019-latest   "/opt/mssql/bin/perm…"   4 minutes ago   Up 4 minutes   0.0.0.0:1433->1433/tcp   sqlserver

３．【SSMS】サーバー接続

SQL Server Management Studio（SSMS）からサーバーに接続する手順


SSMSの画面からファイル＞オブジェクトエクスプローラーの接続から画面を起動し、接続を行う


注意の画面がでてくるので、画面のAdvancedから画面の選択部分をFlaseからTrueへ変更する


接続完了

４．【SSMS】データベースの作成

SSMSのメニュータブの中にある「新しいクエリ」から以下を実行する
CREATE DATABASE ShopDB
COLLATE Japanese_CI_AS;
GO


SSMSの新しいクエリを開き、「CREATE DATABASE DB_NAME」と入力しF5キーを押して実行し、DBを作成する

５．【SSMS】テーブルの作成

テーブル作成クエリ
-- 作成したデータベースを使用
USE ShopDB;
GO

-- 1. 顧客テーブル
CREATE TABLE Customers (
    CustomerID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerName NVARCHAR(50) NOT NULL,
    Email NVARCHAR(100) UNIQUE,
    Phone NVARCHAR(20),
    Address NVARCHAR(200),
    RegistrationDate DATETIME2 DEFAULT GETDATE()
);

-- 2. カテゴリテーブル
CREATE TABLE Categories (
    CategoryID INT IDENTITY(1,1) PRIMARY KEY,
    CategoryName NVARCHAR(50) NOT NULL,
    Description NVARCHAR(200)
);

-- 3. 商品テーブル
CREATE TABLE Products (
    ProductID INT IDENTITY(1,1) PRIMARY KEY,
    ProductName NVARCHAR(100) NOT NULL,
    CategoryID INT NOT NULL,
    Price DECIMAL(10,2) NOT NULL,
    Stock INT NOT NULL DEFAULT 0,
    Description NVARCHAR(500),
    CreatedDate DATETIME2 DEFAULT GETDATE(),
    FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
);

-- 4. 注文テーブル
CREATE TABLE Orders (
    OrderID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID INT NOT NULL,
    OrderDate DATETIME2 DEFAULT GETDATE(),
    Status NVARCHAR(20) DEFAULT '処理中' CHECK (Status IN ('処理中', '発送済み', '完了', 'キャンセル')),
    TotalAmount DECIMAL(12,2) DEFAULT 0,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- 5. 注文詳細テーブル
CREATE TABLE OrderDetails (
    OrderDetailID INT IDENTITY(1,1) PRIMARY KEY,
    OrderID INT NOT NULL,
    ProductID INT NOT NULL,
    Quantity INT NOT NULL,
    UnitPrice DECIMAL(10,2) NOT NULL,
    Subtotal AS (Quantity * UnitPrice) PERSISTED,
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);


作成したDBのテーブルディレクトリの中にテーブルが作成される

６．【SSMS】テストデータの挿入

テストデータの挿入クエリ
-- サンプルデータ挿入

-- カテゴリデータ
INSERT INTO Categories (CategoryName, Description) VALUES
('電子機器', 'スマートフォン、パソコン、家電製品'),
('書籍', '小説、技術書、雑誌'),
('衣類', '洋服、靴、アクセサリー'),
('食品', '生鮮食品、加工食品、飲料');

-- 顧客データ
INSERT INTO Customers (CustomerName, Email, Phone, Address) VALUES
('田中太郎', 'tanaka@email.com', '090-1234-5678', '東京都渋谷区1-1-1'),
('佐藤花子', 'sato@email.com', '090-2345-6789', '大阪府大阪市2-2-2'),
('鈴木次郎', 'suzuki@email.com', '090-3456-7890', '愛知県名古屋市3-3-3'),
('高橋美咲', 'takahashi@email.com', '090-4567-8901', '福岡県福岡市4-4-4');

-- 商品データ
INSERT INTO Products (ProductName, CategoryID, Price, Stock, Description) VALUES
('iPhone 15', 1, 128000.00, 50, '最新のスマートフォン'),
('MacBook Air', 1, 148000.00, 30, '薄型軽量ノートパソコン'),
('ワイヤレスイヤホン', 1, 25000.00, 100, 'ノイズキャンセリング機能付き'),
('SQL入門書', 2, 3200.00, 200, 'データベース学習の基礎本'),
('プログラミング本', 2, 4500.00, 150, 'Python入門書'),
('ビジネススーツ', 3, 45000.00, 80, '高品質なビジネス用スーツ'),
('カジュアルシャツ', 3, 5500.00, 120, '普段着用のシャツ'),
('プレミアム米', 4, 8000.00, 300, '新潟県産コシヒカリ 10kg'),
('オーガニック野菜セット', 4, 2500.00, 50, '無農薬野菜の詰め合わせ');

-- 注文データ
INSERT INTO Orders (CustomerID, OrderDate, Status, TotalAmount) VALUES
(1, '2024-01-15 10:30:00', '完了', 0),  -- 後でストアドプロシージャで計算
(1, '2024-01-20 14:15:00', '発送済み', 0),
(2, '2024-01-18 09:45:00', '完了', 0),
(3, '2024-01-22 16:20:00', '処理中', 0),
(4, '2024-01-25 11:10:00', '処理中', 0);

-- 注文詳細データ
INSERT INTO OrderDetails (OrderID, ProductID, Quantity, UnitPrice) VALUES
-- 田中太郎の1回目の注文
(1, 1, 1, 128000.00),  -- iPhone 15
(1, 4, 2, 3200.00),    -- SQL入門書 2冊

-- 田中太郎の2回目の注文  
(2, 3, 1, 25000.00),   -- ワイヤレスイヤホン

-- 佐藤花子の注文
(3, 2, 1, 148000.00),  -- MacBook Air
(3, 5, 1, 4500.00),    -- プログラミング本

-- 鈴木次郎の注文
(4, 6, 1, 45000.00),   -- ビジネススーツ
(4, 7, 2, 5500.00),    -- カジュアルシャツ 2枚

-- 高橋美咲の注文
(5, 8, 1, 8000.00),    -- プレミアム米
(5, 9, 3, 2500.00);    -- オーガニック野菜セット 3個
テストデータ作成確認クエリ
SELECT 'Database Created Successfully' AS Status;

-- データ確認
SELECT CustomerName, Email FROM Customers;

-- リレーション確認
SELECT 
    c.CustomerName AS CustomerName,
    o.OrderID,
    o.OrderDate,
    p.ProductName,
    cat.CategoryName,
    od.Quantity,
    od.UnitPrice,
    od.Subtotal
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN OrderDetails od ON o.OrderID = od.OrderID  
JOIN Products p ON od.ProductID = p.ProductID
JOIN Categories cat ON p.CategoryID = cat.CategoryID
ORDER BY c.CustomerName, o.OrderDate;


テストデータが挿入されているかを確認する

７．【SSMS】ストアドプロシージャを作成


DB内のプログラミング＞ストアドプロシージャ＞新規作成＞ストアドプロシージャから新規にストアドプロシージャを作成する

ストアドプロシージャの雛形
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>　　　-- 作成者名
-- Create date: <Create Date,,>　　-- 作成日
-- Description:	<Description,,>　-- 説明
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
	-- Add the parameters for the stored procedure here
	<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
	<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO
ANSI_NULLSとSET QUOTED_IDENTIFIERについて
ANSI_NULLSの設定
-- 例：こんなデータがあるとします
ProductName    Price
iPhone 15      128000.00
Test Product   NULL      ← この行にNULL値

-- ANSI_NULLS ON の場合
WHERE Price IS NULL     → Test Productの行が返される ✓
WHERE Price = NULL      → 何も返されない ✗

-- ANSI_NULLS OFF の場合  
WHERE Price IS NULL     → Test Productの行が返される ✓
WHERE Price = NULL      → Test Productの行が返される ✓（非標準）
SET QUOTED_IDENTIFIERの設定
-- QUOTED_IDENTIFIER ON の場合
'iPhone 15'  → 文字列として扱われる ✓
"iPhone 15"  → エラー！文字列として認識されない ✗
"ProductName" → 列名として扱われる ✓

-- QUOTED_IDENTIFIER OFF の場合
'iPhone 15'  → 文字列として扱われる ✓  
"iPhone 15"  → 文字列として扱われる ✓（非標準）
"ProductName" → 文字列として扱われる（列名ではない）


「Ctrl」+「Shift」＋「M」でパラメータ設定画面が開くので入力する

プロシージャ設定と定義とパラメータ定義と処理の記述
プロシージャ設定
SET ANSI_NULLS ON GO -- ANSI_NULLS ON = NULL比較は IS NULL / IS NOT NULL を使うという宣言
SET QUOTED_IDENTIFIER ON GO -- QUOTED_IDENTIFIER ON = 文字列は 'シングルクォーテーション' を使うという宣言
コメント部分
-- =============================================
-- Author:		<Author,,Name>          -- 作成者名
-- Create date: <Create Date,,>         -- 作成日
-- Description:	<Description,,>      -- 説明
-- =============================================
プロシージャ定義（ProcedureNameの部分を定義する）
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
パラメータ定義（@CategoryID int = NULLのような感じでパラメータ名 データ型 = 値とする）
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
処理の設定（「○○行が影響を受けました」というメッセージを非表示にする）
BEGIN
    SET NOCOUNT ON;　
    -- 他の処理...
END
処理の記述（BEGIN～END の役割）
BEGIN　-- プロシージャの処理の開始を示す
    -- ここに実際の処理を書く
END -- プロシージャの処理の終了を示す

９．【SSMS】完成したストアドプロシージャ

完成したストアドプロシージャ

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: pyusuket
-- Create date: 2025-07-27
-- Description: 価格範囲で商品検索
-- =============================================
CREATE PROCEDURE sp_SearchProducts
@CategoryID int = NULL, -- カテゴリーID
@MinPrice DECIMAL(10,2) = 0, 　　-- 最低価格
@MaxPrice DECIMAL(10,2) = 999999 -- 最高価格
AS
BEGIN
SET NOCOUNT ON;

-- 商品検索の実際の処理
SELECT 
    p.ProductID,
    p.ProductName,
    c.CategoryName,
    p.Price,
    p.Stock,
    p.Description
FROM Products p
INNER JOIN Categories c ON p.CategoryID = c.CategoryID
WHERE 
    -- カテゴリーIDが指定されている場合のみフィルタ
    (@CategoryID IS NULL OR p.CategoryID = @CategoryID)
    -- 価格範囲でフィルタ
    AND p.Price BETWEEN @MinPrice AND @MaxPrice
    -- 在庫があるもののみ
    AND p.Stock > 0
ORDER BY p.Price ASC;

-- 検索結果の件数を返す（オプション）
SELECT COUNT(*) AS SearchResultCount
FROM Products p
WHERE 
    (@CategoryID IS NULL OR p.CategoryID = @CategoryID)
    AND p.Price BETWEEN @MinPrice AND @MaxPrice
    AND p.Stock > 0;

END
GO


実行するとストアドプロシージャが作成されている

１０．ストアドプロシージャの実行

プロシージャの実行パターン
EXEC sp_SearchProducts;


プロシージャを実行し、以下の結果が得られれば成功

プロシージャの実行パターン
プロシージャの実行パターン
-- 1. 基本形：EXEC プロシージャ名
EXEC sp_HelloWorld;

-- 2. EXECUTEと書いてもOK（同じ意味）
EXECUTE sp_HelloWorld;

-- 3. パラメータなしの場合、EXECを省略可能
sp_HelloWorld;

１１．【SSMS】パラメータを指定して実行してみる

MinPriceとMaxPriceの値を指定してストアドを実行してみる
EXEC sp_SearchProducts @MinPrice = 4000,  @MaxPrice = 50000


パラメータを指定しないと9件だが指定すると5件になる

Discussion