Sql Serverのストアドプロシージャの概要
By:Greg Robidoux|Updated:2020-07-30|Comments(15)|Related:More>ストアドプロシージャ
Problem
SQL Serverをしばらく使用していましたが、データベースがアプリケーションコードに埋め込まれているため、データベースがアプリケーションコードに埋め込まれています。 知っていることができcreatestored手続きをしたいがいのだが何かする必要dotoの実施を格納手続き,
ソリューション
ストアドプロシージャは、データベースに格納されているT-SQLステートメントのバッチよりも多くのものではありません。 あなたのアプリケーションから複数のステートメントを発行する代わりに、一つのステートメントの代わりにストアドプロシージャを呼び出して作業を さらに、コードはデータベースに保存されているので、異なるアプリケーションやクエリウィンドウから何度も同じコードセットを発行することができます。 開始するには、このヒントの残りの部分では、somesampleストアドプロシージャと、それらを開始して構築する方法について説明します。,
以下の例は、ストアドプロシージャの作成がいかに簡単であるかを示しています。 これらの例はadventureworksデータベースを使用していますが、これらの例は、これらの概念を独自のデータベースやアプリケーションに適用できることを非常に簡単にする必要があります。
例1-シンプルなストアドプロシージャ
この最初の例では、ユーザーから上位1レコードを取得するシンプルなストアドプロシージャを作成します。接触のテーブル。
CREATE PROCEDURE uspGetContact AS SELECT TOP 1 ContactID, FirstName, LastName FROM Person.Contact
上記が作成された後、このstoredprocedureを実行するには、以下のコマンドを使用します。,
EXEC uspGetContact
これは、この最初のクエリの結果です。
例2-パラメータを持つストアドプロシージャ
この次の例は、最初の例を変更したものですが、今回はプロシージャに渡されるパラメータを追加して、レコードを動的に選択します。CREATE PROCEDUREを使用する代わりに、ALTER PROCEDUREを使用して、例1で作成したprocedurethatを最初に削除してから再作成する代わりに変更しています。,
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS SELECT TOP 1 ContactID, FirstName, LastName FROM Person.Contact WHERE LastName = @LastName
以下は、ストアドプロシージャを実行できる二つの異なる方法を示しています。 最初のexamplejustは、使用するパラメータ値を渡し、第二の例では、値と一緒にパラメータ名も含まれています。 ストアドプロシージャは、これらのコマンドのいずれかで実行できます。
EXEC uspGetContact "Alberts" EXEC uspGetContact @LastName="Alberts"
これは、この最初のクエリの結果です。,
例3-パラメータと出力パラメータを持つストアドプロシージャ
この例では、入力パラメータと出力パラメータの両方があります。Outputパラメーターは、ストアドプロシージャでlookingupしているContactIDを返すために使用されます。 この出力パラメータは、thispersonの任意のアドレスレコードとともにthepersons ContactID、FirstName、LastNameを選択するために使用されます。,
再びストアドプロシージャuspGetContactを変更し、次にプロシージャuspGetContactを実行する次のコードセットを実行し、取得した戻り値に基づいてperson nameとaddressinfoも照会します。
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50), @ContactID INT output AS SELECT TOP 1 @ContactID = c.ContactID FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.LastName = @LastName
ストアドプロシージャが変更された後、以下のコードブロックを実行します。 これにより、上記のストアドプロシージャが実行され、ContactIDに値がある場合は、personとaddress情報も返されます。
DECLARE @ContactID INT SET @ContactID = 0 EXEC uspGetContact @LastName="Smith", @ OUTPUT IF @ContactID <> 0 BEGIN SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID = @ContactID SELECT d.AddressLine1, d.City, d.PostalCode FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.ContactID = @ContactID END
これは結果です。,
例4-RAISERRORステートメントを使用したストアドプロシージャ
この例では、例3の二つのステップを一つのストアドプロシージャ最初のステップは、ContactIDを取得し、次に手順の第二部を取得することです人の名前と住所情報をルックアップします。 また、レコードが見つからない場合にエラーを返すためにtheRAISERROR文を使用するコードを追加しました。
これは、データが見つかったときとデータが見つからないときにどのように見えるかを示すために二回実行されています。, RAISERROR文を使用すると、アプリケーションがデータを処理しない方法やその他のエラーが発生する可能性がある方法を制御できます。
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS DECLARE @ContactID INT SELECT TOP 1 @ContactID = c.ContactID FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.LastName = @LastName IF @@ROWCOUNT > 0 BEGIN SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID = @ContactID SELECT d.AddressLine1, d.City, d.PostalCode FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.ContactID = @ContactID END ELSE BEGIN RAISERROR ("No record found",10,1) END
EXEC uspGetContact @LastName="Walters"
これは結果です。
EXEC uspGetContact @LastName="Job"
これは、データが見つからない場合の結果です。
例5-別の呼び出しストアドプロシージャを持つストアドプロシージャ
ここでは、二つのストアドプロシージャを持つ別の例です。, 最初のstoredprocedure uspFindContactは、アドレスレコードを持つ最初のレコードを検索し、次に呼び出し元のストアドプロシージャにContactIDを返して、人とアドレス情報を再び表示します。
CREATE PROCEDURE uspFindContact @LastName NVARCHAR(50), @ContactID INT output AS SELECT TOP 1 @ContactID = c.ContactID FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.LastName = @LastName
以下のコードは、uspgetcontactストアドプロシージャを変更し、uspfindcontactを呼び出してレコードセットを返します。
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS DECLARE @ContactID INT SET @ContactID = 0 EXEC uspFindContact @, @ OUTPUT IF @ContactID <> 0 BEGIN SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID = @ContactID SELECT d.AddressLine1, d.City, d.PostalCode FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.ContactID = @ContactID END ELSE BEGIN RAISERROR ("No record found",10,1) END
EXEC uspGetContact @LastName="Walters"
これは結果です。
EXEC uspGetContact @LastName="Job"
これは結果です。,
例6-コメント付きストアドプロシージャ
この最後の例では、uspGetContactストアドプロシージャを使用し、コメントをコードに追加して、ストアドプロシージャ内でコメントがどのように機能するかを確認できるようにします。
コメントは二つの方法で作成できます
- —
- コメントブロックを開始するには/*を使用し、コメントブロックを終了するには*/を使用します。
それ以外は、ストアドプロシージャで何も変更されていません。,
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS /* This is a sample stored procedure to show how comments work within a stored procedure */ -- declare variable DECLARE @ContactID INT -- set variable value SET @ContactID = 0 -- execute stored proc and return ContactID value EXEC uspFindContact @, @ OUTPUT -- if ContactID does not equal 0 then return data else return error IF @ContactID <> 0 BEGIN SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID = @ContactID SELECT d.AddressLine1, d.City, d.PostalCode FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.ContactID = @ContactID END ELSE BEGIN RAISERROR ("No record found",10,1) END
これらは非常に簡単な例ですが、うまくいけば、SQL Server用のストアドプロシージャを作成するのが簡単です。 クエリウィンドウまたはアプリケーションからSELECTステートメントを実行できる場合は、上記のように簡単にastoredプロシージャを実行できます。,
次のステップ
- まだストアドプロシージャを使用していない場合うまくいけば、これはあなたがそれらを使用し始めるために何をする必要があるかについてのいくつかの洞察を与えます
- ,最終更新：2020-07-30
著者について
greg Robidouxはedgewood solutionsの社長であり、MSSQLTips.com.
すべての私のヒントを表示します関連リソース- より多くのデータベース開発者のヒント。..