Discussion :

[narutobaka]

Bonjour,

Je rencontre un soucis de syntaxe avec du SQL dynamique afin de faire passer en paramètre de ma procédure stockée le nom partiel de ma table.

J'ai utilisé la syntaxe présentée ici.

Ma procédure initiale sans SQL dynamique :

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
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[TEST_PS_Insert_Cas_Annulation] 
 
@id_bon_peinture int,
@id_cas_annulation int,
@precision_refus_peinture text	
 
AS
BEGIN
	SET NOCOUNT ON;
 
        INSERT INTO DEV_REL_ANNULATION
	(
	id_bon_peinture,
	id_cas_annulation,
	precision_refus_peinture
	)
	Values
	(@id_bon_peinture,
	@id_cas_annulation,
	@precision_refus_peinture
	)
END

Cette Procédure Stockée fonctionne correctement.


Première version avec du SQL dynamique :

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
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[TEST_PS_Insert_Cas_Annulation] 
 
@nom_serveur varchar(10),
@id_bon_peinture int,
@id_cas_annulation int,
@precision_refus_peinture text	
 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @Ma_Requete varchar (8000)
 
 
    -- Insert statements for procedure here
 
	SET @Ma_Requete = 
	'INSERT INTO '+@nom_serveur+'_REL_ANNULATION
	(
	id_bon_peinture,
	id_cas_annulation,
	precision_refus_peinture
	)
	Values
	(@id_bon_peinture,
	@id_cas_annulation,
	@precision_refus_peinture
	)'
 
	EXEC (@Ma_Requete)
 
END

Je n'ai pas d'erreur de syntaxe. Cependant lorsque j'exécute dans le requêteur Microsoft SQL Serveur Management Studio Express j'obtient le message d'erreur suivant :

Code :

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

1
2
3
4
Msg 137, Niveau 15, État 2, Ligne 8
La variable scalaire "@id_bon_peinture" doit être déclarée.
 
(1 ligne(s) affectée(s))

J'ai donc pensé à cette deuxième solution en concaténant les variables d'entrées :

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
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[TEST_PS_Insert_Cas_Annulation] 
 
@nom_serveur varchar(10),
@id_bon_peinture int,
@id_cas_annulation int,
@precision_refus_peinture text	
 
AS
BEGIN
 
	SET NOCOUNT ON;
	DECLARE @Ma_Requete varchar (8000)
 
	SET @Ma_Requete = 
	'INSERT INTO '+@nom_serveur+'_REL_ANNULATION
	(
	id_bon_peinture,
	id_cas_annulation,
	precision_refus_peinture
	)
	Values
	('+@id_bon_peinture+','+
	@id_cas_annulation+','+
	@precision_refus_peinture+'
	)'
 
	EXEC (@Ma_Requete)
 
END

Mais avec cette syntaxe il me lève cette erreur lorsque j'essaye d'enregistrer les modifications sur ma procedure :

Code :

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

1
2
Msg*206, Niveau*16, État*2, Procédure*TEST_PS_Insert_Cas_Annulation, Ligne*24
Conflit de types d'opérandes*: text est incompatible avec int

J'avoue ne pas comprendre ou se situe le conflit d'opérande pour ce test.

Si qqn à une idée sur la syntaxe des paramètre d'entrée dans du SQL dynamique.

Merci d'avance.

Je continue à chercher d'où cela peut bien venir ....

+++

[Philippe Robert]

C'est parce que tu concatènes des variables de type int avec du texte.

Essaie pour chaqure variable de type int la syntaxte CAST(@variable as varchar(10)) dans ta requête SQL dynamique.

SQL 2005 ne plainsante pas avec les types de données.

Remarque : en SQL 2008 (d'après ce que j'ai pu lire à ce sujet) les noms de table peuvent être passé en paramètre.

A+

[narutobaka]

Bonjour,

et merci pour ta réponse.

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
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[TEST_PS_Insert_Cas_Annulation] 
 
@nom_serveur varchar(10),
@id_bon_peinture int,
@id_cas_annulation int,
@precision_refus_peinture text	
 
