Microsoft SQL Server
ストアドプロシージャ

前書き

構文

• CREATE {プロシージャ| PROC} [schema_name。] procedure_name
• [@parameter [type_schema_name。]データ型
• [VARYING] [=デフォルト] [OUT | OUTPUT | READONLY]
• 、@parameter [type_schema_name。]データ型
• [VARYING] [=デフォルト] [OUT | OUTPUT | READONLY]]
• [WITH {暗号化| RECOMPILE | EXECUTE AS句}]
• [複製用]
• として
• ベギン
• [宣言セクション]
• executable_section
• 終わり;

基本的なストアドプロシージャの作成と実行

ライブラリデータベースの Authorsテーブルの使用

CREATE PROCEDURE GetName
(
    @input_id INT = NULL,      --Input parameter,  id of the person, NULL default
    @name VARCHAR(128) = NULL  --Input parameter, name of the person, NULL default
) 
AS
BEGIN 
    SELECT Name + ' is from ' + Country 
    FROM Authors 
    WHERE Id = @input_id OR Name = @name
END 
GO

いくつかの異なる構文を使用してプロシージャを実行できます。まず、 EXECUTEまたはEXEC使用できます

EXECUTE GetName @id = 1
EXEC Getname @name = 'Ernest Hemingway'

さらに、EXECコマンドを省略することもできます。また、すべてのパラメータを渡すときに、渡すパラメータを指定する必要はありません。

GetName NULL, 'Ernest Hemingway'

プロシージャーでの宣言方法とは異なる順序で入力パラメーターを指定する場合は、パラメーター名を指定して値を割り当てることができます。例えば

 CREATE PROCEDURE dbo.sProcTemp 
 (
    @Param1 INT,
    @Param2 INT
)
AS
BEGIN

    SELECT
        Param1 = @Param1,
        Param2 = @Param2

END

この手順を実行する通常の順序は、@ Param1の値を最初に指定し、次に@ Param2を最初に指定することです。だから、このように見えるでしょう

  EXEC dbo.sProcTemp @Param1 = 0,@Param2=1

しかし、次のものを使用することも可能です

  EXEC dbo.sProcTemp @Param2 = 0,@Param1=1

ここでは、@ param2の値と@ Param1の値を最初に指定しています。つまり、手順で宣言されているのと同じ順序を保つ必要はありませんが、望むように任意の順序を持​​つことができます。値を設定するパラメータを指定する必要があります

任意のデータベースからストアドプロシージャにアクセスする

そしてまた、あなたが接頭辞を持つプロシージャを作成することができますsp_これらprocuedresを、すべてのシステム・ストアド・プロシージャのように、理由はSQL Serverの既定の動作のデータベースを指定せずに実行することができます。 "sp_"で始まるストアドプロシージャを実行すると、SQL Serverはまずmasterデータベース内のプロシージャを探します。マスターにプロシージャーが見つからない場合は、アクティブ・データベースを調べます。すべてのデータベースからアクセスするストアドプロシージャがある場合は、masterで作成し、 "sp_"接頭辞を含む名前を使用します。

Use Master

CREATE PROCEDURE sp_GetName
(
    @input_id INT = NULL,      --Input parameter,  id of the person, NULL default
    @name VARCHAR(128) = NULL  --Input parameter, name of the person, NULL default
) 
AS
BEGIN 
    SELECT Name + ' is from ' + Country 
    FROM Authors 
    WHERE Id = @input_id OR Name = @name
END 
GO

OUTパラメータを使用したスト​​アドプロシージャ

ストアド・プロシージャは、そのパラメータ・リストのOUTPUTキーワードを使用して値を戻すことができます。

単一のoutパラメーターを使用してストアード・プロシージャーを作成する

CREATE PROCEDURE SprocWithOutParams
(
    @InParam VARCHAR(30),
    @OutParam VARCHAR(30) OUTPUT
)
AS
BEGIN
    SELECT @OutParam = @InParam + ' must come out'   
    RETURN
END   
GO

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

DECLARE @OutParam VARCHAR(30)    
EXECUTE SprocWithOutParams 'what goes in', @OutParam OUTPUT   
PRINT @OutParam 

複数のoutパラメーターを持つストアード・プロシージャーの作成

CREATE PROCEDURE SprocWithOutParams2
(
    @InParam VARCHAR(30),
    @OutParam VARCHAR(30) OUTPUT,
    @OutParam2 VARCHAR(30) OUTPUT
)
AS
BEGIN
    SELECT @OutParam = @InParam +' must come out'   
    SELECT @OutParam2 = @InParam +' must come out'
    RETURN
END    
GO

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

