Procedura: creare ed eseguire una stored procedure SQL Server tramite l'integrazione di Common Language Runtime

Articolo
08/11/2011

Per creare stored procedure SQL, è necessario aggiungere elementi Stored procedure ai progetti di database Common Language Runtime di SQL Server (CLR SQL). Una volta eseguita la distribuzione in un computer in cui è in esecuzione SQL Server, le stored procedure create in codice gestito vengono chiamate ed eseguite come qualsiasi altra stored procedure.

Nota

Le finestre di dialogo e i comandi di menu visualizzati potrebbero non corrispondere a quelli descritti nella Guida in quanto dipendono dall'edizione o dalle impostazioni in uso. Per modificare le impostazioni, scegliere Importa/Esporta impostazioni dal menu Strumenti. Per ulteriori informazioni, vedere la classe Gestione delle impostazioni.

Creazione di stored procedure SQL Server

Per creare una stored procedure SQL Server

Aprire un progetto di database CLR SQL esistente o crearne uno nuovo. Per ulteriori informazioni, vedere Procedura: creare un progetto per gli oggetti di database che utilizzano l'integrazione di Common Language Runtime di SQL Server.
Scegliere Aggiungi nuovo elemento dal menu Progetto.
Nella finestra di dialogo Aggiungi nuovo elemento selezionare Stored procedure.
Nella casella Nome digitare un nome per la nuova stored procedure.
Aggiungere il codice da eseguire durante l'esecuzione della stored procedure. Vedere il primo esempio che segue questa procedura.
In Esplora soluzioni aprire la cartella Script di test e fare doppio clic sul file Test.sql.

Nota

È possibile specificare altri script come script di debug predefinito. Per ulteriori informazioni, vedere Procedura: modificare lo script Test.sql per eseguire oggetti che utilizzano l'integrazione di Common Language Runtime di SQL Server.
Aggiungere codice al file Test.sql per eseguire la stored procedure. Vedere il secondo esempio che segue questa procedura.

Premere F5 per compilare, distribuire ed eseguire il debug della stored procedure. Per informazioni sulla distribuzione senza debug, vedere Procedura: distribuire elementi di progetti di database CLR SQL Server su SQL Server.

Importante
SQL Server 2005 e SQL Server 2008 supportano solo i progetti SQL Server compilati con la versione 2.0, 3.0 o 3.5 di .NET Framework. Se si tenta di implementare un progetto SQL Server in SQL Server 2005 o SQL Server 2008, viene visualizzato un errore: Deploy error (SQL01268): .NET SqlClient Data Provider: Msg 6218, Level 16, State 3, Line 1 CREATE ASSEMBLY for assembly 'AssemblyName' failed because assembly 'AssemblyName' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database (dove AssemblyName è il nome dell'assembly che si sta implementando). Per ulteriori informazioni, vedere Procedura: creare un progetto per gli oggetti di database che utilizzano l'integrazione di Common Language Runtime di SQL Server.

SQL Server 2005 e SQL Server 2008 supportano solo i progetti SQL Server compilati con la versione 2.0, 3.0 o 3.5 di .NET Framework. Se si tenta di implementare un progetto SQL Server in SQL Server 2005 o SQL Server 2008, viene visualizzato un errore: Deploy error (SQL01268): .NET SqlClient Data Provider: Msg 6218, Level 16, State 3, Line 1 CREATE ASSEMBLY for assembly 'AssemblyName' failed because assembly 'AssemblyName' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database (dove AssemblyName è il nome dell'assembly che si sta implementando). Per ulteriori informazioni, vedere Procedura: creare un progetto per gli oggetti di database che utilizzano l'integrazione di Common Language Runtime di SQL Server.

Visualizzare i risultati presenti nella Finestra di output e selezionare Mostra output da: Output del database.

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 di progetti di database CLR 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();
        }
    }
}

Aggiungere codice per eseguire e verificare la stored procedure al file Test.sql presente 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.

Nota

Se l'esempio è stato creato con Visual C#, sostituire InsertCurrency_CS con il nome della stored procedure dell'esempio seguente.

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

Vedere anche

Condividi tramite

Procedura: creare ed eseguire una stored procedure SQL Server tramite l'integrazione di Common Language Runtime

Creazione di stored procedure SQL Server

Per creare una stored procedure SQL Server

Esempio

Vedere anche

Attività

Riferimenti

Concetti

Altre risorse

Risorse aggiuntive