Une boucle dans une proc stock s'arrête sans raison

**eric.bryan** · 20/08/2013, 12h10

Bonjour à tous,

J'ai créé une procédure stockée (Transact-SQL) qui parcourt les lignes du corps d'une autre procédure stockée et qui stocke chaque ligne dans une variable.
Le but final est de vérifier si chaque ligne matche une regexp, mais pour l'instant la proc lit juste les lignes de l'autre proc ligne à ligne.

La proc s'éxécute bien mais sort de la boucle de façon aléatoire, sans message d'erreur et ne parcourt pas toutes les lignes de l'autre proc.

Si quelqu'un voit une solution ...

ci-joint la proc qui parcourt, la proc parcourue et la table sur laquelle porte la proc parcourue.

Merci beaucoup par avance.

proc qui parcourt :

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
 
CREATE PROCEDURE [dbo].[P_TEST_ENTETE_PROC_2] 
(
@SpBody		TEXT = null
)
AS
 
BEGIN
	DECLARE @NewLine VARCHAR(2) = CHAR(13) + CHAR(10);
	DECLARE @CurrPos BIGINT = 0;
	DECLARE @PosNewLine BIGINT;
	DECLARE @Length BIGINT;
	DECLARE @LineContent VARCHAR(Max);
	DECLARE @ReturnedString VARCHAR(Max) = '';
	DECLARE @CurrentPosTotal BIGINT = 0;
	DECLARE @RowIndex BIGINT = 0;
 
	--Get the stored proc body	
	SELECT @SpBody = definition
	FROM sys.sql_modules
	WHERE object_id = (OBJECT_ID(N'dbo.PROC_1'));
 
	SET @Length = DATALENGTH(@SpBody);
 
	SELECT 'Length : ' + CONVERT(Varchar, @Length);
 
	WHILE @CurrentPosTotal <= @Length
	BEGIN
 
		--get the remaining text from the sp body
		SET @SpBody = SUBSTRING(@SpBody, @CurrPos, DATALENGTH(@SpBody));
		SELECT '@SpBody : ';
		SELECT @SpBody;
 
		-- get the next new line position
		SET @PosNewLine = PATINDEX('%' + @NewLine + '%',@SpBody);
 
		SET @RowIndex = @RowIndex + 1
 
		--if there is no remaining new line
		IF @PosNewLine = 0
		BEGIN
			SELECT 'End of program';
			BREAK;
		END 
 
		SELECT '@PosNewLine : ' + CONVERT(Varchar, @PosNewLine);
 
		--get the text between the last new line and the next new line
		SET @LineContent = SUBSTRING(@SpBody, 0, @PosNewLine);
		SELECT '@LineContent : ' + CONVERT(Varchar, @LineContent);			
 
		--the current position is the next new line
		SET @CurrPos = @PosNewLine + 1;
 
		SELECT '@CurrPos : ' + CONVERT(Varchar, @CurrPos);
 
		SELECT '@RowIndex : ' + CONVERT(Varchar, @RowIndex);
		--test 
		SET @ReturnedString = @ReturnedString + '@@@@' + @LineContent;
		SELECT '@ReturnedString : ' + @ReturnedString;	
 
		SET @CurrentPosTotal = @CurrentPosTotal + @CurrPos;
	END
	-- Return the result of the function
 
	SELECT 'End @ReturnedString';
	SELECT @ReturnedString;
 
END

proc parcourue :

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
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
 
