建立、改變和移除預存程序
在 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 中建立、改變和移除預存程序
此程式碼範例示範如何為 AdventureWorks 資料庫建立預存程序。以下範例在給定員工識別碼時會傳回員工的姓。預存程序需要一個輸入參數來指定員工識別碼，以及一個輸出參數來傳回員工的姓。
'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'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.Contact,HumanResources.Employee WHERE Person.Contact.ContactID = HumanResources.Employee.ContactID AND HumanResources.Employee.EmployeeID = @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# 中建立、改變和移除預存程序
此程式碼範例示範如何為 AdventureWorks 資料庫建立預存程序。以下範例在給定員工識別碼時會傳回員工的姓。預存程序需要一個輸入參數來指定員工識別碼，以及一個輸出參數來傳回員工的姓。
{
//Connect to the local, default instance of SQL Server.
Server srv;
srv = new Server();
//Reference the AdventureWorks database.
Database db;
db = srv.Databases("AdventureWorks");
//Define a StoredProcedure object variable by supplying the parent database and name arguments in the constructor.
StoredProcedure sp;
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.
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.Contact,HumanResources.Employee WHERE Person.Contact.ContactID = HumanResources.Employee.ContactID AND HumanResources.Employee.EmployeeID = @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();
}