Erstellen, Ändern und Löschen von gespeicherten Prozeduren
In SQL Server Management Objects (SMO) werden gespeicherte Prozeduren durch das StoredProcedure-Objekt dargestellt.
Die Erstellung eines StoredProcedure-Objekts in SMO erfordert, dass die Einstellung der TextBody-Eigenschaft auf das Transact-SQL-Skript gesetzt wird, das die gespeicherte Prozedur definiert. Die Parameter erfordern das @-Prefix und müssen einzeln mithilfe der StoredProcedureParameter-Objekte und durch Hinzufügen der StoredProcedureParameter-Auflistung des StoredProcedure-Objekts erstellt werden.
Beispiel
Um die bereitgestellten Codebeispiele verwenden zu können, müssen Sie die Programmierumgebung, die Programmiervorlage und die Programmiersprache wählen, in der die Anwendung erstellt werden soll. Weitere Informationen finden Sie unter Erstellen eines Visual Basic-SMO-Projekts in Visual Studio.NET oder Erstellen eines Visual C# SMO-Projekts in Visual Studio.NET.
Erstellen, Ändern und Entfernen einer gespeicherten Prozedur in Visual Basic
Dieses Codebeispiel zeigt, wie eine gespeicherte Prozedur für die AdventureWorks2012-Datenbank erstellt wird. Im Beispiel wird der Nachname eines Mitarbeiters zurückgegeben, wenn die Mitarbeiter-ID-Nummer angegeben wird. Die gespeicherte Prozedur erfordert einen Eingabeparameter zur Angabe der Mitarbeiter-ID-Nummer und einen Ausgabeparameter für die Rückgabe des Nachnamens des Mitarbeiters.
'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()
Erstellen, Ändern und Löschen einer gespeicherten Prozedur in Visual C#
Dieses Codebeispiel zeigt, wie eine gespeicherte Prozedur für die AdventureWorks2012-Datenbank erstellt wird. Im Beispiel wird der Nachname eines Mitarbeiters zurückgegeben, wenn die Mitarbeiter-ID-Nummer angegeben wird (BusinessEntityID). Die gespeicherte Prozedur erfordert einen Eingabeparameter zur Angabe der Mitarbeiter-ID-Nummer und einen Ausgabeparameter für die Rückgabe des Nachnamens des Mitarbeiters.
{
//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();
}
Erstellen, Ändern und Löschen einer gespeicherten Prozedur in PowerShell
Dieses Codebeispiel zeigt, wie eine gespeicherte Prozedur für die AdventureWorks2012-Datenbank erstellt wird. Im Beispiel wird der Nachname eines Mitarbeiters zurückgegeben, wenn die Mitarbeiter-ID-Nummer angegeben wird (BusinessEntityID). Die gespeicherte Prozedur erfordert einen Eingabeparameter zur Angabe der Mitarbeiter-ID-Nummer und einen Ausgabeparameter für die Rückgabe des Nachnamens des Mitarbeiters.
# 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()