AS
BEGIN
 
	SET NOCOUNT ON;
	DECLARE @Ma_Requete varchar (8000)
 
	SET @Ma_Requete = 
	'INSERT INTO '+@nom_serveur+'_REL_ANNULATION
	(
	id_bon_peinture,
	id_cas_annulation,
	precision_refus_peinture
	)
	Values
	('+
	CONVERT(char,@id_bon_peinture)+','+
	CONVERT(char,@id_cas_annulation)+','+
	CONVERT(char,@precision_refus_peinture)
	+')'
 
	EXEC (@Ma_Requete)
 
END

Un de mes formateurs m'avait transmis une syntaxe équivalente au cast la fonction CONVERT. J'ai ainsi réussit à résoudre une partie du problème.

En effet lorsque je lance la requête dans le requêteur j'obtiens un message d'erreur pour @precision_refus_peinture qui est de type texte. J'obtiens deux messages différents :

Lorsque @precision_refus_peinture = ' ' => caractère espace

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
USE [pieces_peintes_db]
GO
 
DECLARE	@return_value int
 
EXEC	@return_value = [dbo].[TEST_PS_Insert_Cas_Annulation]
		@nom_serveur = N'DEV',
		@id_bon_peinture = 106,
		@id_cas_annulation = 2,
		@precision_refus_peinture = N' '
 
SELECT	'Return Value' = @return_value
 
GO

Code :

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

1
2
3
4
Msg*102, Niveau*15, État*1, Ligne*8
Syntaxe incorrecte vers ')'.
 
(1*ligne(s) affectée(s))

Lorsque @precision_refus_peinture contient une chaine de caractère :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
USE [pieces_peintes_db]
GO
 
DECLARE	@return_value int
 
EXEC	@return_value = [dbo].[TEST_PS_Insert_Cas_Annulation]
		@nom_serveur = N'DEV',
		@id_bon_peinture = 106,
		@id_cas_annulation = 2,
		@precision_refus_peinture = N'test_toto'
 
SELECT	'Return Value' = @return_value
 
GO

Code :

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

1
2
3
4
Msg*128, Niveau*15, État*1, Ligne*8
Le nom "test_toto" n'est pas autorisé dans ce contexte. Les expressions valides sont des constantes, des expressions constantes et (dans certains contextes) des variables. Les noms de colonnes ne sont pas autorisés.
 
(1*ligne(s) affectée(s))

Et la je ne vois pas du tout d'ou vient le problème.

Autre chose me turlupine concernant le SQL dynamique :
Que ce passe-t-il si la requête dépasse la taille du varchar(8000)?

voili voilou

je continue mes recherches

[elsuket]

Bonjour,

Avant d'exécuter une procédure stockée qui contient du SQL dynamique, remplacez la commande EXEC par PRINT.
A l'exécution de votre procédure stockée, vous obtiendrez le texte de la requête que vous demandez d'exécuter.
Ainsi, vous auriez vu qu'il manque des quotes pour la variable @precision_refus_peinture dans la clause VALUES

Il vaudra mieux utiliser des VARCHAR pour du SQL dynamique, et remplacer la fonction CONVERT par CAST.
La longueur de votre chaîne de requête n'excèdera pas les 150 caractères à mon avis, par sécurité vous pouvez mettre 256 ...
Pensez-vous que @precision_refus_peinture nécessite une variable et une colonne de type TEXT ?
Pensez à ne plus utiliser ce type de données, je crois qu'il a été retiré de SQL Server depuis sa version 2008

Finalement, vous obtenez le code suivant :

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
ALTER PROCEDURE [dbo].[TEST_PS_Insert_Cas_Annulation] 
	@nom_serveur VARCHAR(10),
	@id_bon_peinture INT,
	@id_cas_annulation INT,
	@precision_refus_peinture VARCHAR(256)	 
AS
BEGIN
	DECLARE @Ma_Requete VARCHAR(256)
 
	SET @Ma_Requete = 
	'INSERT INTO '+ @nom_serveur + '_REL_ANNULATION
	(
		id_bon_peinture,
		id_cas_annulation,
		precision_refus_peinture
	)
	VALUES
	(' +
		CAST(@id_bon_peinture AS VARCHAR)+ ',' +
		CAST(@id_cas_annulation AS VARCHAR)+ ',' +
		'''' + CAST(@precision_refus_peinture AS VARCHAR) + '''' +
	')'
 
	-- PRINT @Ma_Requete
	EXEC (@Ma_Requete)
END

@++

[narutobaka]

Bonjour,

Et encore merci pour vos réponse.

Avant d'exécuter une procédure stockée qui contient du SQL dynamique, remplacez la commande EXEC par PRINT.

Je note cette astuce bien précieusement. Je suis encore novice en TSQL et je ne suis qu'en phase de découverte .

remplacer la fonction CONVERT par CAST.

Quel est la différence entre ces deux fonctions de transtypage ?

La longueur de votre chaîne de requête n'excèdera pas les 150 caractères à mon avis, par sécurité vous pouvez mettre 256 ...

Mon soucis à ce niveau vient de @precision_refus_peinture. En effet cette donnée correspond à du texte saisie par un utilisateur dans un formulaire. J'ai limité le nombre de caractères autorisés à 500 dans mon formulaire de saisie, mais il faut prendre en compte les caractères spéciaux que je transforme en code HTML avant insertion dans la base ce qui implique qu'il prenne plus de place dans la base de donnée.

Au début j'avais utilisé un varchar(5000), car j'ai remarqué que le max en conversion avec HTMLentities était de 9 ou 10 caractère et ca me laisse une marge. (cf Mon post)

Cependant j'ai rencontré un soucis de troncature lorsque je récupérais mes données depuis la BDD pour les utiliser depuis Php (cf Mon post).

Par contre je n'avais pas utilisé le CAST mais changé le type de donnée de ma colonne de varchar(5000) en text

Pensez à ne plus utiliser ce type de données, je crois qu'il a été retiré de SQL Server depuis sa version 2008

J'ai en effet lu cette information dans mes recherches. Je vais d'ailleur régler mon problème de troncature en testant le CAST plutot que changer le type de ma colonne dans la BDD.

Cependant ma question est :

Que se passera-t-il si @precision_refus_peinture à une taille > à la valeur fixée pour la taille de la requête?

Autre chose me turlupine concernant le SQL dynamique :
Que ce passe-t-il si la requête dépasse la taille du varchar(8000)?

Merci pour votre aide

Je vais faire mes différents tests

++

[elsuket]

Bonjour,

Je note cette astuce bien précieusement. Je suis encore novice en TSQL et je ne suis qu'en phase de découverte .

comment croyez-vous que je l'ai trouvée ? En tâtonnant, comme tout le monde

Quel est la différence entre ces deux fonctions de transtypage ?

La fonction CONVERT permet d'appliquer un style à la donnée retournée, alors que la fonction CAST ne fait que transtyper uniquement.
UN SGBD n'étant pas conçu pour faire de la cosmétique, si vous n'avez pas besoin d'appliquer un style, utilisez CAST.

mais il faut prendre en compte les caractères spéciaux que je transforme en code HTML avant insertion dans la base ce qui implique qu'il prenne plus de place dans la base de donnée.

Effectivement, je ne l'avais pas envisagé de cette façon

Je vais d'ailleurs régler mon problème de troncature en testant le CAST plutot que changer le type de ma colonne dans la BDD.

Et pourquoi pas ? Passez votre colonne en VARCHAR(MAX), non ?

Que se passera-t-il si @precision_refus_peinture à une taille > à la valeur fixée pour la taille de la requête?

Si vous avez fixé la propriété de session ANSI_WARNINGS à OFF, cela ne génèrera pas d'erreur.
Dans le cas contraire, une erreur de troncature de données sera générée.
Passez donc @Ma_Requete en type VARCHAR(MAX).

Autre chose me turlupine concernant le SQL dynamique :
Que ce passe-t-il si la requête dépasse la taille du varchar(8000)?

La même chose que ce qui est décrit juste au-desssus

@++

[narutobaka]

Bonjour,

et merci pour vos réponses.

Je me pose encore une petite question :

Si pour le sql dynamique on effectue un transtypage de toute les variables en Varchar, quelle est le type de la donnée qui sera insérer dans ma base ?

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
ALTER PROCEDURE [dbo].[TEST_PS_Insert_Cas_Annulation] 
	@nom_serveur VARCHAR(10),
	@id_bon_peinture INT,
	@id_cas_annulation INT,
	@precision_refus_peinture VARCHAR(256)	 
AS
BEGIN
	DECLARE @Ma_Requete VARCHAR(256)
 
	SET @Ma_Requete = 
	'INSERT INTO '+ @nom_serveur + '_REL_ANNULATION
	(
		id_bon_peinture,
		id_cas_annulation,
		precision_refus_peinture
	)
	VALUES
	(' +
		CAST(@id_bon_peinture AS VARCHAR)+ ',' +
		CAST(@id_cas_annulation AS VARCHAR)+ ',' +
		'''' + CAST(@precision_refus_peinture AS VARCHAR) + '''' +
	')'
 
	-- PRINT @Ma_Requete
	EXEC (@Ma_Requete)
END

Dans mon exemple, @id_bon_peinture qui est un champs de type int dans ma base, si je le transtype en type varchar pour le sql dynamique de quel type sera-t-il lors de mon insertion ?

En fait ce que je veux savoir c l'impacte du CAST sur le type de ma donnée

voili voilou

merci pour votre aide

++

[elsuket]

Bonjour,

Dans le cas du SQL dynamique, cela n'a pas d'incidence sur le type de la valeur qui sera traitée.
Vous n'avez pas d'autre choix que de transtyper un entier en chaîne de caractères parce que, dans le cas contraire, vous ne pouvez pas construire votre chaîne de commande SQL.

Vous pouvez tout simplement tester en remplaçant directement la commande EXEC par PRINT : votre commande SQL vous sera alors retournée.
Il vous suffit alors de la copier dans l'analyseur de requête pour l'exécuter.
Vous pouvez éventuellement tester votre instruction INSERT dans un table de même structure qui n'est pas la table de production.

Pour copier uniquement la structure de la table (sans les contraintes ni les index) de prod dans une table de test :

Code :

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

1
2
3
SELECT TOP 0 *
INTO maTableTest
FROM maTableProd

@++

[narutobaka]

bonjour

et merci pour vos réponses.

Vous n'avez pas d'autre choix que de transtyper un entier en chaîne de caractères

J'ai bien compris cette contrainte du SQL dynamique.

Cependant ce sur quoi je m'interroge, est-ce que toute les données manipulé via le SQL dynamique seront des varchar et stockée en varchar dans ma base.

Que se passe-t-il alors dans un insert ou un update si le champs recevant la valeur n'est pas de type varchar.

Ceci afin de savoir si je peux généraliser le procédé pour toutes mes procédures stockées. Le but étant d'éviter les surprises par la suite

encore merci pour votre aide

++

[elsuket]

Bonjour,

Vous pouvez tout simplement tester en remplaçant directement la commande EXEC par PRINT : votre commande SQL vous sera alors retournée.

Et c'est cette commande qui est exécutée.
Donc si votre commande comporte des valeurs numériques et que la colonne cible est du même type numérique, il n'y aura pas d'erreurs (sauf contraintes, ...)
Si votre commande tente d'insérer une valeur littérale mais que celle ci est numérique ('123' par exemple), si SQL Server parvient à la transtyper automatiquement, il le fera pour réaliser l'insertion dans une colonne cible de type numérique, mais vous voyez les risques que cela comporte.
Si votre commande tente d'insérer une valeur littérale ('123AB' ou 'ElSuket') dans une colonne de type numérique, alors la l'exécution de la commande provoquera une erreur.

J'espère ne pas vous avoir répondu à côté de ce que vous cherchez

@++