IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

MS SQL Server Discussion :

[MsSQL2K5] passage d'un nom de table dans une procédure stockée


Sujet :

MS SQL Server

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre très actif
    Profil pro
    Inscrit en
    Août 2005
    Messages
    483
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2005
    Messages : 483
    Par défaut [MsSQL2K5] passage d'un nom de table dans une procédure stockée
    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 ....

    +++

  2. #2
    Membre confirmé
    Profil pro
    Inscrit en
    Janvier 2008
    Messages
    240
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : Janvier 2008
    Messages : 240
    Par défaut
    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+

  3. #3
    Membre très actif
    Profil pro
    Inscrit en
    Août 2005
    Messages
    483
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2005
    Messages : 483
    Par défaut
    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

  4. #4
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 43
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Par défaut
    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
    @++

  5. #5
    Membre très actif
    Profil pro
    Inscrit en
    Août 2005
    Messages
    483
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2005
    Messages : 483
    Par défaut
    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

    ++

  6. #6
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 43
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Par défaut
    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

    @++

Discussions similaires

  1. passage d'un nom de table dans une procédure stockée
    Par thierry V dans le forum MS SQL Server
    Réponses: 7
    Dernier message: 26/07/2010, 16h48
  2. [ASE] Alter table dans une procédure stockée
    Par malignois dans le forum Adaptive Server Enterprise
    Réponses: 2
    Dernier message: 01/10/2009, 11h20
  3. LOCK Table dans une procédure stockée
    Par nonilastar dans le forum Firebird
    Réponses: 4
    Dernier message: 15/05/2009, 11h27
  4. Réponses: 2
    Dernier message: 03/03/2006, 13h41
  5. Problème d'alter table dans une procédure stockée
    Par Oluha dans le forum MS SQL Server
    Réponses: 3
    Dernier message: 16/03/2005, 09h19

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo