Passage d un nom de table en paramètre d'une procédure stockée

**Cedric33** · 10/02/2009, 13h12

Bonjour,
j'ai un problème que je n'arrive aps a résoudre. J'ai une procédure stockée. Il faudrait que je lui passe en paramèetre le nom de la table sur laquelle je fais le sélect.
J'ai essayé :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
CREATE PROCEDURE MaProc 
	-- Add the parameters for the stored procedure here
	@NOMTABLE varchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	select * from @NOMTABLE
END
GO

Mais je prends l'erreur La variable de table "@NOMTABLE" doit être déclarée.. Le problème est que la structure de la table à traiter n'est pas toujours la même...
Si qu'elqu'un a une idée
Merci beaucoup
Cédric

**Antoun** · 10/02/2009, 13h29

ça m'a tout l'air d'être du SQL Server...

Ta première erreur est d'oublier les parenthèses, la seconde de ne pas utiliser EXEC... Voici une version corrigée :

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE PROCEDURE dbo.MaProc (@NOMTABLE VARCHAR(50)) 
AS
DECLARE @SQL VARCHAR(255) ;
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	SET @SQL = 'SELECT * FROM ' + @NOMTABLE ;
	EXEC (@SQL) ;
END

**Cedric33** · 10/02/2009, 13h43

Merci pour ta réponse. Effectivement c'est bien du SQL Server

Ca marche mais en fait je veux aller plus loin. Je veux mettre le résultat de la requête éxécutée dans la précédure dans une variable. Voici ce que j'ai fait :

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
 
SET ANSI_NULLS ON
GO
CREATE PROCEDURE RecupeListe
	@NOM_TABLE VARCHAR(128),
	@ID int
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @MA_REQUETE VARCHAR(8000)
	declare @SAVEID int
	set @SAVEID = 0
	PRINT @ID
	  WHILE @SAVEID > -1 --@ID <> @SAVEID
	  BEGIN
			SET @MA_REQUETE = 'Select @SAVEID  =  COLID from ' + @NOM_TABLE + ' where GENE = ' + CONVERT(Varchar(50),@ID)
			PRINT @MA_REQUETE
			EXEC (@MA_REQUETE)
			  IF @ID = @SAVEID
					  BREAK;
			  else
				set @SAVEID = @ID
			PRINT @ID
		END
END
 
GO

Et en l'éxécutant :

Code :

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

1
2
 
EXEC RecupeListe @NOM_TABLE = 'LIEU', @ID = 3

j'ai le message suivante

Select @SAVEID = COLID from LIEU where GENE = 3
Msg*137, Niveau*15, État*1, Ligne*1
La variable scalaire "@SAVEID" doit être déclarée.

Select @SAVEID = COLID from LIEU where GENE = 3
Msg*137, Niveau*15, État*1, Ligne*1
La variable scalaire "@SAVEID" doit être déclarée.

Désolé je ne suis pas un expert des procédures stockées

**Antoun** · 10/02/2009, 13h52

Quand tu exécutes une chaîne SQL avec EXEC, elle l'est dans un contexte différent, et du coup elle ne connaît pas les variables déclarées dans ta proc stock, et réciproquement.

Peux-tu m'expliquer ton jeu sur @ID et @SAVE_ID ?

**Cedric33** · 10/02/2009, 14h34

Pardon mon code est faux. En fait je veux récupérer la hiérarchie d'un lieu.
Dans l'idée c'est ça :

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
 
SET ANSI_NULLS ON
GO
CREATE PROCEDURE RecupeListe
	@NOM_TABLE VARCHAR(128),
	@ID int
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @MA_REQUETE VARCHAR(8000)
	PRINT @ID
	  WHILE @ID != NULL
	  BEGIN
			SET @MA_REQUETE = 'Select @ID  =  COLID from ' + @NOM_TABLE + ' where GENE = ' + CONVERT(Varchar(50),@ID)
			PRINT @MA_REQUETE
			EXEC (@MA_REQUETE)
			  IF @ID = NULL
					  BREAK;
			PRINT @ID
		END
