SQL Serverのストアドプロシージャを作成する

SQL Serverのストアドには2種類あります。

  1. ストアドプロシージャ(PROCEDURE)・・・実行のみ
  2. ストアドファンクション(FUNCTION)・・・戻り値あり

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

ストアドプロシージャ

プロシージャを作成する

SQL Serverのストアドプロシージャを作成するには「CREATE PROCEDURE~」を使用します。Oracleの「OR REPLACE」は使えないのが不便ですね。

-- プロシージャを作成する 
CREATE PROCEDURE {プロシージャ名}(@パラメータ {型} {OUTPUT})
AS
BEGIN
  {処理}
END

パラメータ

  • (デフォルト)・・・値渡し
  • OUTPUT・・・参照渡し

  • varchar・・・文字列型
  • int・・・数値型
  • decimal・・・小数型
  • date・・・日付型

変数の宣言

  • DECLARE @変数 型

代入

  • SET {代入される値} = {代入する値}

 

ストアドプロシージャを作成するCREATE PROCEDURE

ストアドプロシージャにはパラメータに値だけを渡す「値渡し」と「参照渡し」があります。この2種類の作成例を紹介します。

例1. ストアドプロシージャを作成する(変数 値渡し)

-- プロシージャを作成する 
CREATE PROCEDURE dbo.proA(@iEno int)
AS
BEGIN
  --変数宣言
  DECLARE @iCnt int;
  SET @iCnt = @iEno + 1;

 --UPDATE実行
  UPDATE emp 
  SET ord_no = 0
  WHERE empno = @iCnt;
END
GO

ここでは受け取った値の条件に合致するレコードをUPDATEするシンプルなストアドプロシージャを作成しました。

パラメータ

パラメータ「@iEno」を受け取り、empテーブルをUPDATEするストアドプロシージャ「proA」を作成しました。

変数

変数は「DECLARE」で宣言します。変数名の先頭に「@」をつけます。「DECLARE @iCnt int」でint型の変数「@iCnt 」を宣言しました。

代入

変数の代入は「SET」以降に記述します。ここでは「SET @iCnt = @iEno + 1;」とし、「@iEno + 1」を「@iCnt」に代入しました。

例2. ストアドプロシージャを作成する(変数 参照渡し)

-- プロシージャを作成する 
CREATE PROCEDURE dbo.proA
(@iEno int, @vEname varchar(30) OUTPUT)
AS
BEGIN
  --SELECT実行(パターン1)
  SELECT @vEname = empname 
  FROM emp
  WHERE empno = @iEno;

 --SELECT実行 (パターン2)
  SET @vEname = (SELECT empname FROM emp WHERE empno = @iEno); 
END
GO

ここではパラメータ(変数)「iEno」に値を渡して、それをストアドプロシージャが受け取って、SELECT結果を「@vEname」に返すストアドプロシージャを作成しました。

SELECTの結果を変数に入れる方法は2つあります。

SELECT結果を変数に渡す(パターン1)

パターン1はSQLの中に埋め込む方法。「SELECT @変数 = 列1 FROM テーブル」と記述します。

ここでは「@vEname = empname」とし、「empname」の結果を変数「@vEname」に入れました。

SELECT結果を変数に渡す(パターン2)

パターン2は変数の代入と同じようにSETを使います。SELECTした結果を「@vEname」に入れました。

 

SQL Serverのストアドプロシージャとして、値渡しと参照渡しの2種類を紹介しました。

参照:CREATE PROCEDURE (Transact-SQL)

以上、SQL Serverのストアドプロシージャ(PROCEDURE)を作成するSQLでした。

コメント