faire passer une collection de uniqueidentifier en paramètre dans une PS

**thor76160** · 06/09/2010, 12h22

Bonjour,
j'ai une procédure stockée, et j'y fais passer en paramètre un uniqueidentifier. seulement j'aimerais y faire passer toute une liste, est-ce possible ?

je vous montre :

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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
CREATE FUNCTION get_distance_metres(@latitudeDepart FlOAT, @longitudeDepart FLOAT, @latitudeArrivee FLOAT, @longitudeArrivee FLOAT)
RETURNS FLOAT
AS
BEGIN
	DECLARE @radLongitudeDepart FLOAT, @radLatitudeDepart FLOAT, @radLongitudeArrivee FLOAT, @radLatitudeArrivee FLOAT, @dlo FLOAT, @dla FLOAT, @a FLOAT
 
	SET @radLongitudeDepart = RADIANS(@longitudeDepart)
    SET @radLatitudeDepart = RADIANS(@latitudeDepart)
    SET @radLongitudeArrivee = RADIANS(@longitudeArrivee)
    SET @radLatitudeArrivee = RADIANS(@latitudeArrivee)
 
	SET @dlo = (@radLongitudeArrivee - @radLongitudeDepart) / 2
    SET @dla = (@radLatitudeArrivee - @radLatitudeDepart) / 2
 
    SET @a = SIN(@dla) * SIN(@dla) + COS(@radLatitudeDepart) * COS(@radLatitudeArrivee) * SIN(@dlo) * SIN(@dlo)
    RETURN (6378137 * 2 * ATN2(SQRT(@a), SQRT(1 - @a)))
END
GO
 
