Discussion :

[facteur]

je suis en train de parcourir des forum sur la création de vue en sql server et honnêtement je ne vois pas de difference entre une requête sql et une vue au niveau de l'architecture

[facteur]

je m'aperçois en parcouru les forums sur les vues que j'ai oublié de préciser une chose qui me semble importante sur mes requêtes sql.
Elles sont toutes deux paramètres de temps (datetime) debut et fin avec le format jj/mm/aaaa hh:mm
Est ce que dans ce cas précis, cela change quelque chose dans la création de vue?

[Thumb down]

Voilà une requête parramtré
Select * from table WHERE debut=@debut And fin=@fin

[facteur]

Bonjour Thumb
je sais créer une requêe sql paramétrée
Ma question est : peut on créer "une vue" dont la requête est paramétré

[Thumb down]

bonjour,
c'est juste de la dialectique!
une vue ou un requête c'est la même chose sauf que dans Sql Serveur on parle de vue.
tu peut écrire ta vue comme la requête que j'ai donné au poste précédent.

[facteur]

ok merci pour l'info Thumb
je vais tester tout de suite

[facteur]

me revoila
j'ai essayé de cr&er ma vue mais cela ne fonctionne .
J'ai un message d'erreur qui me dis que

'CREATE VIEW' doit être la première instruction d'un traitement de requêtes.

Voici ma requete

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
--Declarer les variables
DECLARE @Type1       Int = 1  
DECLARE @Type2        Int = 2
 
 
DECLARE @StartDateTime DateTime = '20200916 05:00:00'
DECLARE @EndDateTime DateTime =   '20200917 05:00:00'
 
CREATE VIEW  teste as 
 
SELECT
P.Num as rang
FROM P
inner JOIN PE
ON P.Id=PE.P.Id AND 
PE.EventTypeNum IN (@TYPE1,@TYPE2) AND
PE.[DateTime] BETWEEN @StartDateTime AND @EndDateTime

[facteur]

J'ai réecrit ma vue en supprimant mes variables

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
CREATE VIEW  teste as 
SELECT
P.PhysicalChuteNum as Sortie
FROM 
[base donnee].dbo.P
JOIN [base donnee].dbo.PE
ON P.Id=PE.P.Id AND 
PE.EventTypeNum IN (2,34) AND
PE.[DateTime] BETWEEN 'debut' AND 'fin'

la création de la vue s'est faite..
c'est déjà un bon debut

mais lorsque j'essaie d'appeler cette vue à partir d'une feuille Excel, j'ai toujours le message
nom d'objet dbo.teste non valide

[facteur]

Apres des recherches, j'ai résolu problème du message d'erreur

nom d'objet dbo.teste non valide

. C'etait un problème de connexion au serveur.
Maintenant on me demande de déclarer les variables @debut et @fin....

[Thumb down]

bonjour,
dans le requêteur exécute

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE FUNCTION TestTM(@Debut datetime,@Fin datetime)
RETURNS TABLE
AS
RETURN 
(SELECT
P.PhysicalChuteNum as Sortie
FROM 
dbo.P
JOIN dbo.PE
ON P.Id=PE.P.Id AND 
PE.EventTypeNum IN (2,34) AND
PE.[DateTime] BETWEEN @Debut AND @Fin
)

ensuite exécute

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

select * from [dbo].[TestTM]('2020-01-01','2020-01-02')

[facteur]

Bonjour Thumb
Effectivement, dans des forum, il parlait de la fonction CREATE FUNCTION pour la création de vue parametrée.
Donc si j'ai bien compris :

Etape 1
dans le dossier VUE de sql server, j'insère le code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CREATE FUNCTION TestTM(@Debut datetime,@Fin datetime)

Etape 2
Sur la feuille Excel où je souhaite voir apparaitre le résultat de la vue j'insere le code

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

select * from [dbo].[TestTM]('2020-01-01','2020-01-02')

dans la code vba du départ ( ligne 17 cf ci-dessous)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
Sub test()
 
Dim sql As String
Dim GenereCSTRING As String
Dim Debut As Object
 
