T-SQL ストアドプロシージャ編

ストアドプロシージャー

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

データベースに対する連続した複数の処理を１つのプログラムにまとめ、データとともに保存できるようにしたもの。

例えば、毎回SELECT文を書くのは手間なので、「こういう処理をしたい時のプログラム」としてあらかじめ作っておくことができる。

例：

「金額に現行の消費税を掛けて・・・」というクエリを毎回書くのは手間
→「消費税を計算する処理」を作っておき保存。

イメージで言えば、様々な処理手順を有した箱がある
→キーワードを入れればそれを加工して完成させてくれる。

消費税計算の例では、「日付」「金額」を入れれば税込金額が帰ってきます。

'2019/09/01', 1000 →　1080

'2019/10/01, 1000 → 1100

そうした「税率計算してくれる箱」「学生の情報を登録してくれる箱」「店舗の新情報を追加してくれる箱」とかをあらかじめ作っておけば、

金額いくら！　日付いつ！、　名前は〜　番号は〜、　商品名は〜　単価は〜

と引数だけ箱に入れてやればあとは自動（あらかじめ設定した処理）で加工して完成してくれます。

雛形：

IF EXISTS ( SELECT ＊ FROM sys.objects WHERE object_id = OBJECT_ID
(N'[dbo].[ストアド名] ) AND TYPE in (タイプ名)

BEGIN
DROP PROCEDURE [dbo].[ストアド名]
END
GO

CREATE PROCEDURE [dbo].[ストアド名]
@引数　データ型　＝　初期値
AS
BEGIN
　処理
END
GO

TRANSACTIONやエラー処理はSELECTでは使わなくても大丈夫そうです。

UPDATEやDELETEなど途中で問題が起きたら困る処理には必要です。

IF EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[student_registration]') AND type in N'TF')

BEGIN
    DROP PROCEDURE [dbo].[student_registration]
END
GO

CREATE PROCEDURE [dbo].[student_registration](
  @p_id int = NULL
, @p_name varchar(50) = ''
)

AS
BEGIN
BEGIN TRY
    BEGIN TRANSACTION
    DECLARE @error_set table (
       id int NULL
     , message varchar(50) NULL
    )

    IF ISNULL(p@_id, 0) < 1
    BEGIN
    INSERT INTO @error_set(
     id
     , message
     )
    SELECT
      1
    , '学生番号が不正です'
    END
    ELSE
    BEGIN
    IF EXISTS(
       SELECT students.country_id
       FROM students
       WHERE (country_id = @p_id)
    )
    BEGIN
    INSERT INTO @error_set(
      id
    , message
    )
    SELECT
      2
    , '学生番号(' + CAST(@p_id AS varchar(8)) + ')は登録済みです'
    END
  END



IF ISNULL(@p_name, '') = ''
BEGIN
INSERT INTO error_set(
 id
 , message
 )
SELECT
3
 , '生徒名は必須入力です'
END

 
    IF EXISTS(
    SELECT error_set.id
    FROM @error_set AS error_set
     )
    BEGIN
    GOTO EXIT_SPC
    END

--更新

INSERT INTO students(
 id
 , name
 )
SELECT
   @p_id AS id
 , @ISNULL(@p_name, '') AS name

 EXIT_SPC: 
 IF NOT EXISTS(
 SELECT error_set.id
 FROM @error_set AS error_set
   )
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
END
END TRY



BEGIN CATCH
INSERT INTO @error_set(
 id
 , message
 )
SELECT
ERROR_NUMBER()
 , '[Error] :' + CHAR(13) + CHAR(10) +
'Procedure: ' + ISNULL(ERROR_PROCEDURE(), '???') + CHAR(13) + CHAR(10) +
'Line: ' + ISNULL(CAST(ERROR_MESSAGE()), 'An unexpected error occured')
ROLLBACK TRANSACTION
END CATCH



--エラー内容返却

SELECT
 error_set.id AS id
 , error_set AS error_set
FROM @eeror_set AS error.SET
ORDER BY error_set.id ASC
END
GO