END
 
GO

Mais d'une part @ID != NULL me renvoie faux puisque je ne rentre pas dans la boucle, et d'autre part je n'arrive pas a affecter le résultat du SELECT dans la variable @ID
Je sais pas si je suis tres clair...

**Antoun** · 10/02/2009, 14h54

D'une part, NULL ne marche jamais avec =, ni avec !=. D'autre part le contexte d'exécution de @MA_REQUETE n'est pas le même que celui de ta procédure, donc tu ne peux effectivement pas affecter. Enfin, pourquoi ne pas faire simplement ceci ?

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE PROCEDURE RecupeListe
	@NOM_TABLE VARCHAR(128),
	@ID int
AS
DECLARE @MA_REQUETE VARCHAR(8000)
BEGIN
	SET NOCOUNT ON;
	SET @MA_REQUETE = 'Select COLID from ' + @NOM_TABLE + ' where GENE = ' + CONVERT(Varchar(50),@ID)
	EXEC (@MA_REQUETE)
END

Ça te renvoie l'ID que tu cherches si elle est trouvée, un empty set sinon, il me semble que cela correspond à ce que tu attends avec ton IF @ID = NULL.

**elsuket** · 10/02/2009, 14h59

Bonjour,

Que se passera-t-il si votre requête SELECT dynamique retourne plusieurs lignes ?
La variable @ID contiendra la valeur de la colonne de la 1e ligne de l'ensemble de résultats retourné par votre requête.
Donc ce que vous avez écrit est faux

Ce que vous pouvez faire :

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
CREATE PROCEDURE RecupeListe
	@NOM_TABLE VARCHAR(128),
	@ID int
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @MA_REQUETE VARCHAR(8000)
	PRINT @ID
	WHILE @ID != NULL
	BEGIN
		SET @MA_REQUETE = 'Select COLID from ' + @NOM_TABLE + ' where GENE = ' + CONVERT(VARCHAR(50),@ID)
		PRINT @MA_REQUETE
 
		DECLARE @TbID TABLE
		(
			ID INT
		)
 
		-- Récupère le résultat de la requête dans @TbID
		INSERT INTO @TbID
		EXEC (@MA_REQUETE)
 
		-- Si la colonne COLID de la table @NOM_TABLE contient des NULL
		IF EXISTS
		(
			SELECT *
			FROM @TbID
			WHERE ID IS NULL
		)
		BREAK;
 
		-- Liste les valeurs contenues dans la table @TbID (<=> COLID de la table @NOM_TABLE)
		DECLARE @szMsg VARCHAR(256)
		SELECT @szMsg = ISNULL(@szMsg, '') + CAST(ISNULL(ID, '<NULL>') AS VARCHAR) + ','
		FROM @TbID
 
		PRINT @szMsg
	END
END

Mais je ne vois pas l'intérêt de ce code.
Que cherchez vous à faire exactement ?

@++

**elsuket** · 10/02/2009, 15h01

Envoyé par Antoun

NULL ne marche jamais avec =, ni avec !=

Pour compléter cette réponse : NULL ne marche avec rien du tout parce que ce n'est pas une valeur : c'est l'absence de valeur !

@++

**Cedric33** · 10/02/2009, 15h09

Tu as raison Antoun. Je me complique la vie un peu trop des fois moi. Du coup avec cette solution ce qu'il faut que je fasse c'est créer une autre procédure stockée (qui appelle celle la) pour faire le WHILE.

Elsuket je vais tester ton code. En fait je recherche a faire ceci :
L'utilisateur crée un LIEU1. Il veut le hiérarchiser, donc pour etre sur que la hiérarchie soit un minimum cohérente, je veux supprimer de la liste les Lieux qui se trouvent sous LIEU1 dans la hiérarchie.
Par exemple : J'ai l'a hiérarchie suivante :
Monde --> Europe --> France --> Hérault
Si je veux hiérarchiser Monde alors je dois supprimer de la liste les lieux proposée Europe, France, Hérault et meme Monde.

