Procedure ou fonction qui echange les lignes d'une table

Version imprimable

Bonjour je suis un peu bloqué quelqu'un aurait une idée de syntaxe pour une fonction qui échange les lignes d'une table quelconque???

avant

nom salaire
julien 454545
henry 121212
cedrick 1212121
franck 44521321
louis 445454
didier 1454545
nico 8778987

de telle facon qu'on ait julien a la place de nico.on echange les lignes i et j de la table de taille n

nom salaire

nico 8778987
henry 121212
cedrick 1212121
franck 44521321
louis 445454
didier 1454545
julien 454545

bien le prototype sql dynamique serait

Code:

create procedure(@nom_table varchar(200),@ligne_i,@ligne_j)............

de plus pour numéroter les lignes jai penser à row number qui donne
Code:

1 2 3 4 5 6 7 8 CREATE Procedure Donne_row_id(@NOM_TABLE VARCHAR(200)) as declare @req as Varchar(8000) --variable de stockage set @req='select *, ROW_NUMBER() OVER (order by (select 1)) as RowNumber from ' + @NOM_TABLE --on cree la colonne row number numerote les ligne de la table suivant les exec(@req)
nom salaire rowNumber

nico 8778987 1
henry 121212 2
cedrick 1212121 3
franck 44521321 4
louis 445454 5
didier 1454545 6
julien 454545 7

1 - Création de la table exemple Table01

Code:

1
2
3
4
CREATE TABLE Table01 
(Nom VARCHAR(50), 
 Salaire INT  
);

2 -Peuplement de la table exemple Table01

Code:

1
2
3
4
5
6
7
8
9
10
INSERT INTO TABLE01
(Nom, salaire) 
 VALUES 
('Julien', 454545), 
('Henry', 121212), 
('Cedrick', 1212121), 
('Franck', 44521321), 
('Louis', 445454), 
('Didier', 1454545), 
('Nico', 8778987);

3 - Vérification du contenu initial de la table exemple Table01, avant permutation des lignes

Code:

1
2
SELECT  Nom, Salaire, ROW_NUMBER() OVER  (ORDER BY (SELECT 1)) AS RowNumber
FROM TABLE01

Code:

1
2
3
4
5
6
7
8
9
Résultat : 
Nom      Salaire RowNumber
Julien   454545      1
Henry    121212      2
Cedrick  1212121     3
Franck   44521321    4
Louis    445454      5
Didier   1454545     6
Nico     8778987     7

4 - On veut par exemple permuter les 2 lignes : ligne I=2 (Henry) avec la ligne J=5 (Louis).
Dans notre exemple : I=2 et J=5

5 - Requête CTE permettant de permuter les 2 lignes I=2 et J=5

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
WITH
  T (Nom, Salaire,  RowNumber)
  AS
  ( SELECT Nom, Salaire, ROW_NUMBER() OVER  (ORDER BY (SELECT 1))  AS RowNumber  
	FROM TABLE01    
  )
SELECT T.Nom, T.Salaire, T.RowNumber 
FROM T  
WHERE T.RowNumber NOT IN (2, 5)     -- (I, J) = (2, 5) 
UNION ALL
SELECT T.Nom, T.Salaire, 2   -- I = 2 
FROM T
WHERE T.RowNumber = 5        -- J = 5 
UNION ALL
SELECT T.Nom, T.Salaire, 5   -- J = 5 
FROM T
WHERE T.RowNumber = 2        -- I = 2 
ORDER BY RowNumber

6 - Résultat après permutation des 2 lignes (I=2 et J=5) :

Code:

1
2
3
4
5
6
7
8
Nom      Salaire    RowNumber
Julien   454545     1
Louis    445454     2  -- ** ligne permutée. avant il y avait Henry à la position 2
Cedrick  1212121    3
Franck   44521321   4
Henry    121212     5  -- ** Ligne permutée. avant il y avait Louis à la position 5
Didier   1454545    6
Nico     8778987    7

Pour ce qui est du SQL Dynamique, je te laisse le le soin de transformer la CTE en SQL Dynamique et de l'intégrer dans ta procédure finale.

