Requetes SQL dynamiques et paramètres dynamiques

Le 18/11/2017 à 00:00

Bonsoir à tous,

Je me permets de solliciter votre aide afin de finaliser un de mes projets.

Je dois alimenter une feuille excel avec les résultats de requêtes SQL dont les paramètres se situent dans un tableau annexe.

Ma problématique vient du fait que le nombre de requetes SQL a effectuer doit etre dynamique et que je dois boucler sur les paramètres contenu dans le tableau.

A ce jour, je duplique autant de fois le code et les variables qui constituent la requete qu'il y a de lignes dans la tableau de paramétrage.

Je n'arrive pas à conceptualiser le tableau et les différentes boucles afin d'automatiser définitivement l’exécution de requête SQL et ce quelque soit le nombre

Voici le tableau de paramétrage des requetes qui se présente sous cette forme, 3 lignes de paramétrage à l'occurence.

NomRapport TableA TableB Champ1 Champ2 Champ3

Rapport21 CLIENTS_21 RDV_21 ville tel_cli adresse_email

Rapport71 CLIENTS_71 RDV_71 commune telephone mail

Rapport69 CLIENTS_69 RDV_69 city tel email

Ci-dessous mon code VBA que j'ai simplifié , vous remarquerez que je dois dupliquer autant de fois le code de la construction de la requête et de son exécution qu'l pourra en avoir dans le tableau des paramétrés

Sub GenererStats()

Dim ServeurSQL As String

Dim LoginSQL As String

Dim PasswordSQL As String

Dim BaseSQL As String

Dim TableAppels As String

Dim TableClients As String

Dim rs1 As String

Dim i, j, r As String

Dim Rapport1, Rapport2, Rapport3 As String

Dim TableA1, TableA2, TableA3 As String

Dim TableB1, TableB2, TableB3 As String

Dim Champ1A, Champ1B, Champs1C As String

Dim Champ2A, Champ2B, Champs2C As String

Dim Champ3A, Champ3B, Champs3C As String

Dim rs As ADODB.Recordset

Dim cn As ADODB.Connection

Set cn = New ADODB.Connection

Set rs = New ADODB.Recordset

'connexion sql serveur

cn.ConnectionString = "provider=sqloledb.1;data source=" & ServeurSQL & ";user id='" & LoginSQL & "';password= '" & PasswordSQL & "';initial catalog= '" & BaseSQL & "'"

cn.Open

rs.ActiveConnection = cn

'parametre requete 1

Rapport1 = Sheet("liste").Range("A2").Value

TableA1 = Sheet("liste").Range("B2").Value

TableB1 = Sheet("liste").Range("C2").Value

Champ1A = Sheet("liste").Range("D2").Value

Champ2A = Sheet("liste").Range("E2").Value

Champ3A = Sheet("liste").Range("F2").Value

'parametre requete 2

Rapport2 = Sheet("liste").Range("A3").Value

TableA2 = Sheet("liste").Range("B3").Value

TableB2 = Sheet("liste").Range("C3").Value

Champ1B = Sheet("liste").Range("D3").Value

Champ2B = Sheet("liste").Range("E3").Value

Champ3B = Sheet("liste").Range("F3").Value

'modele requete sql

rs1 = "SELECT 'Rapport' AS NOM_RAPPORT, TableB.VILLE, TableB.TEL, TableB.EMAIL, "

rs1 = rs1 + "COUNT(CASE WHEN TableA.STATUS = 'RDV') THEN 1 ELSE NULL END) AS NB_RDV, "

rs1 = rs1 + "COUNT(TableA.INDICE) AS NB_FICHE "

rs1 = rs1 + "FROM TableA INNER JOIN TableB ON TableA.ID = TableB.ID "

rs1 = rs1 + "GROUP BY TableB.VILLE, TableB.TEL, TableB.EMAIL "

Sheets("consolidation").Select

Cells.Select

Selection.ClearContents

'execution rapport 1 du tableau

If Rapport1 <> "" Then

rs2 = Replace(rs1, "TableA", "" & TableA1 & "")

rs2 = Replace(rs2, "TableB", "" & TableB1 & "")

rs2 = Replace(rs2, "Rapport", "" & Rapport1 & "")

rs2 = Replace(rs2, "Champ1", "" & Champ1A & "")

rs2 = Replace(rs2, "Champ2", "" & Champ2A & "")

rs2 = Replace(rs2, "Champ3", "" & Champ3A & "")

