Procedure et boucle

**ecureuilmasque** · 15/12/2008, 15h46

Bonjour, je bloque sur une requête (c n'est pas mon fort)

J'ai une table avec un certain nombre d'adresse IP
je veux en fonction de la plage afficher (@netid que je passe par formulaire ) celles n'étant pas présente dans la table.
J'ai donc fais une procédure stocké avec une boucle mais le résultat ne convient pas (la page ne me sort que la 1ere IP 'dispo' alors que dans le manager je les listes toutes). Je dépose la requête. Merci pour vos conseils, je suis un peu à la rue.

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
 
ALTER PROCEDURE [dbo].[p_sel_ipdispo2]
 
@netid nvarchar(12)
AS
BEGIN
	SET NOCOUNT ON;
 
	DECLARE 
		@ip_test nvarchar(3), 
		@ip nvarchar(15);
 
	Set @ip_test = CONVERT(int,140);
 
	WHILE (@ip_test < 160)
	BEGIN
		Set @ip_test = @ip_test + 1
		Set @ip = @netid+CONVERT(nvarchar(3),@ip_test)
 
		SELECT DISTINCT @ip as "@ip"  from t_ip where @ip NOT IN (SELECT Adresse from t_ip)
 
	END
END

**elsuket** · 15/12/2008, 21h54

Bonjour,

la page ne me sort que la 1ere IP 'dispo' alors que dans le manager je les listes toutes

C'est normal, vous demandez que SQL Server vous retourne un ensemble de résultat pour chaque IP, alors que vous voulez obtenir un ensemble de résultat avec tous les IP possibles.
Par défaut, votre driver n'est pas configuré pour supporter MARS (Multiple Active Result Sets), donc il transmet à votre application seulement le premier.

Préférez la fonction CAST à la fonction CONVERT lorsque vous n'avez pas besoin d'appliquer un style à une donnée :

Code :

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

SET @ip_test = CONVERT(int,140);

@ip_test est de type NVARCHAR(3).
Vous transtypez 140 qui est déjà un entier en entier, et vous le stockez dans @ip_test, de type chaîne, ce que fait SQL Server automatiquement, mais qui vous pique un peu de temps d'exécution.

Il aurait fallu écrire :

Code :

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

1
2
3
SELECT @ip_test = CONVERT(CHAR(3), 140)
-- ou encore, ce qui est mieux :
SELECT @ip_test = CAST(140 AS CHAR(3))

Code :

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

SELECT DISTINCT @ip AS "@ip"

Une colonne n'est pas une valeur, c'est un ensemble de valeurs.
N'écrivez donc pas "@ip" qui est une valeur chaîne de caractères, mais plutôt [@ip].
Méfiez vous néanmoins de l'utilisation des caractères diacritiques dans les noms d'objets et de colonnes, ils ne vous apporteront que des ennuis.
Lisez la documentation à ce sujet, c'est par ici

Voici la procédure stockée que je vous propose :

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
ALTER PROCEDURE p_sel_ipdispo2
	@netID VARCHAR(12),
	@debPlage INT,
	@finPlage INT
AS
BEGIN;
	SET NOCOUNT ON
 
	-- Si le @netID ne se termine pas par '.', on l'ajoute
	IF @netID NOT LIKE '%.'
	BEGIN;
		SET @netID = @netID + '.';
	END;
 
	-- Recherche des IP dont le dernier octet est compris entre @debPlage et @finPlage
	WITH
		CTE_chercheIP AS
		(
				-- IP 192.168.200.140
				SELECT @netID NetID, @debPlage Indice
			UNION ALL
				-- IP 192.168.200.141, 192.168.200.142, ... 192.168.200.160
				SELECT @netID, Indice + 1
				FROM CTE_chercheIP
				WHERE Indice < @finPlage
		)
	-- Association de la colonne NetID avec la colonne Indice pour produire la liste des IPs
	-- Exclusion des IP dans la table dbo.T_IP
	SELECT @netID + CAST(Indice AS VARCHAR(3)) AS IP
	FROM CTE_chercheIP
	WHERE @netID + CAST(Indice AS VARCHAR(3)) NOT IN (SELECT Adresse FROM dbo.T_IP)
	OPTION (MAXRECURSION 255);
END;

Cette procédure ne fonctionnera que sous SQL Server 2005 parce qu'elle utilise une expression de table commune, récursive.
Vous pouvez passer en paramètre le netID, le début de la plage et la fin de la plage.
Vous n'obtenez qu'un seul ensemble de résultats.

Voici un test :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE T_IP
(
	Adresse VARCHAR(15)
)
GO
 
INSERT INTO dbo.T_IP VALUES ('192.168.200.140')
INSERT INTO dbo.T_IP VALUES ('192.168.200.145')
INSERT INTO dbo.T_IP VALUES ('192.168.200.150')
INSERT INTO dbo.T_IP VALUES ('192.168.200.155')
GO
 
EXEC dbo.p_sel_ipdispo2 '192.168.200', 140, 160

Et le résultat qu'elle produit :

IP
---------------
192.168.200.141
192.168.200.142
192.168.200.143
192.168.200.144
192.168.200.146
192.168.200.147
192.168.200.148
192.168.200.149
192.168.200.151
192.168.200.152
192.168.200.153
192.168.200.154
192.168.200.156
192.168.200.157
192.168.200.158
192.168.200.159
192.168.200.160
192.168.200.161

L'indicateur de requête OPTION (MAXRECURSION 255) est ici pour outrepasser la limite de 100 de récurrences autorisées sous SQL Server 2005, et la limite à 255 puisque c'est la plus grande valeur d'un octet d'adresse IPv4.

@++