Procedura: creare ed eseguire una stored procedure CLR di SQL Server

Articolo
09/12/2008

Aggiornamento: novembre 2007

Per creare stored procedure SQL è necessario aggiungere elementi Stored procedure ai progetti SQL Server. Una volta eseguita la distribuzione su un computer su cui è in esecuzione SQL Server, le stored procedure create nel codice gestito vengono chiamate ed eseguite come qualsiasi altra stored procedure.

Nota:
In Microsoft SQL Server la funzionalità di integrazione di CLR (Common Language Runtime) è disattivata per impostazione predefinita, ma deve essere attivata per poter utilizzare gli elementi del progetto SQL Server. Per attivare la funzionalità di integrazione di CLR, utilizzare l'opzione clr enabled della stored procedure sp_configure. Per ulteriori informazioni, vedere Attivazione dell'integrazione CLR.

In Microsoft SQL Server la funzionalità di integrazione di CLR (Common Language Runtime) è disattivata per impostazione predefinita, ma deve essere attivata per poter utilizzare gli elementi del progetto SQL Server. Per attivare la funzionalità di integrazione di CLR, utilizzare l'opzione clr enabled della stored procedure sp_configure. Per ulteriori informazioni, vedere Attivazione dell'integrazione CLR.

Nota:
È possibile che le finestre di dialogo e i comandi di menu visualizzati siano diversi da quelli descritti nella Guida in linea a seconda delle impostazioni attive o dell'edizione del programma. Per modificare le impostazioni, scegliere Importa/Esporta impostazioni dal menu Strumenti. Per ulteriori informazioni, vedere Impostazioni di Visual Studio.

Creazione di stored procedure SQL Server

Per creare una stored procedure SQL Server

Aprire un Progetto SQL Server esistente o crearne uno nuovo. Per ulteriori informazioni, vedere Procedura: creare un progetto SQL Server.
Scegliere Aggiungi nuovo elemento dal menu Progetto.
Selezionare Stored procedure nella Finestra di dialogo Aggiungi nuovo elemento.
Nella casella Nome digitare un nome per la nuova stored procedure.
Aggiungere il codice da eseguire durante l'esecuzione della stored procedure. Vedere l'esempio riportato di seguito.

Nota:

Gli esempi di C++ devono essere compilati con l'opzione del compilatore /clr:safe.
Per Visual Basic e Visual C#, in Esplora soluzioni aprire la cartella Script di test e fare doppio clic sul file Test.sql.

Per Visual C++, in Esplora soluzioni aprire il file debug.sql.
Aggiungere codice al file Test.sql (debug.sql per Visual C++) per eseguire la stored procedure. Vedere il secondo esempio riportato di seguito.
Premere F5 per compilare, distribuire ed eseguire il debug della stored procedure. Per informazioni sulla distribuzione senza debug, vedere Procedura: distribuire elementi del progetto SQL Server su SQL Server.
Visualizzare i risultati nella Finestra di output e selezionare Mostra output di: Output database.

Nota:
Gli esempi di C++ devono essere compilati con l'opzione del compilatore /clr:safe.

Esempio

Nell'esempio di codice che segue viene creata una stored procedure che inserisce un record nella tabella Currency del database di esempio Adventure Works. Una volta creata la stored procedure, distribuirla su SQL Server. Per ulteriori informazioni, vedere Procedura: distribuire elementi del progetto SQL Server su SQL Server.

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures

    <SqlProcedure()> _
    Public Shared Sub InsertCurrency( _
        ByVal currencyCode As SqlString, ByVal name As SqlString)

        Using conn As New SqlConnection("context connection=true")

            Dim InsertCurrencyCommand As New SqlCommand()
            Dim currencyCodeParam As New SqlParameter("@CurrencyCode", SqlDbType.NVarChar)
            Dim nameParam As New SqlParameter("@Name", SqlDbType.NVarChar)

            currencyCodeParam.Value = currencyCode
            nameParam.Value = name


            InsertCurrencyCommand.Parameters.Add(currencyCodeParam)
            InsertCurrencyCommand.Parameters.Add(nameParam)

            InsertCurrencyCommand.CommandText = _
                "INSERT Sales.Currency (CurrencyCode, Name, ModifiedDate)" & _
                " VALUES(@CurrencyCode, @Name, GetDate())"

            InsertCurrencyCommand.Connection = conn

            conn.Open()
            InsertCurrencyCommand.ExecuteNonQuery()
            conn.Close()
        End Using
    End Sub
