【SqlServer】ストアドプロシージャを使って初めての処理

現在SqlServerとAccessを使ってシステムを開発しているのですが、ネット検索していて、

ココがポイント

ストアドプロシージャを使って初めて大学生レベル

になれるって書き込みをみて自分も成長するために足を踏み入れてみようと決心しました。

ストアドプロシージャって何？

ストアドプロシージャとは、データベース管理システム（DBMS）の機能の一つで、データベースに対する連続した複数の処理を一つのプログラムにまとめ、データと共に保存できるようにしたもの。処理はDBMS側で行われ、外部からはクエリを発行するのと同じ手順で実行できる。（引用：ＩＴ用語辞典）

何だか難しくて良くわかりませんね。

簡単に言うと、

ココがポイント

サーバー側で処理できる複数クエリ

だと思っていいんじゃないかと思います。

SqlServerをサーバーにして、Accessなどを端末として接続すると、データ容量が大きくなるとAccessのクエリなどで処理をしていると非常に動作が重くなってくる場合があります。

それを、ストアドプロシージャ（以下ストアドに略）を使う事で、SqlServer側で処理をさせるので、端末で使用しているAccessの動作が軽くなるって事だと思います。

SSMSを使ってストアドを作成する

SQL Server Management Studio (SSMS)を使って実際にストアドを作成してみたいと思います。

SSMSでテーブルやViewを作成するようなシンプルな方法を探しましたが、実際は、SSMSのオブジェクトエクスプローラーからデータベースを選択してから「新しいクエリ」をクリックし、入力ウィンドウを開いて、クエリの「実行」することでストアドが作成されるようです。

CREATE PROCEDURE stock_calc
AS
BEGIN
	PRINT 'ここに処理を記述';
END;

上記の記述コードで最初は作成できましたが、なぜか２個目のストアドプロシージャを作成しようとすると、クエリは正常終了されているようですが、表示されません。再度クエリを実行すると、「既に存在しています」というエラー表示がでます。

実際は、下記の様に書かないとダメなようです。

CREATE PROCEDURE [dbo].[stock_calc]
AS
BEGIN
	PRINT 'ここに処理を記述';
END;

stock_calcというストアド名にして作成してみました。

MSSMSで作成したストアドが表示されない！？

上のやり方で、

ココに注意

更新しても、SqlServerを再起動しても作成したはずのストアドが表示されません。

解決内容だけ書いておきますので原因は不明ですが、SqlServerが入っているサーバー上のMSSMSにて下記のクエリを実行したらちゃんと表示されました。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[client_calc]
AS
BEGIN
PRINT 'ここに処理を記述';
END
GO

ストアドのコードを作成

ストアドのコードがサクサクと書けるようなスキルが無い為に、「新しいクエリ」にて空のクエリ作成モードにしてから、ホワイトの空白エリアにて、右クリックして、「エディターでクエリのデザイン」というのが出てきます。

このモードにすると、

ココがポイント

Accessのクエリを作成する画面に大変似ている為、簡単にSQL

を組めます。

今回は、View_job_arrivalのフィールドarrival_resaltsの値を、stockテーブルのstockフィールドに値を更新するという処理をさせました。

列の下の空白セル上で、右クリックをすると、「種類の変更」という選択が出てくるので、その中から、「更新」を選択して、「新しい値」のエリアに処理内容を書き込みました。

そこで作成したSQＬをそのまま、ストアドの方に貼り付けて使ってみました。

ストアドの修正は、クエリの実行

USE [Logistics]
GO
/****** Object:  StoredProcedure [dbo].[stock_calc]    Script Date: 2020/02/24 15:20:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[stock_calc]
AS
BEGIN TRY
	BEGIN TRANSACTION

UPDATE stock
SET stock = View_job_arrival.arrival_resalts
FROM View_job_arrival INNER JOIN stock
ON View_job_arrival.product_code = stock.product_code

	COMMIT TRANSACTION

END TRY

BEGIN CATCH
	ROLLBACK TRANSACTION

	RETURN ERROR_NUMBER()

END CATCH

RETURN 0

上のストアドのコードには、トランザクション処理を付加させました。

今回は、１つのＳＱＬ処理しかさせていませんが、BEGIN TRANSACTIONからCOMMIT TRANSACTIONの間に、複数のＳＱＬ処理コードを記入していいようです。

※Accessを端末で使用することが前提のストアドコード構成になっています。

ＶＢＡと同じように、トランザクション処理の機能ができるようです。

ストアドを変更修正する時は、「変更」というメニューから行う事が出来ますが、

ココに注意

保存は、クエリの「実行」をする

ことで保存になるようです。保存と言う流れもありますが、ただ単にサーバー内にクエリのコードがファイルとして保存されるだけで、保存されませんでした。

※実行しても今回作成した更新処理はされていませんでした。

ストアドのテスト実行をする

ストアドの実行をテストするために、実際に動かしてみました。

ストアドの実行も、クエリの「実行」から行うようです。

結果は、見事に処理されてた内容でテーブルが更新されていました。

Accessからストアドを実行

下記のコードをAccessのボタンのコードにして実行してみましたが、見事にストアドが実行されて更新できていました。

Private Sub stock_calc_btn_Click()
    Dim Cnn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim strMsg As String
On Error GoTo Err_stock_calc_btn_Click
   
    'SQLServer接続設定＆接続
    With Cnn
        .Provider = "SQLOLEDB"
        .ConnectionString = "Data Source=サーバー名;" & _
            "Initial Catalog=Logistics;" & _
            "User ID=ユーザーID;" & _
            "Password=パスワード"
        .Open
    End With

    'ストアドプロシージャ呼び出し設定＆呼び出し
    With cmd
        .CommandTimeout = 0             'タイムアウト設定を無制限に
        .ActiveConnection = Cnn         'サーバー接続設定
        .CommandText = "stock_calc"      'ストアド名セット
        .CommandType = adCmdStoredProc  'ストアドなのでadCmdStoredProc
       
        '戻り値設定
        .Parameters.Append .CreateParameter("@rtn", adInteger, adParamReturnValue)
       
        '実行※レコードは返ってこないのでadExecuteNoRecords
        .Execute , , adExecuteNoRecords
       
        '戻り値の判断＆表示用メッセージ生成
        If .Parameters.Item("@rtn").Value = 0 Then
            strMsg = "正常終了しました"
        Else
            strMsg = "異常終了しました" & vbCrLf & _
                    "ErrNO=" & .Parameters.Item("@rtn").Value
        End If
    End With

Exit_stock_calc_btn_Click:
    '接続解除
    Cnn.Close
    Set Cnn = Nothing
    'メッセージ表示
    MsgBox strMsg
    Exit Sub

Err_stock_calc_btn_Click:
    'ストアド以外の場所でのエラーなので、VBAのErrの情報セット
    strMsg = "異常終了しました" & vbCrLf & _
            "ErrNO=" & Err.Number & " ErrMsg=" & Err.Description
    Resume Exit_stock_calc_btn_Click

End Sub

ストアドには、２種類ある

SQL Serverのストアドには２種類あるようです。

●ストアドプロシージャ(PROCEDURE)・・・実行のみ

●ストアドファンクション(FUNCTION)・・・戻り値あり

戻り値があるのがファンクションで、戻り値がないのがプロシージャというらしいです。

つまり、今回のストアドは、ストアドプロシージャの方ですかね。

また、時間があるときに勉強を進めていきたいと思います。

Accessで実行命令をだして、サーバー上でデータ処理ができるというは今後の開発に本当に助かります。