ストアド プロシージャの作成、変更、および削除
SQL Server 管理オブジェクト (SMO) では、ストアド プロシージャは StoredProcedure オブジェクトで表現されます。
SMO で StoredProcedure オブジェクトを作成する場合は、TextBody プロパティに、ストアド プロシージャを定義する Transact-SQL スクリプトを設定する必要があります。パラメーターには @ プレフィックスが必要です。パラメーターは StoredProcedureParameter オブジェクトを使用して個別に作成し、StoredProcedure オブジェクトの StoredProcedureParameter コレクションに追加する必要があります。
例
提供されているコード例を使用するには、アプリケーションを作成するプログラミング環境、プログラミング テンプレート、およびプログラミング言語を選択する必要があります。詳細については、「Visual Studio .NET で Visual Basic SMO プロジェクトを作成する方法」または「Visual Studio .NET で Visual C# SMO プロジェクトを作成する方法」を参照してください。
Visual Basic でのストアド プロシージャの作成、変更、および削除
このコード例では、AdventureWorks2008R2 データベースのストアド プロシージャを作成する方法を示します。この例では、従業員 ID 番号が指定されると、従業員の姓を返します。このストアド プロシージャには、従業員 ID 番号を指定する 1 つの入力パラメーターと、従業員の姓を返す 1 つの出力パラメーターが必要です。
'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks2008R2 database.
Dim db As Database
db = srv.Databases("AdventureWorks2008R2")
'Define a StoredProcedure object variable by supplying the parent database and name arguments in the constructor.
Dim sp As StoredProcedure
sp = New StoredProcedure(db, "GetLastNameByEmployeeID")
'Set the TextMode property to false and then set the other object properties.
sp.TextMode = False
sp.AnsiNullsStatus = False
sp.QuotedIdentifierStatus = False
'Add two parameters.
Dim param As StoredProcedureParameter
param = New StoredProcedureParameter(sp, "@empval", DataType.Int)
sp.Parameters.Add(param)
Dim param2 As StoredProcedureParameter
param2 = New StoredProcedureParameter(sp, "@retval", DataType.NVarChar(50))
param2.IsOutputParameter = True
sp.Parameters.Add(param2)
'Set the TextBody property to define the stored procedure.
Dim stmt As String
stmt = " SELECT @retval = (SELECT LastName FROM Person.Person AS p JOIN HumanResources.Employee AS e ON p.BusinessEntityID = e.BusinessEntityID AND e.BusinessEntityID = @empval )"
sp.TextBody = stmt
'Create the stored procedure on the instance of SQL Server.
sp.Create()
'Modify a property and run the Alter method to make the change on the instance of SQL Server.
sp.QuotedIdentifierStatus = True
sp.Alter()
'Remove the stored procedure.
sp.Drop()
Visual C# でのストアド プロシージャの作成、変更、および削除
このコード例では、AdventureWorks2008R2 データベースのストアド プロシージャを作成する方法を示します。この例では、従業員 ID 番号 (BusinessEntityID) が指定されると、従業員の姓を返します。このストアド プロシージャには、従業員 ID 番号を指定する 1 つの入力パラメーターと、従業員の姓を返す 1 つの出力パラメーターが必要です。
{
//Connect to the local, default instance of SQL Server.
Server srv;
srv = new Server();
//Reference the AdventureWorks2008R2 database.
Database db;
db = srv.Databases["AdventureWorks2008R2"];
//Define a StoredProcedure object variable by supplying the parent database and name arguments in the constructor.
StoredProcedure sp;
sp = new StoredProcedure(db, "GetLastNameByBusinessEntityID");
//Set the TextMode property to false and then set the other object properties.
sp.TextMode = false;
sp.AnsiNullsStatus = false;
sp.QuotedIdentifierStatus = false;
//Add two parameters.
StoredProcedureParameter param;
param = new StoredProcedureParameter(sp, "@empval", DataType.Int);
sp.Parameters.Add(param);
StoredProcedureParameter param2;
param2 = new StoredProcedureParameter(sp, "@retval", DataType.NVarChar(50));
param2.IsOutputParameter = true;
sp.Parameters.Add(param2);
//Set the TextBody property to define the stored procedure.
string stmt;
stmt = " SELECT @retval = (SELECT LastName FROM Person.Person,HumanResources.Employee WHERE Person.Person.BusinessEntityID = HumanResources.Employee.BusinessentityID AND HumanResources.Employee.BusinessEntityID = @empval )";
sp.TextBody = stmt;
//Create the stored procedure on the instance of SQL Server.
sp.Create();
//Modify a property and run the Alter method to make the change on the instance of SQL Server.
sp.QuotedIdentifierStatus = true;
sp.Alter();
//Remove the stored procedure.
sp.Drop();
}
PowerShell でのストアド プロシージャの作成、変更、および削除
このコード例では、AdventureWorks2008R2 データベースのストアド プロシージャを作成する方法を示します。この例では、従業員 ID 番号 (BusinessEntityID) が指定されると、従業員の姓を返します。このストアド プロシージャには、従業員 ID 番号を指定する 1 つの入力パラメーターと、従業員の姓を返す 1 つの出力パラメーターが必要です。
# Set the path context to the local, default instance of SQL Server and get a reference to AdventureWorks2008R2
CD \sql\localhost\default\databases
$db = get-item Adventureworks2008R2
# Define a StoredProcedure object variable by supplying the parent database and name arguments in the constructor.
$sp = New-Object -TypeName Microsoft.SqlServer.Management.SMO.StoredProcedure `
-argumentlist $db, "GetLastNameByBusinessEntityID"
#Set the TextMode property to false and then set the other object properties.
$sp.TextMode = $false
$sp.AnsiNullsStatus = $false
$sp.QuotedIdentifierStatus = $false
# Add two parameters
$type = [Microsoft.SqlServer.Management.SMO.Datatype]::Int
$param = New-Object -TypeName Microsoft.SqlServer.Management.SMO.StoredProcedureParameter `
-argumentlist $sp,"@empval",$type
$sp.Parameters.Add($param)
$type = [Microsoft.SqlServer.Management.SMO.DataType]::NVarChar(50)
$param2 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.StoredProcedureParameter `
-argumentlist $sp,"@retval",$type
$param2.IsOutputParameter = $true
$sp.Parameters.Add($param2)
#Set the TextBody property to define the stored procedure.
$sp.TextBody = " SELECT @retval = (SELECT LastName FROM Person.Person,HumanResources.Employee WHERE Person.Person.BusinessEntityID = HumanResources.Employee.BusinessentityID AND HumanResources.Employee.BusinessEntityID = @empval )"
# Create the stored procedure on the instance of SQL Server.
$sp.Create()
# Modify a property and run the Alter method to make the change on the instance of SQL Server.
$sp.QuotedIdentifierStatus = $true
$sp.Alter()
#Remove the stored procedure.
$sp.Drop()