End Class

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


public partial class StoredProcedures
{
    [SqlProcedure()]
    public static void InsertCurrency_CS(
        SqlString currencyCode, SqlString name)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand InsertCurrencyCommand = new SqlCommand();
            SqlParameter currencyCodeParam = new SqlParameter("@CurrencyCode", SqlDbType.NVarChar);
            SqlParameter nameParam = new SqlParameter("@Name", SqlDbType.NVarChar);

            currencyCodeParam.Value = currencyCode;
            nameParam.Value = name;

            InsertCurrencyCommand.Parameters.Add(currencyCodeParam);
            InsertCurrencyCommand.Parameters.Add(nameParam);

            InsertCurrencyCommand.CommandText =
                "INSERT Sales.Currency (CurrencyCode, Name, ModifiedDate)" +
                " VALUES(@CurrencyCode, @Name, GetDate())";

            InsertCurrencyCommand.Connection = conn;

            conn.Open();
            InsertCurrencyCommand.ExecuteNonQuery();
            conn.Close();
        }
    }
}

#include "stdafx.h"

#using <System.dll>
#using <System.Data.dll>
#using <System.Xml.dll>

using namespace System;
using namespace System::Data;
using namespace System::Data::Sql;
using namespace System::Data::SqlClient;
using namespace System::Data::SqlTypes;
using namespace Microsoft::SqlServer::Server;

// In order to debug your Stored Procedure, add the following to your debug.sql file:
//
// EXEC InsertCurrency_CPP 'AAA', 'Currency Test'
// SELECT * FROM Sales.Currency WHERE CurrencyCode = 'AAA'

public ref class StoredProcedures
{
public:
    [SqlProcedure]
    static void InsertCurrency_CPP(SqlString currencyCode, SqlString name)
    {
        SqlConnection ^conn = gcnew SqlConnection("context connection=true");

        SqlCommand ^insertCurrencyCommand = gcnew SqlCommand();
        SqlParameter ^currencyCodeParam =
            gcnew SqlParameter("@CurrencyCode", SqlDbType::NVarChar);
        SqlParameter ^nameParam =
            gcnew SqlParameter("@Name", SqlDbType::NVarChar);

        insertCurrencyCommand->CommandText =
            "insert Sales.Currency(CurrencyCode, Name, ModifiedDate)" +
            " values(@CurrencyCode, @Name)";
        insertCurrencyCommand->Connection = conn;

        conn->Open();
        insertCurrencyCommand->ExecuteNonQuery();

        conn->Close();
    }
};

Aggiungere il codice per eseguire e verificare la stored procedure al file Test.sql (debug.sql in Visual C++) nella cartella Script di test del progetto. Se, ad esempio, è stata distribuita una stored procedure, eseguirla chiamando EXEC <NomeStoredProcedure> e passando tutti i parametri previsti. Se la stored procedure non restituisce valori, inserire codice aggiuntivo per verificare se i dati sono stati modificati dalla stored procedure.

EXEC InsertCurrency 'AAA', 'Currency Test'
SELECT * from Sales.Currency where CurrencyCode = 'AAA'

Vedere anche

Condividi tramite

Procedura: creare ed eseguire una stored procedure CLR di SQL Server

Creazione di stored procedure SQL Server

Per creare una stored procedure SQL Server

Esempio

Vedere anche

Attività

Concetti

Riferimenti

Altre risorse

Risorse aggiuntive