rs.Open rs2

For i = 0 To rs.Fields.Count - 1

Cells(1, i + 1) = rs.Fields(i).Name

Cells(1, i + 1).Interior.ColorIndex = 15

Next

'ligne de départ

r = 2

Do While Not rs.EOF

For j = 0 To rs.Fields.Count - 1

Cells(r, j + 1) = rs.Fields(j)

Next j

rs.MoveNext

r = r + 1

Loop

rs.Close

End If

'execution rapport 2 du tableau

If Rapport2 <> "" Then

rs2 = Replace(rs1, "TableA", "" & TableA2 & "")

rs2 = Replace(rs2, "TableB", "" & TableB2 & "")

rs2 = Replace(rs2, "Rapport", "" & Rapport2 & "")

rs2 = Replace(rs2, "Champ1", "" & Champ1B & "")

rs2 = Replace(rs2, "Champ2", "" & Champ2B & "")

rs2 = Replace(rs2, "Champ3", "" & Champ3B & "")

rs.Open rs2

Do While Not rs.EOF

For j = 0 To rs.Fields.Count - 1

Cells(r, j + 1) = rs.Fields(j)

Next j

rs.MoveNext

r = r + 1

Loop

rs.Close

End If

End Sub

Pouvez-vous donc s'il vous plaît m' orienter sur la démarche et la structure à créer et à mettre en place afin de rendre ce code totalement dynamique.

En espérant avoir été explicite, je vous remercie par avance pour votre aide.

Bonne soirée.

37requete-sql-dynamique.xlsm (18.55 Ko)
Le 18/11/2017 à 09:12

Bonjour,

Un truc dans le genre ? Surement à adapter encore mais c'est une piste :

Sub GenererStats()

    Dim ServeurSQL As String
    Dim LoginSQL As String
    Dim PasswordSQL As String
    Dim BaseSQL As String
    Dim TableAppels As String
    Dim TableClients As String
    Dim SQL As String
    Dim i, j, r As String
    Dim TableA As String
    Dim TableB As String
    Dim Rapport As String
    Dim Rapport1, Rapport2, Rapport3 As String '<---attention, seul Rapport3 est typé "String", les deux autres sont typés "Variant"
    'Dim Champ1A, Champ1B, Champs1C As String
    'Dim Champ2A, Champ2B, Champs2C As String
    'Dim Champ3A, Champ3B, Champs3C As String
    Dim RS As ADODB.Recordset
    Dim CN As ADODB.Connection

    Set CN = New ADODB.Connection
    Set RS = New ADODB.Recordset

    ' Pas initialisé ?!
    '---> ServeurSQL
    '---> LoginSQL
    '---> PasswordSQL
    '---> BaseSQL

    'connexion sql serveur
    CN.ConnectionString = "provider=sqloledb.1;data source=" & ServeurSQL & ";user id='" & LoginSQL & "';password= '" & PasswordSQL & "';initial catalog= '" & BaseSQL & "'"
    CN.Open
    RS.ActiveConnection = CN

    Sheets("consolidation").Select
    Cells.Select
    Selection.ClearContents

    Rapport = Sheet("liste").Range("A2").Value
    TableA = Sheet("liste").Range("B2").Value
    TableB = Sheet("liste").Range("C2").Value

    If Rapport <> "" Then

        SQL = "SELECT '" & Rapport & "' AS NOM_RAPPORT, " & TableB & ".VILLE, " & TableB & ".TEL, " & TableB & ".EMAIL, "
        SQL = SQL & "COUNT(CASE WHEN " & TableA & ".STATUS = 'RDV') THEN 1 ELSE NULL END) AS NB_RDV, "
        SQL = SQL & "COUNT(" & TableA & ".INDICE) AS NB_FICHE "
        SQL = SQL & "FROM " & TableA & " INNER JOIN " & TableB & " ON " & TableA & ".ID = " & TableB & ".ID "
        SQL = SQL & "GROUP BY " & TableB & ".VILLE, " & TableB & ".TEL, " & TableB & ".EMAIL "

       RS.Open SQL

        For i = 0 To RS.Fields.Count - 1
            Cells(1, i + 1) = RS.Fields(i).Name
            Cells(1, i + 1).Interior.ColorIndex = 15
        Next

        'ligne de départ
        r = 2

        Do While Not RS.EOF

            For j = 0 To RS.Fields.Count - 1: Cells(r, j + 1) = RS.Fields(j): Next j

            RS.MoveNext
            r = r + 1

        Loop

        RS.Close

    End If

    Rapport = Sheet("liste").Range("A3").Value
    TableA = Sheet("liste").Range("B3").Value
    TableB = Sheet("liste").Range("C3").Value

    'execution rapport 2 du tableau
    If Rapport <> "" Then

        SQL = "SELECT '" & Rapport & "' AS NOM_RAPPORT, " & TableB & ".VILLE, " & TableB & ".TEL, " & TableB & ".EMAIL, "
        SQL = SQL & "COUNT(CASE WHEN " & TableA & ".STATUS = 'RDV') THEN 1 ELSE NULL END) AS NB_RDV, "
        SQL = SQL & "COUNT(" & TableA & ".INDICE) AS NB_FICHE "
        SQL = SQL & "FROM " & TableA & " INNER JOIN " & TableB & " ON " & TableA & ".ID = " & TableB & ".ID "
        SQL = SQL & "GROUP BY " & TableB & ".VILLE, " & TableB & ".TEL, " & TableB & ".EMAIL "

        RS.Open SQL

        Do While Not RS.EOF

            For j = 0 To RS.Fields.Count - 1: Cells(r, j + 1) = RS.Fields(j): Next j

            RS.MoveNext
            r = r + 1

        Loop

        RS.Close

    End If