CREATE PROCEDURE SearchInZone
@id_region uniqueidentifier, @id_departement uniqueidentifier, @id_chef_lieu_departemental uniqueidentifier, @id_canton uniqueidentifier, @id_ville_depart uniqueidentifier, @rayon INT
AS
	/* nous partons d'un point de départ, le centre de la zone de recherche */
	DECLARE @latitude_depart FLOAT, @longitude_depart FLOAT
 
	SELECT
		@latitude_depart = latitude,
		@longitude_depart = longitude
	FROM
		Ville
	WHERE
		id_ville = @id_ville_depart
 
	/* variable de construction de la requete */
	DECLARE @requete VARCHAR(1000), @req_GDM VARCHAR(100),  @i INT
 
	SET @req_GDM = 'dbo.get_distance_metres(' + CONVERT(VARCHAR, @latitude_depart) + ', ' + CONVERT(VARCHAR, @longitude_depart) + ', latitude, longitude)'
	SET @requete = 'SELECT id_ville, nom_ville, arrondissement_ville, ROUND(' + @req_GDM + ' / 1000 , 2) AS distance
					FROM Ville V '
	SET @i = 0
 
	/* construction de la requete */
	IF @id_canton IS NOT NULL
		BEGIN
			SET @i = @i + 1
			SET @requete = @requete + 'WHERE V.id_canton = ''' + CONVERT(CHAR(36), @id_canton) + ''''
		END
	ELSE
		BEGIN
			IF @id_chef_lieu_departemental IS NOT NULL
				BEGIN
					SET @i = @i + 1
					SET @requete = @requete + 'WHERE V.id_chef_lieu_departemental = ''' + CONVERT(CHAR(36), @id_chef_lieu_departemental) + ''''
				END
			ELSE
				BEGIN
					IF @id_departement IS NOT NULL
						BEGIN
							SET @i = @i + 1
							SET @requete = @requete + 'WHERE V.id_departement = ''' + CONVERT(CHAR(36), @id_departement) + ''''
						END
					ELSE
						BEGIN
							IF @id_region IS NOT NULL
							BEGIN
								SET @i = @i + 1
								SET @requete = @requete + 'INNER JOIN Departement D ON V.id_departement = D.id_departement
												WHERE D.id_region = ''' + CONVERT(CHAR(36), @id_region) + ''''
							END
						END
				END
		END
 
	/* intégration du périmètre de recherche */
	IF @i = 0
		BEGIN
			SET @requete = @requete + ' WHERE '
		END
	ELSE
		BEGIN
			SET @requete = @requete + ' AND '
		END
 
	SET @requete = @requete + @req_GDM + ' < ' + CONVERT(VARCHAR, @rayon)
 
	/* order by */
	SET @requete = @requete + ' ORDER BY distance ASC'
 
	/* exécution de la reqiête */
	EXEC(@requete)
GO

j'aimerais faire passer plusieurs villes ou canton en paramètre. avez-vous une solution à me proposer svp ? merci

**SQLpro** · 06/09/2010, 14h58

Voulez-vous une solution, pratique simple et peu performante ou une solution performante mais plus complexe à mettre en œuvre ?

A +

**thor76160** · 06/09/2010, 15h47

Envoyé par SQLpro

Voulez-vous une solution, pratique simple et peu performante ou une solution performante mais plus complexe à mettre en œuvre ?

A +

j'opte pour la performance ! je ne veux pas d'une solution qui sera tellement lourde que mon SGBD en sera ralenti ! (car j'appelle ça du gachi ^^)

j'ai d'ailleurs pensé à une solution :

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
CREATE TABLE #lstRegions (
	id_region	uniqueidentifier
)
go
 
CREATE TABLE #lstDepartements (
	id_departement	uniqueidentifier
)
go
 
CREATE TABLE #lstChefs_lieu_departemental (
	id_chef_lieu_departemental	uniqueidentifier
)
go
 
CREATE TABLE #lstCantons (
	id_canton	uniqueidentifier
)
go
 
INSERT INTO #lstRegions VALUES
('{74836C6C-0FE5-4C8C-90FB-70BA4D73F505}'),
('{50B205A4-F2C4-4CBD-A0B2-1B2C8234608E}')
go
 
INSERT INTO #lstDepartements VALUES
('{2D471A5C-2F55-4A6D-91EC-B267913B1CE0}'),
('{F64C2CE6-0B6E-4AF0-B952-6BB929D7D909}'),
('{165A177A-36DE-4F49-8058-1D9E3B2E8CD5}')
go
 
INSERT INTO #lstChefs_lieu_departemental VALUES
('{28482FDF-E4E1-43FC-A83C-FF1F2A2CDC4D}'),
('{A517EE39-A1C0-4263-BC63-458E01A10BE1}'),
('{122ECC87-9589-47D0-90E2-E7E81CC04322}'),
('{660695DE-8206-49C3-B806-5D5E8CABE9CD}')
go
 
INSERT INTO #lstCantons VALUES
('{29A9C3D4-9951-4FDF-BCF5-23CC2D8C12E1}'),
('{0393C085-0449-4687-8F0D-9807937876F7}'),
('{314587BB-3750-41FF-B711-7A122CC432CD}'),
('{ACC49B3A-41E7-4A4D-857A-C8A9932B6A0F}'),
('{2DC22471-C8AA-4798-B49B-92638A38CCB4}'),
('{AF434B69-9E4A-4300-8CAB-1DB62EC2DB22}'),
('{6449874E-7EE0-45C5-AAB8-65A907B529D6}')
go

j'ai créé des tables de collection, leur contenu n'est que temporaire !
quand je serais dans mon projet (notamment c#), je remplirais mes tables avec les infos dont j'ai besoin. voila le nouveau code de ma Procédure stockée :

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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
CREATE PROCEDURE SearchInZone
@region BIT, @departement BIT, @chef_lieu_departemental BIT, @canton BIT, @id_ville_depart uniqueidentifier, @rayon INT
AS
	/* nous partons d'un point de départ, le centre de la zone de recherche */
	DECLARE @latitude_depart FLOAT, @longitude_depart FLOAT
 
	SELECT
		@latitude_depart = latitude,
		@longitude_depart = longitude
	FROM
		Ville
	WHERE
		id_ville = @id_ville_depart
 
	/* variable de construction de la requete */
	DECLARE @requete VARCHAR(1000), @req_GDM VARCHAR(100),  @i INT
 
	SET @req_GDM = 'dbo.get_distance_metres(' + CONVERT(VARCHAR, @latitude_depart) + ', ' + CONVERT(VARCHAR, @longitude_depart) + ', latitude, longitude)'
	SET @requete = 'SELECT id_ville, nom_ville, arrondissement_ville, ROUND(' + @req_GDM + ' / 1000 , 2) AS distance
					FROM Ville V '
	SET @i = 0
 
	/* construction de la requete */
	IF @canton = 1
		BEGIN
			SET @i = @i + 1
			SET @requete = @requete + 'WHERE V.id_canton IN (SELECT id_canton FROM #lstCantons)'
		END
	ELSE
		BEGIN
			IF @chef_lieu_departemental = 1
				BEGIN
					SET @i = @i + 1
					SET @requete = @requete + 'WHERE V.id_chef_lieu_departemental IN (SELECT id_chef_lieu_departemental FROM #lstChef_lieu_departemental)'
				END
			ELSE
				BEGIN
					IF @departement = 1
						BEGIN
							SET @i = @i + 1
							SET @requete = @requete + 'WHERE V.id_departement (SELECT id_departement FROM #lstDepartements)'
						END
					ELSE
						BEGIN
							IF @region = 1
							BEGIN
								SET @i = @i + 1
								SET @requete = @requete + 'INNER JOIN Departement D ON V.id_departement = D.id_departement
												WHERE D.id_region IN (SELECT id_region FROM #lstRegions)'
							END
						END
				END
		END
 
	/* intégration du périmètre de recherche */
	IF @i = 0
		BEGIN
			SET @requete = @requete + ' WHERE '
		END
	ELSE
		BEGIN
			SET @requete = @requete + ' AND '
		END
 
	SET @requete = @requete + @req_GDM + ' < ' + CONVERT(VARCHAR, @rayon)
 
	/* order by */
	SET @requete = @requete + ' ORDER BY distance ASC'
 
	/* exécution de la reqiête */
	EXEC(@requete)
 
	/* suppression des tables temporaires */
	DELETE FROM #lstRegions
	GO
 
	DELETE FROM #lstDepartements
	GO
 
	DELETE FROM #lstChefs_lieu_departemental
	GO
 
	DELETE FROM #lstCantons
	GO
GO

y a t'il encore mieux comme solution ?

appel de procédure :

Code :

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

EXEC SearchInZone 0, 0, 1, 1, '{25EEF587-6101-4327-A3E2-1B7EFE34E48B}', 15000

résultats :

C76C963A-FBFD-4B1B-9589-FC02D069D259 Le Petit-Quevilly NULL 9,07
82688ACB-3832-4363-8B91-26429BF22B6E Tourville-la-Rivière NULL 11,7
FE7B3BD8-6395-4509-AFFF-0361ABE7C655 Sotteville-sous-le-Val NULL 12,05
DC7F5A1A-1CA8-4014-A5BB-50FAFF597608 Freneuse NULL 13,98

**SQLpro** · 06/09/2010, 16h08

Créez dans votre base une table de nom T_LISTE_DATA_LSD avec les colonnes suivantes :

Code :

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

1
2
3
4
5
6
7
8
CREATE TABLE dbo.T_LISTE_DATA_LSD
(LSD_ID          BIGINT IDENTITY PRIMARY KEY,
 LSD_TRAN_GUID   UNIQUEIDENTIFIER NOT NULL UNIQUE,
 LSD_DATA        VARCHAR(256) NOT NULL);
GO
 
CREATE INDEX X ON dbo.T_LISTE_DATA_LSD (LSD_TRAN_GUID, LSD_DATA);
GO

Dans votre code client, pour chaque liste de données à utiliser dans une requête, commencer par générer un GUID que vous utiliserez à titre de jeton transactionnel.
Insérez votre liste de données dans la colonne LSD_DATA avec toujours le même jeton transactionnel (GUID).
Dans votre requête remplacez la liste par une sous requête faisant appel à la table ainsi créée filtrée sur le jeton transactionnel.
Supprimez les lignes relative à ce jeton transactionnel après usage.

Exemple - soit faire une requête incorporant la liste 'Paris', 'Lyon', 'Marseille' :

Code :

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

1
2
3
4
5
6
DECLARE @JETON UNIQUEIDENTIFIER;
 
SET @JETON = NEWID();
 
INSERT INTO dbo.T_LISTE_DATA_LSD
VALUES (@JETON , 'Paris'), (@JETON , 'Lyon'), (@JETON , 'Marseille');

Dès lors récrivez votre PS comme suit :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT id_ville, nom_ville, arrondissement_ville, 
       ROUND(dbo.get_distance_metres(@latitude_depart, @longitude_depart,
             V.latitude, V.longitude) / 1000 , 2) AS distance
FROM   Ville V 
WHERE  id IN (SELECT id 
              FROM   MatableGeo AS G
                     INNER JOIN dbo.T_LISTE_DATA_LSD AS L
                           ON G.nom_geo = L.LSD_DATA)
  AND  dbo.get_distance_metres(@latitude_depart, @longitude_depart,
                               V.latitude,       V.longitude) <= @rayon
ORDER BY distance  ;

A +

**WOLO Laurent** · 07/09/2010, 09h59

Quelle version de SQL Serveur ?

**thor76160** · 09/09/2010, 10h38

j'ai sqlserver 2008 pourquoi ?

faire passer une collection de uniqueidentifier en paramètre dans une PS

MS SQL Server

Vue hybride

Discussions similaires

Partager

Partager