Creazione, modifica e rimozione di stored procedure
In SMO (SQL Server Management Objects) le stored procedure sono rappresentate dall'oggetto StoredProcedure.
La creazione di un oggetto StoredProcedure in SMO richiede l'impostazione della proprietà TextBody sullo script Transact-SQL che definisce la stored procedure. I parametri richiedono il prefisso @ e devono essere creati singolarmente utilizzando oggetti StoredProcedureParameter e aggiungendoli alla raccolta StoredProcedureParameter dell'oggetto StoredProcedure.
Esempio
Per utilizzare qualsiasi esempio di codice fornito, è necessario scegliere l'ambiente di programmazione, il modello di programmazione e il linguaggio di programmazione per la creazione dell'applicazione. Per ulteriori informazioni, vedere Creare un progetto SMO per Visual Basic in Visual Studio .NET o Creare un progetto SMO per Visual C# in Visual Studio .NET.
Creazione, modifica e rimozione di una stored procedure in Visual Basic
In questo esempio di codice viene illustrato come creare una stored procedure per il database AdventureWorks2012. Nell'esempio viene restituito il cognome di un dipendente quando viene fornito il numero ID del dipendente. La stored procedure richiede un parametro di input per specificare il numero ID del dipendente e un parametro di output per restituire il cognome del dipendente.
'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()
Creazione, modifica e rimozione di una stored procedure in Visual C#
In questo esempio di codice viene illustrato come creare una stored procedure per il database AdventureWorks2012. Nell'esempio viene restituito il cognome di un dipendente quando viene fornito il numero ID del dipendente (BusinessEntityID). La stored procedure richiede un parametro di input per specificare il numero ID del dipendente e un parametro di output per restituire il cognome del dipendente.
{
//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();
}
Creazione, modifica e rimozione di una stored procedure in PowerShell
In questo esempio di codice viene illustrato come creare una stored procedure per il database AdventureWorks2012. Nell'esempio viene restituito il cognome di un dipendente quando viene fornito il numero ID del dipendente (BusinessEntityID). La stored procedure richiede un parametro di input per specificare il numero ID del dipendente e un parametro di output per restituire il cognome del dipendente.
# 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()