创建、更改和删除存储过程
在 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 数据库创建存储过程。如果提供了雇员的 ID 号，则将返回该雇员的姓氏。存储过程需要一个输入参数来指定雇员的 ID 号，需要一个输出参数来返回该雇员的姓氏。
'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 数据库创建存储过程。如果提供了雇员的 ID 号，则将返回该雇员的姓氏。存储过程需要一个输入参数来指定雇员的 ID 号，需要一个输出参数来返回该雇员的姓氏。
{
//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();
}