A+

26/07/2011, 22h51
hmira

Attention, il y a une "faille" dans le raisonnement, ou plutôt dans l'exposé du problème concernant l'ordre initial des lignes.
Sous SQL Server, dans une table, il n'y a aucun ordre naturel des lignes, Pour ordonner les lignes avec certitude, le seul moyen est de rajouter une clause ORDER BY explicite. Sans cela l'ordre des lignes est aléatoire suite à un SELECT.

Donc entre les 2 étapes :
L'étape 3 “Vérification du contenu initial de la table exemple Table01 “
et
L'étape 5 "Requête CTE permettant de permuter les 2 lignes I=2 et J=5" ,

L'ordre initial supposé fixe, et réutilisé ensuite dans la CTE, peut potentiellement changer entretemps. Et la permutation ne fonctionnera pas, ou plutôt, elle sera appliquée à d'autres lignes différentes des lignes I et J que l'on croyait permuter !!!

CONCLUSION :
Il faut trouver autre chose, plus déterministe, pour classer les lignes. il ne faut pas utiliser
ORDER BY (SELECT 1)). Ce dernier établi un ordre non déterministe, non répétitif.
Le mieux est de classer les lignes sur la primaire.

A+

Merci pour tout

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
WITH
  T (Nom, Salaire,  RowNumber)
  AS
  ( SELECT Nom, Salaire, ROW_NUMBER() OVER  (ORDER BY (SELECT 1))  AS RowNumber  
	FROM TABLE01    
  )
SELECT T.Nom, T.Salaire, T.RowNumber 
FROM T  
WHERE T.RowNumber NOT IN (2, 5)     -- (I, J) = (2, 5) 
UNION ALL
SELECT T.Nom, T.Salaire, 2   -- I = 2 
FROM T
WHERE T.RowNumber = 5        -- J = 5 
UNION ALL
SELECT T.Nom, T.Salaire, 5   -- J = 5 
FROM T
WHERE T.RowNumber = 2        -- I = 2 
ORDER BY RowNumber

voici mon idee

Bien sur ce n'est qu'une premiere reflexion , toute correction de syntaxe et analyse est la bienvenue , I am new

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Create Procedure (@Nom_Table varchar(500),@Nom_Champ1 varchar(500),@Nom_Champ2 varchar(500),@J int ,@I int)
 
 BEGIN
	WITH
  T (Nom, @Nom_Champ2,  RowNumber)
  AS
  ( SELECT @Nom_Champ1, @Nom_Champ2, ROW_NUMBER() OVER  (ORDER BY (SELECT 1))  AS RowNumber  
	FROM  @Nom_Table   
  )
 
SELECT T.@Nom_Champ1, T.@Nom_Champ2, T.RowNumber 
FROM T  
WHERE T.RowNumber NOT IN (@I, @J)     -- (I, J) = (2, 5) 
UNION ALL
SELECT T.@Nom_Champ1, T.@Nom_Champ2,@I   -- I = 2 
FROM T
WHERE T.RowNumber =@J       -- J = 5 
UNION ALL
SELECT T.@Nom_Champ1, T.@Nom_Champ2,@J  -- J = 5 
FROM T
WHERE T.RowNumber =@I       -- I = 2 
ORDER BY RowNumber 
END

Finalement j'ai opté ca :

