パラメータを持つSQL Serverストアドプロシージャ

admin
By:Greg Robidoux
概要

ストアドプロシージャの本当の力は、パラメータを渡し、ストアドプロシージャが行われた異なる要求 この記事では、ストアドプロシージャにパラメータ値を渡す方法を見ていきます。

説明

SQLコードでパラメータを使用できるように、ストアドプロシージャをセットアップして、一つ以上のパラメータ値を受け入れることもできます。 のデータベースを使用しています。,

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

  1. 次の構文を使用してパラメータを持つストアドプロシージャを作成します。
  2. CREATE PROCEDURE dbo。uspGetAddress@City nvarchar(30)AS
  3. 以下の詳細と例を参照してください

ストアドプロシージャに変換するSQL Serverクエリ

以下は、ストアドプロシージャを作成するため

USE AdventureWorksGOSELECT * FROM Person.AddressGO

アイデアは、都市がストアドプロシージャに渡されるストアドプロシージャを作成して、動的な結果を作成できるようにすることです。, これは次のように行うことができます変数。 以下のコードを実行すると、NewYorkの結果だけが返されます。

USE AdventureWorksGODECLARE @City nvarchar(30)SET @City = 'New York'SELECT * FROM Person.AddressWHERE City = @CityGO

このアプローチを使用して@City変数を更新し続けることができますが、ストアドプロシージャを作成してこれを行うより良い方法

一つのパラメータを持つSQL Serverストアドプロシージャを作成します

この例では、人にクエリを実行します。AdventureWorksdatabaseからのアドレステーブルですが、すべてのレコードを取り戻すのではなく、それを特定のものに制限します。, この例では、渡される都市の値に完全に一致するものがあることを前提としています。

USE AdventureWorksGOCREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)ASSELECT * FROM Person.AddressWHERE City = @CityGO

このストアドプロシージャを呼び出すには、次のように実行します。

EXEC dbo.uspGetAddress @City = 'New York'

ストアドプロシージャの削除

ストアドプロシージャを作成し、同じ名前のstoredprocedureを再作成する場合は、次の方法で削除できます。 

USE AdventureWorksGODROP PROCEDURE dbo.uspGetAddressGO

ストアドプロシージャを作成しようとすると、すでに存在する場合は、エラーメッセージが表示されます。,

メッセージ2714、レベル16、状態3、手順uspGetAddress、行1
データベースにはすでに’uspGetAddress’という名前のオブジェクトがあります。

ワイルドカードを使用してパラメータを持つSQL Serverストアドプロシージャ

我々はまた、同じことを行うことができますが、ユーザーが私たちにデータを検索

ここでは、”=”をLIKEに変更し、”%”ワイルドカードを使用できます。

これはNewで始まるすべての都市を見つけるために次のように実行できます。,

EXEC dbo.uspGetAddress @City = 'New'

SQL Serverストアドプロシージャエラーパラメーターが渡されなかったとき

この手順の両方の例では、パラメーター値が常に渡されることを前提 Parametervalueを渡さずにプロシージャを実行しようとすると、次のようなエラーメッセージが表示されます。

EXEC dbo.uspGetAddress
Msg201,Level16,State4,Procedure uspGetAddress,Line0
プロシージャまたは関数’uspGetAddress’は、パラメータ’@City’を,

SQL Serverストアドプロシージャ既定のパラメーターとしてNULLを使用して

ほとんどの場合、すべてのパラメーター値を渡すことをお勧めしますが、時には したがって、この例ではNULLオプションを使用して、パラメータ値を渡さないようにします。 このstoredprocedureをそのまま作成して実行すると、NULLに等しいCityvaluesを探しているため、データは返されません。 

USE AdventureWorksGOCREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30) = NULLASSELECT *FROM Person.AddressWHERE City = @CityGO

以下を実行すると動作しますが、データは返されません。,

EXEC dbo.uspGetAddress

このストアドプロシージャを変更し、ISNULL関数を使用して取得することができますこれ。 したがって、値が渡されると、その値を使用してresultsetを絞り込み、値が渡されない場合はすべてのレコードを返します。 （注：City列にNULL値がある場合、これらの値は含まれません。 City IS NULLのロジックを追加する必要があります）

以下のコマンドを実行すると、すべてのデータがテーブルから返されます。,

EXEC dbo.uspGetAddress

複数のパラメータを持つSQL Serverストアドプロシージャを作成します

複数のパラメータを設定することは非常に簡単です。 以下に示すように、パラメータとデータ型をカンマで区切ってlisteachするだけです。

これを実行するには、次のいずれかを行うことができます。

最終更新日：4/3/2020

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です