Discussion :

[Pierre Fauconnier]

Dans ta procédure stockée, les paramètres obligatoires doivent être déclarés avant les paramètres optionnels. Or, @Debut et @Fin sont obligatoires car sans valeur par défaut alors que les autres sont optionnels, puisqu'ils ont une valeur par défaut. Lorsque tu invoques ta procédure stockée côté VBA en ne renseignant comme paramètres que les dates bornes de la période, tu les passes en fait aux paramètres @VACATION_STS_ACTIVE et @VACATION_STS_PAUSED (les deux premiers de la proc) qui sont de type int, d'où le message d'erreur de conversion d'un datetime en int...

Je me pose la question de ces paramètres optionnels. J'ai l'impression que ce sont en fait des variables internes à la proc (vu leurs valeurs), et pour moi, elles ne devraient pas être déclarées en paramètres, mais bien en variables.

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
CREATE PROCEDURE [dbo].[SP_PlanTri_TPGD_V2]
	-- Add the parameters for the stored procedure here
	@debut Date,
	@fin Date
AS
 
	DECLARE
		@VACATION_STS_ACTIVE     Int = 0x00000001 , -- Vacation active
		@VACATION_STS_PAUSED     Int = 0x00000002 , -- Vacation suspendue
		@VACATION_STS_STOPPING   Int = 0x00000004,  -- Vacation en cours d'arrêt
		@VACATION_STS_ARCHIVED   Int = 0x00000008,  -- Vacation archivée
 
		@EVENT_TYPE_INJECTION   Int = 0 , -- Injection
		@EVENT_TYPE_SORTING    Int = 2 , -- Tri
		@EVENT_TYPE_REJECT      Int = 34 ,-- Rejet
 
		@systemNum int ='0' -- plan de tri TPGD
BEGIN
	-- Ici, le code de ta proc
END

[Thumb down]

Ok pas de problème

[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!

[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!

[Pierre Fauconnier]

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

Je n'ai jamais dit le contraire, mais je n'avais personnellement jamais créé une fonction sql à cette fin... . Pourquoi? Parce que je trouve la procédure stockée plus simple à l'emploi côté VBA, puisqu'elle n'impose pas l'énumération des champs ou l'emploi de * et ne requiert pas l'expression des arguments dans la ligne de commande. Elle permet donc à un programmeur peu au fait du langage SQL d'utiliser de façon "simple" ce qui a été mis à sa disposition. Cela dit, la fonction permet de modifier l'ordre des champs récupérés là où la procédure stockée ne le permet pas. Ca peut être un plus dans certains cas et je note l'idée. Merci pour cet apport.

Procédure stockée (expression sql "plus simple" mais ordre des champs imposé par la procédure)

Code :

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

1
2
3
4
    .CommandText = "getContacts"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("p1", adDate, adParamInput, 4, Range("DateDébut"))
    .Parameters.Append .CreateParameter("p2", adDate, adParamInput, 4, Range("DateFin"))

Fonction Stockée (expression sql "plus complexe" avec syntaxe sql, mais possibilité de choisir l'ordre des champs)

Code :

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

1
2
3
4
    .CommandText = "select * from selectcontacts(?,?)"
    .CommandType = adCmdText
    .Parameters.Append .CreateParameter("p1", adDate, adParamInput, 4, Range("DateDébut"))
    .Parameters.Append .CreateParameter("p2", adDate, adParamInput, 4, Range("DateFin"))

Cela étant, à l'expression de la commande sql près, le code VBA pour récupérer les données et les pousser dans un tableau structuré est identique

C'est donc là une vision personnelle et juste mon avis, bien sûr. Il ne s'agit ni de combat de coq ni de vouloir imposer une manière de faire

[facteur]

Pierre
encore une quetsion
Si je reprend ton code vba
pourrais tu m'expliquer cette fonction

Code :

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

1
2
3
Function getConnectionString() As String
  getConnectionString = Range("ConnectionString").Value
End Function

Si je ne me trompe pas cette fonction appelle bien la ligne de code

Code :

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

"Provider=SQLOLEDB.1;Password=" & "tintin" & ";Persist Security Info=True;User ID=" & "tintin" & ";Initial Catalog=" & "tintin" & ";Data Source=" & "10.20.30.40" & " "

mais comment tu fais pour appeler le sql server à partir de cette fonction ???

[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.

[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