Dim Fin As Object
Dim Cm As Object 'Pour utiliser une requête parametrés il faut utiliser l'oblet Command!
Dim cn As Object 'Pour ce connecter à un base de données Access il faut utiliser l'objet Connection!
Dim rs As Object
 
Dim i As Integer
 
Sheets("feuil1").Select
 
 
sql ="select * from [dbo].[TestTM]('2020-01-01','2020-01-02')"
 
Set cn = CreateObject("ADODB.Connection")
Set Cm = CreateObject("Adodb.Command")
  'MsgBox ThisWorkbook.Path
 
 GenereCSTRING = "Provider=SQLOLEDB.1;Password=TON_PASSWORD;Persist Security Info=True;User ID=tintin;Initial Catalog=NOM_DE_TA_DB;Data Source=10.20.30.40.50
 
 
 
 cn.Open GenereCSTRING 'J'ouvre ma connection.
 
 
Cm.CommandText = sql 'je passe ma requête à mon objet command
Cm.ActiveConnection = cn 'j'affecte ma connection à ma commande
 
Const adDate = 7 'defini le type de parametre
 
 
Set Debut = CreateObject("ADODB.Parameter")
Debut.Name = "debut": Debut.Type = adDate: Debut.Value = CDate(Format(ThisWorkbook.Sheets("feuil2").Range("B97").Value, "yyyy-mm-dd hh:mm")): Cm.Parameters.Append Debut
 
Set Fin = CreateObject("ADODB.Parameter") 'c'est avec l'objet Parameter que je vais reseigner les parmetres de ma requête Access.
Fin.Name = "Fin": Fin.Type = adDate: Fin.Value = CDate(Format(ThisWorkbook.Sheets("Feuil2").Range("G97").Value, "yyyy-mm-dd hh:mm")): Cm.Parameters.Append Fin
 
 
 
Set rs = Cm.Execute 'on recupere le résultat de la requête.
 
 
With ThisWorkbook.Sheets("Feuil1")
 
 
     For i = 0 To rs.Fields.Count - 1 'On place le nom des champs sur la ligne 4 de ma feuille Excel
               .Range("A1").Offset(0, i) = rs(i).Name
     Next
 
        .Range("A2").CopyFromRecordset rs 'la on copie le résultat de la requête!

[Pierre Fauconnier]

Salut.

Mon grain de sel...

J'ai écrit quatre billets, amorcés à partir de celui-ci. ils modélisent l'accès à un serveur de données, dont il suffit de renseigner la chaine de connexion.

• Vue = Requête... de sélection non paramétrable;
• Procédure stockée (éventuellement paramétrée) = "Vue" paramétrable et Requête Action (Insert Into, Update, Delete)...

PS: Pour moi, ça n'a pas trop de sens de créer des fonctions, des vues ou des procédures stockées par code si on accès au serveur Source. Ces outils devraient préexister à leur utilisation par des outils tels que le vba (et on peut espérer que les DBA ont bloqué ces possibilités sur les serveurs SQL)... . Bien sûr, dans le cadre d'un fichier Access externe ou d'un sql sur lequel on n'a pas la main (chez le(s) client(s)), on pourra avoir une utilité à ces modifs, pour autant que les DBA aient permis ces modifs.


Bon amusement

[Thumb down]

Salut.

Mon grain de sel...
---
PS: Pour moi, ça n'a pas trop de sens de créer des fonctions, des vues ou des procédures stockées par code si on accès au serveur Source. Ces outils devraient préexister à leur utilisation par des outils tels que le vba (et on peut espérer que les DBA ont bloqué ces possibilités sur les serveurs SQL)... . Bien sûr, dans le cadre d'un fichier Access externe ou d'un sql sur lequel on n'a pas la main (chez le(s) client(s)), on pourra avoir une utilité à ces modifs, pour autant que les DBA aient permis ces modifs.


Bon amusement

bonjour Pierre,
tu es le bien venu!

je ne comprends pas ce que tu veux dire! il est question de créer un fonction paramétrable dans Sql Serveur!

le script que j'ai fourni est un exemple de fonction sql serveur et l'autre un example d’utilisation dans sql manger!

[Pierre Fauconnier]

Salut Thumb.

[...]
tu es le bien venu![...]

Merci



