Comment : créer et exécuter une procédure stockée SQL Server à l'aide de l'intégration du Common Language Runtime

Article
08/10/2011

Créez des procédures stockées SQL en ajoutant des éléments Procédure stockée aux projets de base de données du Common Language Runtime (CLR SQL). Une fois le déploiement sur un ordinateur SQL Server effectué, les procédures stockées créées dans du code managé sont appelées et exécutées comme toute autre procédure stockée.

Notes

Selon vos paramètres actifs ou votre édition, les boîtes de dialogue et les commandes de menu que vous voyez peuvent différer de celles qui sont décrites dans l'aide. Pour modifier vos paramètres, choisissez Importation et exportation de paramètres dans le menu Outils. Pour plus d'informations, consultez Utilisation des paramètres.

Création de procédures stockées SQL Server

Pour créer une procédure stockée SQL Server

Ouvrez un projet de base de données CLR SQL existant ou créez-en un. Pour plus d'informations, consultez Comment : créer un projet pour des objets de base de données qui utilisent l'intégration du Common Language Runtime SQL Server.
Dans le menu Projet, cliquez sur Ajouter un nouvel élément.
Dans la boîte de dialogue Ajouter un nouvel élément, sélectionnez Procédure stockée.
Tapez le Nom de la nouvelle procédure stockée.
Ajoutez le code à exécuter lors de l'application de la procédure stockée. Consultez le premier exemple qui suit cette procédure.
Dans l'Explorateur de solutions, ouvrez le dossier TestScripts et double-cliquez sur le fichier Test.sql.

Notes

Vous pouvez spécifier d'autres scripts comme script de débogage par défaut. Pour plus d'informations, consultez Comment : modifier le script Test.sql pour exécuter des objets qui utilisent l'intégration du Common Language Runtime SQL Server.
Ajoutez le code au fichier Test.sql pour exécuter la procédure stockée. Consultez le deuxième exemple qui suit cette procédure.
Appuyez sur F5 pour générer, déployer et déboguer la procédure stockée. Pour plus d'informations sur le déploiement sans débogage, consultez Comment : déployer des éléments de projet de base de données CLR SQL sur un serveur SQL Server.

Important

SQL Server 2005 et SQL Server 2008 prennent uniquement en charge les projets SQL Server générés avec la version 2.0, 3.0 ou 3.5 de .NET Framework. Si vous essayezSQL Server de déployer un projet versSQL Server 2005 ouSQL Server 2008, une erreur s'affiche : 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 (où AssemblyName est le nom de l'assembly que vous déployez). Pour plus d'informations, consultez Comment : créer un projet pour des objets de base de données qui utilisent l'intégration du Common Language Runtime SQL Server.
Affichez les résultats dans etSortie, fenêtre entrez la sortie de spectacle de : Sortie de base de données.

Exemple

L'exemple de code suivant crée une procédure stockée qui insère un enregistrement dans la table Currency de l'exemple de base de données Adventure Works. Une fois la procédure créée, déployez-la sur SQL Server. Pour plus d'informations, consultez Comment : déployer des éléments de projet de base de données CLR SQL sur un serveur 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();
        }
    }
}

Ajoutez le code permettant d'exécuter et de tester votre procédure stockée au fichier Test.sql situé dans le dossier Scripts de test de votre projet. Par exemple, si vous avez déployé une procédure stockée, exécutez-la en appelant EXEC <StoredProcedureName> et en passant les paramètres attendus. Si votre procédure stockée ne retourne aucune valeur, insérez le code supplémentaire afin de vérifier que les données ont été affectées par la procédure stockée.

Notes

Si vous avez créé l'exemple à l'aide de Visual C#, substituez InsertCurrency_CS au nom de procédure stockée dans l'exemple suivant.

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

Voir aussi

Partager via

Comment : créer et exécuter une procédure stockée SQL Server à l'aide de l'intégration du Common Language Runtime

Création de procédures stockées SQL Server

Pour créer une procédure stockée SQL Server

Exemple

Voir aussi

Tâches

Référence

Concepts

Autres ressources

Ressources supplémentaires