DECLARE @OutParam VARCHAR(30)    
DECLARE @OutParam2 VARCHAR(30)  
EXECUTE SprocWithOutParams2 'what goes in', @OutParam OUTPUT, @OutParam2 OUTPUT   
PRINT @OutParam 
PRINT @OutParam2

If ... ElseおよびInsert into Operationへのストアドプロシージャ

サンプルテーブルの作成Employee ：

CREATE TABLE Employee
(
    Id INT,
    EmpName VARCHAR(25),
    EmpGender VARCHAR(6),
    EmpDeptId INT
)

ストアドプロシージャで渡された値がnullでないか、空でないかどうかをチェックし、Employeeテーブルで挿入操作を実行するストアドプロシージャを作成します。

CREATE PROCEDURE spSetEmployeeDetails
(
    @ID int,
    @Name VARCHAR(25),
    @Gender VARCHAR(6),
    @DeptId INT
)
AS
BEGIN
    IF (
        (@ID IS NOT NULL AND LEN(@ID) !=0) 
        AND (@Name IS NOT NULL AND LEN(@Name) !=0)
        AND (@Gender IS NOT NULL AND LEN(@Gender) !=0)
        AND (@DeptId IS NOT NULL AND LEN(@DeptId) !=0)
    )
    BEGIN
        INSERT INTO Employee
        (
            Id,
            EmpName,
            EmpGender,
            EmpDeptId
        )
        VALUES
        (
            @ID,
            @Name,
            @Gender,
            @DeptId
        )
    END
ELSE
    PRINT 'Incorrect Parameters'
END
GO

ストアドプロシージャを実行する

DECLARE @ID INT,
    @Name VARCHAR(25),
    @Gender VARCHAR(6),
    @DeptId INT

EXECUTE spSetEmployeeDetails
    @ID = 1,
    @Name = 'Subin Nepal',
    @Gender = 'Male',
    @DeptId = 182666 

ストアドプロシージャ内の動的SQL

動的SQLを使用すると、実行時にSQL文を生成および実行できます。ダイナミックSQLは、コンパイル時に異なる識別子を含むSQL文が必要な場合に必要です。

動的SQLの簡単な例：

CREATE PROC sp_dynamicSQL
@table_name      NVARCHAR(20),
@col_name        NVARCHAR(20), 
@col_value       NVARCHAR(20) 
AS
BEGIN
DECLARE  @Query  NVARCHAR(max)
SET      @Query = 'SELECT * FROM ' + @table_name
SET      @Query = @Query + ' WHERE ' + @col_name + ' = ' + ''''+@col_value+''''
EXEC     (@Query)
END

上記のSQLクエリでは、実行時に@table_name, @col_name, and @col_value値を定義することによって、上記のクエリを使用できることがわかります。クエリは実行時に生成され、実行されます。これは、文字列としてスクリプト全体を変数に作成して実行できる手法です。動的SQLと連結コンセプトを使用して、より複雑なクエリを作成することができます。このコンセプトは、いくつかの条件下で使用できるスクリプトを作成する場合に非常に強力です。

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

DECLARE @table_name     NVARCHAR(20) = 'ITCompanyInNepal',
        @col_name       NVARCHAR(20) = 'Headquarter',
        @col_value      NVARCHAR(20) = 'USA'
    
EXEC    sp_dynamicSQL   @table_name,    
                        @col_name,
                        @col_value

私が使用した表

出力

単純なループ

まず、 #systablesという名前の#systablesテーブルと広告の行番号を増やして、一度に1つのレコードを照会できるようにします

select
    o.name,
    row_number() over (order by o.name) as rn
into
    #systables
from
    sys.objects as o
where
    o.type = 'S'

次に、ループを制御する変数を宣言し、この例ではテーブル名を格納します

declare
    @rn int = 1,
    @maxRn int = (
                    select
                        max(rn)
                    from
                        #systables as s
                    )
declare    @tablename sys name

今度は簡単な方法でループすることができます。 select文で@rnをインクリメントしselectが、これはex set @rn = @rn + 1ための別個の文でもあります。これは要件によって異なります。我々はまた、の値を使用@rnから単一のレコードを選択するために、インクリメントれる前#systables 。最後にテーブル名を出力します。

while @rn <= @maxRn
    begin

        select
            @tablename = name,
            @rn = @rn + 1
        from
            #systables as s
        where
            s.rn = @rn

        print @tablename
    end

単純なループ

CREATE PROCEDURE SprocWithSimpleLoop
(
    @SayThis VARCHAR(30),
    @ThisManyTimes INT
)
AS
BEGIN
    WHILE @ThisManyTimes > 0
    BEGIN
        PRINT @SayThis;
        SET @ThisManyTimes = @ThisManyTimes - 1;
    END
    
    RETURN;
END    
GO