Au départ, la demande ne concerne pas une fonction para (je suppose que tu veux dire paramétrée) mais une requête Access qui migre vers du sql server. Je pense donc que, dans ce cas de figure, il est intéressant de créer d'abord une procédure stockée paramétrée sur le serveur SQL puis de l'utiliser au travers d'une dal (Data Access Layer) qui appelle cette procédure stockée paramétrée. L'utilisation de ADODB permet de basculer assez aisément d'Access vers Sql, puisque l'objet de la passerelle ADODB est justement de s'affranchir du type de serveur (dans une certaine mesure).


Je rappelle qu'au départ, il est question de créer une vue sql sur base d'une requête Access.

Dans ton exemple, je ne comprends pas l'utilité de créer une fonction qui retourne une table. Un procédure stockée paramétrée retourne un jeu d'enregistrements qui correspond à "la requête" initiale existant en Access, paramètres en plus...

Je peux imaginer qu'il peut être utile de créer, à la volée, des procédures ou fonctions stockées sur un serveur SQL, mais je pense que cet usage doit être limité, dans les utilisations courantes d'un sql server.

Il en va bien évidemment autrement si l'on imagine la mise à jour d'un sql server distant pour une application distribuée et fonctionnant en connexion sur plusieurs SQL Server, mais je pense que ce cas de figure dépasse le cadre de VBA et de la présente discussion.

[Thumb down]

pour ce que j'ai compris, mais c'est à notre amis qu'il appartient de le confirmer, c'est lui qui à migré vers Sql Serveur.

il voudrait , je mets le conditionnel, il voudrait une Requête paramétré, bien que ça n'existe pas, dans Sql Serveur!
j'ai compris, à tor ou à raisons qu'il voulais ce passer d'Access!

d'une certaine manière c'est lui le DBA, DBA en herbe je te le concède!

il est possible à ma connaissance possible de répondre soit par un procédure stock, soit par un fonction et c'est la deuxième que j'ai choisi!

[Pierre Fauconnier]

[...]
il voudrait , je mets le conditionnel, il voudrait une Requête paramétré, bien que ça n'existe pas, dans Sql Serveur![...]

Ben si ça existe, c'est une procédure stockée, que l'on crée côté serveur (idéalement avec le Sql Managment) et que l'on utilise comme je l'illustre dans mes billets.

[Thumb down]

Ben si ça existe, c'est une procédure stockée, que l'on crée côté serveur (idéalement avec le Sql Managment) et que l'on utilise comme je l'illustre dans mes billets.

Une procédure stockée ce n'est pas une requête parrametrée.

Comme dit dans le poste précédent on peut créer une fonction parrametrée avec SQL Manager comme je l'ai préconisé !

Édite:

Code SQL MANGER :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE FUNCTION TestTM(@Debut datetime,@Fin datetime)
RETURNS TABLE
AS
RETURN 
(SELECT
P.PhysicalChuteNum as Sortie
FROM 
dbo.P
JOIN dbo.PE
ON P.Id=PE.P.Id AND 
PE.EventTypeNum IN (2,34) AND
PE.[DateTime] BETWEEN @Debut AND @Fin
)

[facteur]

Bonjour les amis
Je viens de prendre connaissance de la discussion de Pierre

Pour faire simple:
Sur un fichier Excel, j'ai deux cellules ou il y a deux dates : date Debut et date Fin (format jj/mm/aaaa hh:mm)
Et à partir de ce fichier Excel, je souhaite exécuter une requête sql server parametrée avec une date Debut et date Fin ( ces 2 variables prennent les valeurs des deux dates du fichier Excel).
Thumb m'aide à construire le code VBA.
Une vue ne permet pas cela car cette dernière ne pas pas être paramétrée . C'est pourquoi on passe par une fonction paramétrée .

Pierre, effectivement, je me suis posé la question sur la mise en place d'une procédure stockée pour réaliser ce que je souhaite faire.

j'éspère avoir été explicite et claire

[Pierre Fauconnier]

Je maintiens qu'une procédure stockée correspond bien à une requête paramétrée

Voici donc un exemple d'extraction de données bornées par deux dates et récupération dans un tableau structuré Excel

Une table SQL gérant des contacts et reprenant leur date de naissance