**Antoun** · 10/02/2009, 15h13

Pour une solution élégante aux questions d'arborescence (mais qui suppose de changer la modélisation) : http://sqlpro.developpez.com/cours/arborescence/

**Antoun** · 10/02/2009, 15h14

Envoyé par elsuket

Pour compléter cette réponse : NULL ne marche avec rien du tout parce que ce n'est pas une valeur : c'est l'absence de valeur !

... et il faut donc utiliser IS NULL et IS NOT NULL, ainsi que nous avons tous deux omis de préciser...

**Antoun** · 10/02/2009, 15h22

Envoyé par Cedric33

Elsuket je vais tester ton code. En fait je recherche a faire ceci :
L'utilisateur crée un LIEU1. Il veut le hiérarchiser, donc pour etre sur que la hiérarchie soit un minimum cohérente, je veux supprimer de la liste les Lieux qui se trouvent sous LIEU1 dans la hiérarchie.
Par exemple : J'ai l'a hiérarchie suivante :
Monde --> Europe --> France --> Hérault
Si je veux hiérarchiser Monde alors je dois supprimer de la liste les lieux proposée Europe, France, Hérault et meme Monde.

Je ne voyais pas trop à quoi servait ton WHILE, mais je comprends un peu mieux maintenant...

L'idéal est la représentation intervallaire, décrite dans l'article de SQLpro, puisqu'elle te permet de supprimer toute ta branche en une seule requête.

Néanmoins, si tu ne peux pas ou ne veux pas changer de modélisation, je te propose le fonctionnement suivant :

je suppose que tu as une contrainte FOREIGN KEY entre ton ID et ton ID parente ; tu peux y ajouter une clause ON DELETE SET NULL
Tu supprimes Europe, du coup l'ID du parent de France devient NULL
Tu supprimes tous les membres dont les ID parentes sont NULL ; maintenant, c'est l'ID parente de l'Hérault qui passe à NULL
Tu repasses ton DELETE jusqu'à ce qu'il ne trouve plus rien à supprimer (@@ROWCOUNT = 0)

Comme tu vois, ça simplifie largement ta procédure stockée.

**Cedric33** · 10/02/2009, 15h28

je ne comprends pas tres bien ta solution; Je ne veux pas supprimer les termes de la table

**Antoun** · 10/02/2009, 15h29

Alors je n'ai rien compris à ça :

J'ai l'a hiérarchie suivante :
Monde --> Europe --> France --> Hérault
Si je veux hiérarchiser Monde alors je dois supprimer de la liste les lieux proposée Europe, France, Hérault et meme Monde.

**Cedric33** · 10/02/2009, 15h30

bien je veux supprimer ces termes de la liste proposée à l'utilisateur. Mais je ne les supprimes pas de la table

**Antoun** · 10/02/2009, 15h33

Envoyé par Cedric33

bien je veux supprimer ces termes de la liste proposée à l'utilisateur. Mais je ne les supprimes pas de la table

OK... comment se présente ta liste (ou tes listes) ?

**Cedric33** · 10/02/2009, 15h46

Ma liste est une combobox remplie a partir de la table LIEU de la base de données. Elle contient tous les termes sauf ceux qui font partie de la hiérarchie du terme traité. Par exemple :
Dans ma table LIEU j'ai : Monde, Europe, France, Espagne, Iatlie, Paris, Hérault, Alsace, Strasbourg
Une seule hiérarchie est déjà faite :
Monde --> Europe --> France --> Alsace --> Strasbourg
L'utilisateur veut changer terme générique à Alsace. La liste qui lui sera proposée sera donc :
Monde, Europe, Espagne, Iatlie, Paris, Hérault, France.
Alsace ne fera pas partie de la liste car on ne peut pas hiérarchiser un terme sous lui meme. Et Strasbourg n'en fera pas partie car on ne peut pas avoir Strasbourg sous Alsace et Alsace sous Strasbourg