Problème variable dans requête SELECT

**mary27** · 10/06/2009, 09h40

Bonjour à tous,
Je suis sous SQL server.

J'aimerais récupérer le nom de la table dans laquelle je suis en faisant:

Code :

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

1
2
3
4
SET @SQL3 = 'SELECT @NAME_TABLE =name FROM ' + @NAME_BASE + '.sys.tables WHERE object_id=@ID_OBJECT '
PRINT @SQL3
EXEC(@SQL3)
PRINT @NAME_TABLE

pour pouvoir reconstruire avec la fonction suivante:

Code :

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

1
2
 Set @Requete = 'DBCC DBREINDEX ('+@NAME_TABLE+', '+@NAME_INDEX+',80)'
Exec (@Requete)

Il me met l'erreur suivante:
Must declare the scalar variable "@NAME_TABLE"

Pourtant elle est déclarée en tant que VARCHAR(255)

Pouvez-vous m'en dire plus?

**SQLpro** · 10/06/2009, 10h27

ENCORE UNE FOIS MONTREZ TOUT VOTRE CODE

A +

**mary27** · 10/06/2009, 10h51

Voici mon code:

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
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
DECLARE @NAME_BASE VARCHAR(255)
DECLARE @NAME_TABLE VARCHAR(255)
DECLARE @NAME_INDEX VARCHAR(255)
DECLARE @SQL1 NVARCHAR(1000)
DECLARE @SQL2 NVARCHAR(1000)
DECLARE @SQL3 NVARCHAR(1000)
DECLARE @ID_BDD INT
DECLARE @ID_OBJECT INT
DECLARE @ID_INDEX INT
DECLARE @FRAG FLOAT
DECLARE @NOM_TABLE VARCHAR(500)
CREATE TABLE TEMPTABLE (
  ID INT,
  NAME VARCHAR(500),
  INDEXID INT,
  FRAG FLOAT,
  NOM_TABLE VARCHAR(500)
)


DECLARE @PARCOURS_BASE AS CURSOR
SET @PARCOURS_BASE=CURSOR FOR SELECT name, database_id FROM sys.databases where NAME NOT IN ('master','model','msdb','tempdb') 
OPEN @PARCOURS_BASE

/* on se place au premier enregistrement*/
FETCH @PARCOURS_BASE INTO @NAME_BASE, @ID_BDD