Une procédure stockée appelée GetContacts avec deux paramètres pour borner la plage des dates de naissance

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
USE [Folders]
GO
/****** Object:  StoredProcedure [dbo].[GetContacts]    Script Date: 21-09-20 09:49:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetContacts]
	@Date1 date,
	@Date2 date
AS
BEGIN
	SET NOCOUNT ON;
	Select
		FirstName,
		LastName,
		BirthDate
	FROM
		Contact with(NOLOCK) 
	 WHERE
		BirthDate between @Date1 and @Date2
END

Voici un exemple d'utilisation de la procédure stockée côté SQL




Côté Excel, on dispose d'un tableau structuré T_Contacts et de deux plages nommées DateDébut et DateFin




Le code suivant

• vide le tableau s'il ne l'est pas;
• se connecte au serveur SQL;
• appelle la procédure stockée en lui passant les dates souhaitées en paramètres;
• pousse les données dans le tableau structuré.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Function getConnectionString() As String
  getConnectionString = Range("ConnectionString").Value
End Function
 
Sub getContacts()
  Dim cn As ADODB.Connection
  Dim cm As ADODB.Command
  Dim rs As ADODB.Recordset
 
  Set cn = New ADODB.Connection
  If Not Range("t_Contacts").ListObject.DataBodyRange Is Nothing Then Range("t_Contacts").ListObject.DataBodyRange.Delete
  cn.Open getConnectionString
  Set cm = New ADODB.Command
  With cm
    .ActiveConnection = cn
    .CommandText = "GetContacts"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("p1", adDate, adParamInput, 4, Range("DateDébut"))
    .Parameters.Append .CreateParameter("p2", adDate, adParamInput, 4, Range("DateFin"))
  End With
  Set rs = cm.Execute()
  Range("t_Contacts").CopyFromRecordset rs
End Sub

Voici le résultat dans le tableau structuré avec les dates allant du 01/01/1960 au 31/12/1970




En modifiant les dates et en relançant le code, on obtient bien les nouvelles données.




L'avantage de la procédure stockée est qu'il limite le langage SQL à la sphère du serveur et permet à un programmeur non averti en SQL d'utiliser une procédure stockée "un peu comme une fonction" sans devoir connaître le langage SQL. Il lui suffit de connaître le nom de la procédure stockée et la façon de lui passer les paramètres éventuels. Normalement, la documentation qui accompagne la procédure stockée précise cela.



On pourrait passer le code sql en clair à la commande sql sans créer de procédure stockée, et on fonctionnerait avec +/- le même code, en modifiant le type de commande.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Sub getContacts()
  Dim cn As ADODB.Connection
  Dim cm As ADODB.Command
  Dim rs As ADODB.Recordset
 
  Set cn = New ADODB.Connection
  If Not Range("t_Contacts").ListObject.DataBodyRange Is Nothing Then Range("t_Contacts").ListObject.DataBodyRange.Delete
  cn.Open getConnectionString
  Set cm = New ADODB.Command
  With cm
    .ActiveConnection = cn
    .CommandText = "select firstname, lastname, birthdate from contact with(NOLOCK)  where birthdate between ? and ?" 
    .CommandType = adCmdText
    .Parameters.Append .CreateParameter("p1", adDate, adParamInput, 4, Range("DateDébut"))
    .Parameters.Append .CreateParameter("p2", adDate, adParamInput, 4, Range("DateFin"))
  End With
  Set rs = cm.Execute()
  Range("t_Contacts").CopyFromRecordset rs
End Sub

Dans les billets que j'ai cité plus tôt dans la discussion, j'illustre comment on peut rendre le code générique pour la gestion de la connexion et des interactions avec le sql serveur. Je stocke ce code dans un module nommé DAL (Data Access Layer) et je l'invoque en passant les chaines de commande et les paramètres. De cette façon, dès que j'ai un projet Excel qui doit être connecté à un SQL Server, j'importe mon module DAL et je suis prêt

[Thumb down]

bonjour,
je ne me bâterais sur la définition d'une procédure stocké!

il est possible de répondre à la problématique de Facteur soit par une procedure stocké ou par une fonction.

la méthode choisie ne m'importe peut!

je ne viens pas pour un combat de coq!