Code:

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
Create Procedure Swap_row1(@Nom_Table varchar(500),@Nom_Champ1 varchar(500),@Nom_Champ2 varchar(500),@I int ,@J int)
as 
 BEGIN
 
 
  Declare @SQL VarChar(1000)
  Declare @SQL1 VarChar(1000)
  Declare @SQL2 VarChar(1000)
  Declare @SQL3 VarChar(1000)
 
 
 
 
 
  Select @sql='SELECT @Nom_Champ1, @Nom_Champ2, ROW_NUMBER() OVER  (ORDER BY (SELECT 1))  AS RowNumber  FROM'
  SELECT @SQL = @SQL +  @Nom_Table 
  Exec ( @SQL)  
 
 
  Select @SQL1='SELECT @Nom_Champ1, @Nom_Champ2, RowNumber FROM '
  SELECT @SQL1 = @SQL1 +  @Nom_Table + 'WHERE RowNumber NOT IN (@I, @J) '    -- (I, J) = (2, 5) 
 
 
   UNION ALL
 
  Select @SQL2='SELECT @Nom_Champ1, @Nom_Champ2,@I FROM'  -- I = 2 
  SELECT @SQL2 = @SQL1 +  @Nom_Table + 'WHERE RowNumber NOT IN (@I, @J)'
  SELECT @SQL2 = @SQL2+  @Nom_Table +'WHERE RowNumber =@J '      -- J = 5 
 
 
  UNION ALL
 
  Select @SQL3='SELECT @Nom_Champ1, @Nom_Champ2,@J from'  -- J = 5   
  Select @SQL3= @SQL3+@Nom_Table+'WHERE RowNumber =@I ORDER BY RowNumber'       -- I = 2 ORDER BY RowNumber 
 
 
 
end

j'ai cette erreur Msg 10734, Level 15, State 1, Procedure Swap_row1, Line 31
Variable assignment is not allowed in a statement containing a top level UNION, INTERSECT or EXCEPT operator

comment la contourner?

28/07/2011, 22h37
hmira

Tu réinvente complètement la syntaxe du SQL Dynamique !!!

Tu ne t'es même pas donné la peine de regarder la syntaxe du SQL Dynamique !!!

Ci-dessous un lien traitant du SQL Dynamique à lire attentivement avant de poster …
http://msdn.microsoft.com/fr-fr/library/ms188001.aspx

A+

correction

Si si j'ai modifié mes procédures en regardant la syntaxe du sql dynamique , il suffit juste de concaténer mes procedures avec une variable statement

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
set @req +='T (@Nom_colonne,RowNumber)'
                             0set @req +='AS ( SELECT *, ROW_NUMBER() OVER  (ORDER BY (SELECT 1))  AS RowNumber from '+@nom_table+')'
                                          exec sp_executesql @req --execution  de la requete dynamique
 
 
                                          print(@req)
 
 
 
                                          set @req ='select'--la table e, question est affiché sauf les lignes de rang I et J
                                          set @req +='*,T.RowNumber'
                                          set @req +='Where T.RowNumber not in (@random ,@i) '-- (I, J) = (2, 5) 
                                          exec sp_executesql @req
 
                                          print(@req)

Code:

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
set @req +='T (@Nom_colonne,RowNumber)'
                                          set @req +='AS ( SELECT *, ROW_NUMBER() OVER  (ORDER BY (SELECT 1))  AS RowNumber from '+@nom_table+')'
                                          exec sp_executesql @req --execution  de la requete dynamique
 
 
                                          print(@req)
 
 
 
                                          set @req ='select'--la table e, question est affiché sauf les lignes de rang I et J
                                          set @req +='*,T.RowNumber'
                                          set @req +='Where T.RowNumber not in (@random ,@i) '-- (I, J) = (2, 5) 
                                          exec sp_executesql @req
 
                                          print(@req)
 
 
 
                                            /*SELECT T.@nom_colonne, T.RowNumber    --cette requete permet 
                                            FROM T  
                                            WHERE T.RowNumber NOT IN (@I, @J)     -- (I, J) = (2, 5) */
 
                                          --on fait l'union des requetes  l'union des 2 requetes est l'echange du champ de rowNumber I avec le champ de rowNumber J  
 
                                            --union all
 
                                            /*set @req='select'
                                            set @req += '* '
                                            set @req +='FROM T '
                                            set @req+='WHERE T.RowNumber=@i'
                                                    -- I = 2 
                                                    -- J = 5 
                                           exec sp_executesql @req
                                              
                                          --  union 
 
 
 
                                            set @req='select'
                                            set @req += '*'
                                            set @req +='FROM T '
                                            set @req+='WHERE T.RowNumber=@random'  
                                            set @req+='ORDER BY RowNumber '
                                             exec sp_executesql @req
                                                */