ストアド プロシージャの作成、変更、および削除

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 でのストアド プロシージャの作成、変更、および削除

このコード例では、AdventureWorks2012 データベースのストアド プロシージャを作成する方法を示します。 この例では、従業員 ID 番号が指定されると、従業員の姓を返します。 このストアド プロシージャには、従業員 ID 番号を指定する 1 つの入力パラメーターと、従業員の姓を返す 1 つの出力パラメーターが必要です。

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks2012 2008R2 database.
Dim db As Database
db = srv.Databases("AdventureWorks2012")
'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# でのストアド プロシージャの作成、変更、および削除

このコード例では、AdventureWorks2012 データベースのストアド プロシージャを作成する方法を示します。 この例では、従業員 ID 番号 (BusinessEntityID) が指定されると、従業員の姓を返します。 このストアド プロシージャには、従業員 ID 番号を指定する 1 つの入力パラメーターと、従業員の姓を返す 1 つの出力パラメーターが必要です。

{
            //Connect to the local, default instance of SQL Server. 
            Server srv;
            srv = new Server();
            //Reference the AdventureWorks2012 database. 
            Database db;
            db = srv.Databases["AdventureWorks2012"];
            //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 でのストアド プロシージャの作成、変更、および削除

このコード例では、AdventureWorks2012 データベースのストアド プロシージャを作成する方法を示します。 この例では、従業員 ID 番号 (BusinessEntityID) が指定されると、従業員の姓を返します。 このストアド プロシージャには、従業員 ID 番号を指定する 1 つの入力パラメーターと、従業員の姓を返す 1 つの出力パラメーターが必要です。

# Set the path context to the local, default instance of SQL Server and get a reference to AdventureWorks2012
CD \sql\localhost\default\databases
$db = get-item Adventureworks2012

# 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()

関連項目

参照

StoredProcedure