CREATE PROCEDURE [dbo].[PROC_1] 
(
	@FIELD_1	BIGINT OUTPUT, 
	@FIELD_2	VARCHAR(8),
	@FIELD_3	VARCHAR(8),
	@FIELD_4	VARCHAR(8),
	@FIELD_5	VARCHAR(8),
	@FIELD_6	VARCHAR(8),
	@FIELD_7	CHAR(50),
	@FIELD_8	VARCHAR(8),
	@FIELD_9	DECIMAL(10, 2),
	@FIELD_10	VARCHAR(100),
	@FIELD_11	VARCHAR(100),
	@FIELD_12	VARCHAR(100),
	@FIELD_14	VARCHAR(100),
	@FIELD_15	VARCHAR(100),
	@FIELD_16	VARCHAR(100),
	@FIELD_17	VARCHAR(10),
	@FIELD_18	VARCHAR(100),
	@FIELD_19	DATETIME,
	@FIELD_20	VARCHAR(8),
	@FIELD_21	VARCHAR(8),
	@FIELD_22	DATETIME,
	@FIELD_23	SMALLINT,
	@FIELD_24	SMALLINT,
	@FIELD_25	VARCHAR(11),
	@FIELD_26	VARCHAR(34),
	@FIELD_27	VARCHAR(50),
	@FIELD_28	BIGINT,
	@FIELD_29	VARCHAR(50),
	@FIELD_30	VARCHAR(3),
	@FIELD_31	DATETIME,
	@FIELD_32	DATETIME,
	@FIELD_33	VARCHAR(3),
	@FIELD_34	VARCHAR(8),
	@FIELD_35	VARCHAR(20),
	@FIELD_36	VARCHAR(100),
	@FIELD_37	BIGINT,
    @FIELD_38	DATETIME,
    @FIELD_39	VARCHAR(100),
    @FIELD_40	DATETIME
)
AS
BEGIN
 
	INSERT INTO T_TABLE1 (         
		FIELD_2,
		FIELD_3,
		FIELD_4,
		FIELD_5,
		FIELD_6,
		FIELD_7,
		FIELD_8,
		FIELD_9,
		FIELD_10,
		FIELD_11,
		FIELD_12,
		FIELD_14,
		FIELD_15,
		FIELD_16,
		FIELD_17,
		FIELD_18,
		FIELD_19,
		FIELD_20,
		FIELD_21,
		FIELD_22,
		FIELD_23,
		FIELD_24,
		FIELD_25,
		FIELD_26,
		FIELD_27,
		FIELD_28,
		FIELD_29,
		FIELD_30,
		FIELD_31,
		FIELD_32,
		FIELD_33,
		FIELD_34,
		FIELD_35,
		FIELD_36,
		FIELD_37,
		FIELD_38,
		FIELD_39,
		FIELD_40)
	VALUES(
		@FIELD_2,
		@FIELD_3,
		@FIELD_4,
		@FIELD_5,
		@FIELD_6,
		@FIELD_7,
		@FIELD_8,
		@FIELD_9,
		@FIELD_10,
		@FIELD_11,
		@FIELD_12,
		@FIELD_14,
		@FIELD_15,
		@FIELD_16,
		@FIELD_17,
		@FIELD_18,
		@FIELD_19,
		@FIELD_20,
		@FIELD_21,
		@FIELD_22,
		@FIELD_23,
		@FIELD_24,
		@FIELD_25,
		@FIELD_26,
		@FIELD_27,
		@FIELD_28,
		@FIELD_29,
		@FIELD_30,
		@FIELD_31,
		@FIELD_32,
		@FIELD_33,
		@FIELD_34,
		@FIELD_35,
		@FIELD_36,
		@FIELD_37,
		@FIELD_38,
		@FIELD_39,
		@FIELD_40
	)
 
	SET @FIELD_1 = SCOPE_IDENTITY();
 
END

la table sur laquelle porte PROC_1:

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
 
GO
 