/*on parcourt les bases*/
WHILE (@@FETCH_STATUS=0)
BEGIN
  PRINT 'base:'
  PRINT @NAME_BASE
  
  SET @SQL1 = 'INSERT INTO TEMPTABLE (ID,NAME,INDEXID) SELECT object_id,name,index_id FROM ' + @NAME_BASE + '.sys.indexes WHERE name not like ''PK_%'' AND name not like ''clust'' AND name not like ''clst'' AND name not like ''nc%'' AND name not like ''queue_secondary_index'' AND name not like ''docid'' AND name not like ''queue_clustered_index'' AND name not like ''cl'' AND name not like''i%'' '
  PRINT @SQL1
  EXECUTE(@SQL1)
  
  
  
  DECLARE @PARCOURS_TABLE AS CURSOR
  SET @PARCOURS_TABLE=CURSOR FOR SELECT ID,NAME,INDEXID FROM TEMPTABLE FOR UPDATE
  OPEN @PARCOURS_TABLE 
  FETCH @PARCOURS_TABLE INTO @ID_OBJECT, @NAME_INDEX, @ID_INDEX

  WHILE (@@FETCH_STATUS=0)
  BEGIN
   
 
    PRINT @ID_BDD
    PRINT @ID_OBJECT
    PRINT @ID_INDEX
    SET @SQL2 = 'UPDATE TEMPTABLE SET FRAG=(SELECT AVG(avg_fragmentation_in_percent) FROM ' + @NAME_BASE + '.sys.dm_db_index_physical_stats(' + CAST(@ID_BDD AS VARCHAR(10)) + ',' + CAST(@ID_OBJECT AS VARCHAR(10)) + ',' + CAST(@ID_INDEX AS VARCHAR(10)) + ',NULL,NULL)) WHERE ID='+ CAST(@ID_OBJECT AS VARCHAR(10)) + ' AND NAME=''' + @NAME_INDEX + ''' AND INDEXID=' + CAST(@ID_INDEX AS VARCHAR(10))
    PRINT @SQL2
    EXEC(@SQl2)
    SELECT 
      @FRAG=FRAG
    FROM 
      TEMPTABLE
    WHERE
      ID=@ID_OBJECT AND 
      NAME = @NAME_INDEX AND 
      INDEXID = @ID_INDEX
     
    print @NAME_INDEX
    print ('frag:')
    print @FRAG
	
	SET @SQL3 = 'UPDATE TEMPTABLE  SET NOM_TABLE=(SELECT name FROM ' + @NAME_BASE + '.sys.tables WHERE ID='+ CAST(@ID_OBJECT AS VARCHAR(10)) + ')'						
	
    PRINT @SQL3
    EXEC(@SQL3)
/*récupération nom de la table pour reconstruction DBCC DBREINDEX 
    SET @SQL3 = 'SELECT NAME_TABLE =name FROM ' + @NAME_BASE + '.sys.tables WHERE object_id=@ID_OBJECT '   PRINT @SQL3
    EXEC(@SQL3)
    */
           
    SELECT 
      @NOM_TABLE=NOM_TABLE
    FROM 
      TEMPTABLE
    WHERE
      ID=@ID_OBJECT AND 
      NAME = @NAME_INDEX AND 
      INDEXID = @ID_INDEX
      
    PRINT ('nom de la table:')
    PRINT @NOM_TABLE
    
         /*IF @FRAG >=30.0
       BEGIN
         Set @Requete = 'DBCC DBREINDEX ('+@NAME_TABLE+', '+@NAME_INDEX+',80)'
         Exec (@Requete)
         print ('reconstruction')
       END*/  

      
    FETCH @PARCOURS_TABLE INTO @ID_OBJECT, @NAME_INDEX, @ID_INDEX
  END
  CLOSE @PARCOURS_TABLE
  DEALLOCATE @PARCOURS_TABLE
  /*on change de ligne de base*/
  DELETE FROM TEMPTABLE
  FETCH @PARCOURS_BASE INTO @NAME_BASE, @ID_BDD
END   
CLOSE @PARCOURS_BASE
DEALLOCATE @PARCOURS_BASE    
DROP TABLE TEMPTABLE

le but étant de reconstruire les index de tte les tables de toutes les bases
J'ai finalement changé de méthode, en utilisant une table temporaire.
L'erreur suivante se marque au niveau de la ligne en gras:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

**mary27** · 10/06/2009, 11h21

Please, help me.
Pourtant quand j'execute la requête:

Code :

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

SELECT  name FROM nom_base.sys.tables WHERE object_id=501576825

Il y a qu'un seul nom qui est selectionné

**Ptit_Dje** · 10/06/2009, 13h29

Bonjour,

L'erreur provient du SQL dynamique que vous générez.
En effet la variable @NAME_TABLE est bien déclarée dans votre code.
Cependant, lorsque vous effectuez:

Code :

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

1
2
3
4
 
SET @SQL3 = 'SELECT @NAME_TABLE =name FROM ' + @NAME_BASE + '.sys.tables WHERE object_id=@ID_OBJECT '
PRINT @SQL3
EXEC(@SQL3)

Vous réutilisez la variable @NAME_TABLE, qui n'est pas connue au moment de l'execution de EXEC(@SQL3).

Essayez quelque chose dans le style (pas testé... je vous guide pour la logique):

Code :

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

1
2
 
SET @table_name = exec (@sql3)

Tout comme vous allez devoir aussi sortir le "=@ID_OBJECT"

En espérant que cela vous serve,

Bonne journée,
Dje

**mary27** · 10/06/2009, 13h55

Merci Dje pour ces informations.

J'ai essayé de faire comme expliqué mais il m'indique une erreur de syntaxe:

Code :

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

1
2
3
SET @SQL3 = 'SELECT name FROM ' + @NAME_BASE + '.sys.tables WHERE object_id=@ID_OBJECT '
    PRINT @SQL3
    SET @NAME_TABLE2 = EXEC (@SQL3)

Incorrect syntax near the keyword 'EXEC'. :/

**Ptit_Dje** · 10/06/2009, 13h59

Comme expliqué, je vous ai donné une piste de réflexion, je n'ai pas testé.
Je suis convaincu que vous trouverez la solution en cherchant un peu par vous même (c'est aussi ainsi que l'on se forge de l'expérience

).

**mary27** · 10/06/2009, 14h32

C'est noté merci de votre réponse.
Ca fait déjà 2 jours que je cherche désespérément...

Que pensez-vous de mon code complet un peu plus haut et notamment de l'erreur qui est générée ??

Je continue mes recherches

**mary27** · 10/06/2009, 15h12

J'ai finalement trouvé une solution en reprenant la version

Code :

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

1
2
3
SET @SQL3 = 'SELECT name FROM ' + @NAME_BASE + '.sys.tables WHERE object_id=@ID_OBJECT '
    PRINT @SQL3
    SET @NAME_TABLE2 = EXEC (@SQL3)

Voici le bon code final:

Code :

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

1
2
3
4
5
6
7
DECLARE @SQLVARS NVARCHAR(500)
    DECLARE @table NVARCHAR (500)
    SET @SQL3 = 'SELECT @NAME_TABLE2 =name FROM ' + @NAME_BASE + '.sys.tables WHERE object_id='+ CAST(@ID_OBJECT AS VARCHAR(10))+ ''
	SET @SQLVARS ='@NAME_TABLE2 VARCHAR(500) OUTPUT, @table sysname'
	EXEC sp_executesql @SQL3, @SQLVARS, @NAME_TABLE2 OUTPUT, @table 
	print('nom de la table:')
	PRINT @NAME_TABLE2

Merci de votre aide

**Ptit_Dje** · 15/06/2009, 18h43

Bien joué !

Problème variable dans requête SELECT

MS SQL Server

Discussions similaires

Partager

Partager