End Sub
Le 02/12/2017 à 08:07

Finalement, voici mon code pour executer autant de requetes sql que l'on souhaite en fonction des parametres renseignées dans un tableau.

A adapter en fonction des besoins

Option Explicit
Option Base 1
Sub GenererReqDyn()
'Declaration des variables
'Appel de la fonction de connexion au serveur SQL :
ConnexionSQL
rs.ActiveConnection = cn
'Requete SQL :
rs1 = "SELECT 'NomCampagne' AS NOM_CAMPAGNE, TableClients.CodeAff, TableClients.NomAff, TableClients.MarqueAff, "
rs1 = rs1 + "COUNT(CASE  WHEN TableAppels.STATUS IN (1) THEN 1 ELSE NULL END) AS RDV, "
rs1 = rs1 + "FROM TableAppels INNER JOIN TableClients ON TableAppels.INDICE = TableClients.INDICE "
rs1 = rs1 + "GROUP BY TableClients.CodeAff, TableClients.NomAff, TableClients.MarqueAff "

'on vide la feuille cible
Sheets("cible").Select
Cells.Select
Selection.ClearContents

'initialisation du tableal
Sheets("Tableau").Select
Montab = Sheets("Tableau").Range("A1:H500").Value

'position du premier resultat de la requete
r = 2

For i = LBound(Montab, 1) To UBound(Montab, 1)
'Affectation des valeurs du tableau
    NomCampagne01 = Montab(i, 1)
'Sortie de la boucle si non renseignée
    If NomCampagne01 = "" Then Exit For
        TableAppels01 = Montab(i, 2)
        TableClients01 = Montab(i, 3)
        CodeAff01 = Montab(i, 4)
        NomAff01 = Montab(i, 5)
        MarqueAff01 = Montab(i, 6)

'Remplacement des critères de la requete SQL par celles du tableau
    rs2 = Replace(rs1, "TableAppels", "" & TableAppels01 & "")
    rs2 = Replace(rs2, "TableClients", "" & TableClients01 & "")
    rs2 = Replace(rs2, "NomCampagne", "" & NomCampagne01 & "")
    rs2 = Replace(rs2, "CodeAff", "" & CodeAff01 & "")
    rs2 = Replace(rs2, "NomAff", "" & NomAff01 & "")
    rs2 = Replace(rs2, "MarqueAff", "" & MarqueAff01 & "")

'selection de la feuille cible
Sheets("cible").Select
'execution de la requete SQL
rs.Open rs2

'Initialisation de la 1ere ligne
If Cells(1, 1) = "" Then
    For j = 0 To rs.Fields.Count - 1
        Cells(1, j + 1) = rs.Fields(j).Name
        Cells(1, j + 1).Interior.ColorIndex = 15
    Next
End If

'Execution requete SQL et remplissage
Do While Not rs.EOF
    For k = 0 To rs.Fields.Count - 1
        Cells(r, k + 1) = rs.Fields(k)
    Next k
    rs.MoveNext
    r = r + 1
Loop
rs.Close
Next i
End Sub
Rechercher des sujets similaires à "requetes sql dynamiques parametres"