Curseur infini dans script dynamique
Bonjour
Je tente de créer un script dynamique pour ajouter les colones qui n'existe pas entre les tables A et B dans la table C mon probleme est que mon curseur ne ce termine jamais.
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
|
@V_COLUMN_NAME NVARCHAR (100),
@V_DATA_TYPE NVARCHAR (100),
@V_CHARACTER_MAXIMUM_LENGTH NVARCHAR (100),
@QUERY NVARCHAR(255),
@V_COLUMN_DEFAULT NVARCHAR (100);
DECLARE ADD_COLUMN_CURSOR2 CURSOR FOR
SELECT b.COLUMN_NAME ,
b.DATA_TYPE ,
b.CHARACTER_MAXIMUM_LENGTH,
b.COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS b
WHERE UPPER(TABLE_NAME)= UPPER('TABLE_A')
AND b.COLUMN_NAME NOT IN (SELECT a.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS a
WHERE UPPER(a.TABLE_NAME)= UPPER('TABLE_B')
)
BEGIN
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND UPPER(TABLE_NAME)= UPPER('TABLE_C'))
CREATE TABLE TABLE_C (ID_LIGNE NUMERIC not null)
ELSE
Print ('entre dans le else')
OPEN ADD_COLUMN_CURSOR2
FETCH NEXT FROM ADD_COLUMN_CURSOR2
INTO @V_COLUMN_NAME,@V_DATA_TYPE ,@V_CHARACTER_MAXIMUM_LENGTH,@V_COLUMN_DEFAULT
WHILE @@FETCH_STATUS = 0
--SET @QUERY = 'ALTER TABLE TABLE_C ADD ' + @V_COLUMN_NAME +@V_DATA_TYPE + @V_CHARACTER_MAXIMUM_LENGTH + @V_COLUMN_DEFAULT
--EXEC(@QUERY)
Print (@V_COLUMN_NAME)
FETCH NEXT FROM ADD_COLUMN_CURSOR2 INTO @V_COLUMN_NAME,@V_DATA_TYPE ,@V_CHARACTER_MAXIMUM_LENGTH,@V_COLUMN_DEFAULT
END;
Print ('sortie du while')
CLOSE ADD_COLUMN_CURSOR2;
DEALLOCATE ADD_COLUMN_CURSOR2; |
Merci pour votre aide