/****** Object:  Table [dbo].[T_TABLE1]    Script Date: 08/20/2013 12:04:54 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[T_TABLE1](
	[FIELD_1] [bigint] NULL,
	[FIELD_2] [varchar](8) NULL,
	[FIELD_3] [varchar](8) NULL,
	[FIELD_4] [varchar](8) NULL,
	[FIELD_5] [varchar](8) NULL,
	[FIELD_6] [varchar](8) NULL,
	[FIELD_7] [char](50) NULL,
	[FIELD_8] [varchar](8) NULL,
	[FIELD_9] [decimal](10, 2) NULL,
	[FIELD_10] [varchar](100) NULL,
	[FIELD_11] [varchar](100) NULL,
	[FIELD_12] [varchar](100) NULL,
	[FIELD_14] [varchar](100) NULL,
	[FIELD_15] [varchar](100) NULL,
	[FIELD_16] [varchar](100) NULL,
	[FIELD_17] [varchar](10) NULL,
	[FIELD_18] [varchar](100) NULL,
	[FIELD_19] [datetime] NULL,
	[FIELD_20] [varchar](8) NULL,
	[FIELD_21] [varchar](8) NULL,
	[FIELD_22] [datetime] NULL,
	[FIELD_23] [smallint] NULL,
	[FIELD_24] [smallint] NULL,
	[FIELD_25] [varchar](11) NULL,
	[FIELD_26] [varchar](34) NULL,
	[FIELD_27] [varchar](50) NULL,
	[FIELD_28] [bigint] NULL,
	[FIELD_29] [varchar](50) NULL,
	[FIELD_30] [varchar](3) NULL,
	[FIELD_31] [datetime] NULL,
	[FIELD_32] [datetime] NULL,
	[FIELD_33] [varchar](3) NULL,
	[FIELD_34] [varchar](8) NULL,
	[FIELD_35] [varchar](20) NULL,
	[FIELD_36] [varchar](100) NULL,
	[FIELD_37] [bigint] NULL,
	[FIELD_38] [datetime] NULL,
	[FIELD_39] [varchar](100) NULL,
	[FIELD_40] [datetime] NULL
) ON [PRIMARY]

**aieeeuuuuu** · 21/08/2013, 12h10

Bonjour,

Quel est le but final de l'opération ?

voulez vous vérifier l'absence de certaines instructions dans vos procédure ?
Je pense que vous ne partez pas dans la bonne direction.

Pourquoi ne pas faire une expression rationnelle qui contrôlerait directement l'ensemble de la procédure, plutôt que ligne par ligne ?

Ou alors passer par un autre langage que T-SQL plus adapté pour ce genre de choses.

**soazig** · 28/08/2013, 16h22

Bonjour,
Ton @SpBody est défini en TEXT.
Les autres variables sont en varchar.

Je ne connais pas bien le type TEXT, mais ne serait-ce pas possible que DATALENGTH te renvoie le nombre d'octet de TEXT qui serait codé en UTF8 et que tu comptes d'un autre coté des caractéres codé en ANSI?

Cordialement
Soazig

**SQLpro** · 28/08/2013, 17h13

le type text est considéré comme "deprecated" depuis le version 2005 de SQL server et ne peut être utilisé par de nombreux opérateurs SQL...
En gros un opérateur sur text donne un résultat hasardeux !

A +

**eric.bryan** · 04/09/2013, 22h24

Merci aieeeuuuuu, soazig et SQLpro pour vos réponses.

En effet je passe maintenant par des regex dans SQL Server par le biais des CLR UDF.

Ce sont des assembly créés en C# (ou VB.NET) et compilé qui peuvent être installés dans une base SQL Server et venir enrichir les fonctions.

Donc j'ai créé des fonctions qui gèrent les regex et je peux les utiliser dans SQL Server.

Et maintenant, j'utilise des NVARCHAR(Max) à la place des TEXT.

Eric.

**SQL_EVAN** · 06/09/2013, 11h57

Salut,

Juste pour info, de façon générale pour ce genre de problème. N'hésitez pas à incorporer un TRY...CATCH dans le boucle pour gérer l'erreur quand ça arrive et ne pas arrêter les travaux quand le script trouve une erreur

Ex :

Code :

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

1
2
3
4
5
6
7
8
9
WHILE [condition de boucle]
BEGIN
BEGIN TRY
[traitement]
END TRY
BEGIN CATCH
PRINT 'Il y a eu une erreur, voici le message d''erreur : ' + ERROR_MESSAGE()
END CATCH
END

**eric.bryan** · 12/06/2014, 17h01

Merci SQL_EVAN,

Je vais tenir compte de ta remarque.

Une boucle dans une proc stock s'arrête sans raison

Développement SQL Server

Discussions similaires

Partager

Partager