通过

创建、更改和删除存储过程

  • 项目

在 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 号，需要一个输出参数来返回该雇员的姓氏。

'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 号，需要一个输出参数来返回该雇员的姓氏。

{
            //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 号，需要一个输出参数来返回该雇员的姓氏。

# 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