none
Procédure stockée et sous requete qui retourne plus d'une valeur RRS feed

  • Question

  • Bonjour,

    j'écris une procédure stockée qui reçoit différents paramètres dont  1 Table Value Parameters dont le type est définit comme ceci :

    CREATE TYPE [dbo].[CoursType] AS TABLE(
    	[CoursId] [int] NULL,
    	[CoursNom] [varchar](50) NULL
     

    Ma procédure stocké reçoit le paramètre @CoursSuivis qui est de ce type.

    Le but de cette procédure est de modifier une fiche élève et de parcourir la Table EleveCours et modifier les cours d'un même élève. Elle se présente comme ceci:

    CREATE PROCEDURE [dbo].[sp_EditerEleve]
    	@idEleve int,
    	@nom varchar(50),
    	@prenom varchar(50),
    	@age int,
    	@idstatut int,
    	@annee int,
    	@actif int,
    	@cheminPhoto varchar(350),
    	@CoursSuivis CoursType READONLY
    	
    AS
    BEGIN
    IF(@age BETWEEN 12 AND 20)
    	BEGIN
    		UPDATE Eleve SET Nom=@nom , Prenom=@prenom, Age=@age, IDStatut=@idstatut, Annee=@annee, Actif=@actif, Photo=@cheminPhoto
    		WHERE IDEleve = @idEleve 
    	END
    	
    	
    	BEGIN
    	DECLARE @NbreCoursActuel int
    	DECLARE @NbreCours int
    	BEGIN
    		set @NbreCoursActuel = (SELECT COUNT(*) FROM EleveCours WHERE IDEleve = @idEleve);
    		set @NbreCours = (SELECT COUNT(*) FROM @CoursSuivis);
    	END
    	IF @NbreCoursActuel = @NbreCours
    	PRINT 'OK_1'
    		BEGIN
    		DECLARE @identifiantEleve int;
    		SET @identifiantEleve  = ( SELECT IDEleve FROM EleveCours WHERE IDEleve = @idEleve);
    		While (@identifiantEleve = @idEleve)
    			BEGIN
    			UPDATE EleveCours SET IDCours = (SELECT CoursId FROM @CoursSuivis), CoursNom = (SELECT CoursNom FROM @CoursSuivis) WHERE IDEleve = @idEleve;
    			END
    		END
    	END
    END

    Mon problème est que lorsque j'exécute ma procédure, ma sous requète retourne visiblement plusieurs valeurs ce qui n 'est pas permis lorsqu'elle suit un =, !=, <... . Le message d'erreur est :

    Msg 512, Niveau 16, État 1, Procédure sp_EditerEleve, Ligne 38
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    

    Auriez-vous une idée?

    Merci

    mercredi 21 novembre 2012 12:50

Réponses

  • Bonjour,

    j'ai testé la procédure dans mon code C# et après plusieurs ajustages, cela fonctionne parfaitement.

    Merci pour votre aide et vos conseils ! J'étais parti sur une mauvaise piste en voulant tester le nombre de cours présents et le nombre de cours à modifier.

    Comme vous me l'avez conseillé, un simple DELETE suivi d'un INSERT fait parfaitement l’affaire.

    Je vous donne la version finale de la procédure stocké en espérant que cela puisse en aider d'autres.

    Pour rappel elle fait les 3 actions suivantes:

    1 - Modifier les informations (déjà existantes) d'un élève

    2 - Supprime les cours qu'il ne suit plus.

    3 - Ajoute les nouveaux cours auxquels il s'inscrit

    USE [Ecole]
    GO
    
    /****** Object:  StoredProcedure [dbo].[sp_EditerEleve]    Script Date: 11/24/2012 17:12:53 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    -- =============================================
    CREATE PROCEDURE [dbo].[sp_EditerEleve]
    	@idEleve int,
    	@nom varchar(50),
    	@prenom varchar(50),
    	@age int,
    	@idstatut int,
    	@annee int,
    	@actif int,
    	@cheminPhoto varchar(350),
    	@CoursSuivis CoursType READONLY
    	
    AS
    BEGIN
    
    SELECT * FROM @CoursSuivis
    
    IF @age BETWEEN 12 AND 20
    		UPDATE Eleve 
    		SET Nom=@nom , 
    		Prenom=@prenom, 
    		IDStatut=@idstatut, 
    		Annee=@annee,
    		Age=@age,  
    		Actif=@actif, 
    		Photo=@cheminPhoto
    		WHERE IDEleve = @idEleve 
    	
    
    	DELETE FROM EleveCours 
    	WHERE IDEleve=@idEleve 
    	AND IDCours 
    	NOT IN (SELECT CoursID FROM @CoursSuivis)
    	
    	SELECT CAST(@@ROWCOUNT AS VARCHAR)+' suppressions.'
    
    	INSERT INTO EleveCours(IDCours,IDEleve,CoursNom) 
    	SELECT CoursId, @idEleve,CoursNom 
    	FROM @CoursSuivis 
    	WHERE CoursId  NOT IN (SELECT IDCours FROM EleveCours WHERE IDEleve=@idEleve)
    	SELECT CAST(@@ROWCOUNT AS VARCHAR)+' ajouts.'
    
    END

    Encore un grand merci !

    • Marqué comme réponse paintbox00 samedi 24 novembre 2012 16:16
    samedi 24 novembre 2012 16:16

Toutes les réponses

  • Bonjour,

    Il semblerait que l'intention soit de supprimer les cours qui ne sont plus dans la liste et d'ajouter ceux qui manquent ? Cela donnerait par exemple qq chose comme :

    -- Supprimer les cours qui ne sont plus dans la liste
    DELETE FROM EleveCours WHERE IDEleve=@IdEleve AND NOT IDCours IN (SELECT CoursId FROM @CoursSuivis)

    -- Ajouter les cours qui sont dans la liste et pas déjà présents
    INSERT INTO EleveCours(IDEleve,IDCours) SELECT @IdEleve,CoursID FROM @CoursSuivis WHERE NOT CoursID IN (SELECT IDCours FROM EleveCours WHERE IDEleve=@IdEleve)

    Je ne traite pas CoursNom. Cela me parait bizarre que cette colonne soit dans EleveCours. Elle devrait plutôt être dans une table Cours ?

    Eventuellement expliquez votre intention. La cause du problème est claire (vous faites une sous-requête sur une liste qui peut contenir plusieurs valeurs) mais il n'est pas forcément facile de comprendre votre intention exacte à partir d'un code ou d'une logique peut-être erronée (je suis surpris de ne voir qu'un UPDATE j'imagine que la liste peut contenir des cours en plus ou en moins par rapport aux cours actuels de l'élève ?).


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    mercredi 21 novembre 2012 14:02
  • Bonjour,

    merci pour votre aide. Le but en effet est de modifier:

    1° la fiche d'un étudiant

    2° Les cours en + ou en - d'un étudiant et aussi le cas où le nombre de cours à modifier est = au nombre de cours déjà encodés.

    Je ne suis qu'au début de ma procédure. Celle que je tente de faire est de mettre à jour les cours si le nombre de cours à modifier et le nombre de cours auxquels il est déjà inscrit sont identiques. Donc mon idée était de sélectionner les cours suivis par 1 élève et de les mettre à jour (même s'ils sont identiques).

    Les autres étapes (+ et -) se feront après.

    La colonne LibelleCours existe en effet dans la table Cours mais je l'ai repris dans EleveCours pour une question de lisibilité plus facile que les IDCour.


    • Modifié paintbox00 mercredi 21 novembre 2012 14:24 Correction
    mercredi 21 novembre 2012 14:16
  • La ligne de suppression

    DELETE FROM EleveCours WHERE IDEleve=@IdEleve AND NOT IDCours IN (SELECT CoursId FROM @CoursSuivis)

    enlève tous les lignes où IDEleve est le même sans tenir compte si IDCours se trouve ou pas dans @CoursSuivis

    mercredi 21 novembre 2012 14:39
  • A mon avis autant le faire tout de suite car si le nombre de cours est identique le cas général fonctionnera aussi. De plus si je supprime un cours et que j'en ajoute un autre, le nombre de cours est identique et pourtant je dois aussi en supprimer et en ajouter  un ce qui me ramène encore au cas général DELETE/INSERT qui permet de tout traiter.

    Ce sera sans doute aussi plus efficace que l'approche qui semble consister à tout traiter ligne par ligne (mais votre boucle pour l'instant ne se termine jamais, apparemment votre intention était de parcourir les lignes existantes et de les écraser sans de poser de questions si on a le même nombre de lignes des deux côtés ?)

    Egalement l'approche DELETE/INSERT permettrait éventuellement de préserver d'autres valeurs que l'on ajouterait dans la table EleveCours (par exemple la date d'inscription à ce cours) ce que ne permettra pas un écrasement total des lignes existantes.

    Pour CoursNom, si vous y tenez vraiment, la mise à jour du libellé serait qq chose comme UPDATE EleveCours SET CoursNom=@Suivi.coursNom FROM EleveCours JOIN @coursID ON CoursId=IdCours WHERE IDEleve=@idEleve, mais :
    - la table Cours a t'elle été mise à jour au préalable ? Si non, il faut le faire aussi sinon incohérence.
    - et donc il faudrait peut-être supprimer le critère sur l'élève pour que la mise à jour du libellé soit faite aussi pour les autres élèves concernés par ces mêmes cours
    ce qui introduit déjà un certain nombre de complications qui semblent déjà pires que d'aller chercher la donnée dans une autre table avec une simple jointure.


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".


    mercredi 21 novembre 2012 14:58
  • Mon idée en cas de nombre de cours identiques était de mettre à jour la table et donc même si le nombre est identique mais que les intitulé et IDCours changent , ils le seront dans la table EleveCours parce qu'il va tout réécrire.

    Pour info je ne change pas l'intitulé de la table cours c'est simplement si un élève décide de changer de cours, on modifie la liste(EleveCOurs) des cours qu'il suit.

    Le problème du DELETE est qu'il me supprime toutes les lignes dont l'IDEleve est @IDEleve sans faire de distinction entre les IDCours

    Alors que écrit hors de la procédure stockée, la commande 

    DELETE FROM EleveCours WHERE IDEleve='3' and IDCours NOT IN ('4', '7', '1')

    Supprime bien les lignes ou les cours ne figure pas dans ('4', '7', '1')

    • Modifié paintbox00 mercredi 21 novembre 2012 15:13 ajout
    mercredi 21 novembre 2012 15:09
  • Essayez peut-être de sortir SELECT CoursID FROM @CoursSuivi et SELECT IDCours FROM EleveCours WHERE IdEleve=@idEleve pour voir visuellement quelles sont vos données ?

    A priori on a bien pourtant comme deuxième critère "et la valeur IDCours (de la table ElèveCours) n'est pas présente dans la liste des CoursID des nouveaux cours (à moins que CoursID ne correspondent pas à IDCours même si le cours ne change pas ???)


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    mercredi 21 novembre 2012 15:26
  • Pourtant un test dans une base vierge comme :

    DROP PROC sp_EditerEleve
    DROP TYPE CoursType
    DROP TABLE EleveCours
    GO
    CREATE TYPE [dbo].[CoursType] AS TABLE(
    	[CoursId] [int] NULL --,[CoursNom] [varchar](50) NULL
    )
    GO
    CREATE TABLE EleveCours(EleveId INT,CoursId INT)
    GO
    CREATE PROCEDURE [dbo].[sp_EditerEleve](
    	@idEleve int,
    	@CoursSuivis CoursType READONLY)
    AS
    DELETE FROM EleveCours WHERE EleveId=@idEleve AND CoursID NOT IN (SELECT CoursID FROM @CoursSuivis)
    SELECT CAST(@@ROWCOUNT AS VARCHAR)+' suppressions.'
    INSERT INTO EleveCours(EleveId,CoursId) SELECT @idEleve,CoursID FROM @CoursSuivis WHERE CoursID NOT IN (SELECT CoursId FROM EleveCours WHERE EleveId=@idEleve)
    SELECT CAST(@@ROWCOUNT AS VARCHAR)+' ajouts.'
    -- Pour vérifier que les deux listes sont identiques
    SELECT * FROM @coursSuivis
    SELECT * FROM EleveCours WHERE EleveId=@IdEleve
    GO
    DECLARE @t CoursType
    INSERT INTO @t VALUES (1)
    INSERT INTO @t VALUES (2)
    INSERT INTO @t VALUES (3)
    EXECUTE sp_EditerEleve 1,@t
    DELETE FROM @t WHERE CoursId=2
    EXECUtE sp_EditerEleve 1,@t

    semble ok chez moi : le nombre de suppressions et d'ajouts est correct, la liste passée en paramètre et celle de l'élève sont bien identiques après modification des données existantes et donc la ligne 2 est bien supprimée mais pas les autres...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".


    mercredi 21 novembre 2012 18:45
  • Bonjour,

    merci pour votre code.

    Cela fonction effectivement chez moi pour le DELETE mais le INSERT ne marche pas.

    INSERT INTO EleveCours(EleveId,CoursId) SELECT @idEleve,CoursID FROM @CoursSuivis WHERE CoursID NOT IN (SELECT CoursId FROM EleveCours WHERE EleveId=@idEleve)

    je crois qu'il n'identifie pas WHERE CoursID comme étant le CoursID de CoursSuivis.

    J'ai essayé alors comme ceci :

    INSERT INTO EleveCours(IDEleve,IDCours) 
    	SELECT @idEleve,CoursID 
    	FROM @CoursSuivis 
    	WHERE (SELECT CoursID FROM @CoursSuivis)  NOT IN (SELECT CoursId FROM EleveCours WHERE IDEleve=@idEleve)

    mais je me retrouve à nouveau avec le message 

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


    mercredi 21 novembre 2012 22:26
  • Cela ne serait pas plutôt au niveau de la sous-requête ?

    (SELECT CoursId FROM EleveCours WHERE EleveId=@IdEleve) serait peut-être
    (SELECT IdCours FROM etc...

    J'ai effectivement toujours utilisé TableId tandis que vous utilisez tantôt TableId tantôt IdTable...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".


    jeudi 22 novembre 2012 09:17
  • Bonjour,

    merci pour votre réponse.

    Non, j'ai corrigé la sous-requête selon les champs de mes tables. 

    Je rencontre à présent, un conflit de clé étrangère avec la table cours.

    The INSERT statement conflicted with the FOREIGN KEY constraint "FK_EleveCours_Cours". The conflict occurred in database "Ecole", table "dbo.Cours", column 'IDCours'.
    
    J'ai vérifié la table Cours et fait un select de @CoursSuivis et je ne vois pas où il pourrait y avoir un conflit de clé étrangère.

    Les IDCours utilisés dans @CoursSuivis sont bien des id de cours existants.

    jeudi 22 novembre 2012 10:45
  • Et votre instruction INSERT est donc devenue ? J'imagine que si SQL Server signale ce problème c'est qu'il existe réellement. Si les données semblent bonnes se pourrait il qu'il y a ait une intervention de colonnes (qui ferait par exemple que l'on essaie de mettre des valeurs EleveId dans une colonne CoursId ou qq chose comme cela) ?


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    jeudi 22 novembre 2012 11:05
  • Ma requête INSERT est à présent comme ceci :

    INSERT INTO EleveCours(IDCours,IDEleve,CoursNom) 
    	SELECT @idEleve,CoursId, CoursNom 
    	FROM @CoursSuivis 
    	WHERE CoursId  NOT IN (SELECT IDCours FROM EleveCours WHERE IDEleve=@idEleve)
    	SELECT CAST(@@ROWCOUNT AS VARCHAR)+' ajouts.'


    J'ai également pensé à une inversion de colonne mais il semble que non.

    Les champs de mes tables sont :

    CREATE TABLE [dbo].[EleveCours](
    	[IDCours] [int] NOT NULL,
    	[IDEleve] [int] NOT NULL,
    	[CoursNom] [varchar](50) NOT NULL
    ) ON [PRIMARY]

    CREATE TABLE [dbo].[EleveEcole](
    	[IDEleve] [int] NOT NULL,
    	[IDEcole] [int] NOT NULL
    ) ON [PRIMARY]

    et mon type

    CREATE TYPE [dbo].[CoursType] AS TABLE(
    	[CoursId] [int] NULL,
    	[CoursNom] [varchar](50) NULL
    )

    Je teste la requete suivante :

    DECLARE @CoursSuivis AS CoursType
    DECLARE @idEleve int = '22';
    
    INSERT INTO @CoursSuivis Values('2','Informatique')
    INSERT INTO @CoursSuivis Values('5','Biologie')
    INSERT INTO @CoursSuivis Values('6','Langues')
    INSERT INTO @CoursSuivis Values('1','Science')
    INSERT INTO @CoursSuivis Values('4','Mathématique')
    
    SELECT COUNT (*) FROM @CoursSuivis
    
    INSERT INTO EleveCours(IDCours,IDEleve,CoursNom) 
    	SELECT @idEleve,CoursId, CoursNom 
    	FROM @CoursSuivis 
    	WHERE CoursId  NOT IN (SELECT IDCours FROM EleveCours WHERE IDEleve=@idEleve)
    	SELECT CAST(@@ROWCOUNT AS VARCHAR)+' ajouts.'

    • Modifié paintbox00 jeudi 22 novembre 2012 11:24 Ajout
    jeudi 22 novembre 2012 11:13
  • Lorsque je lance le débugeur sur la requete suivante :

    SELECT * FROM Eleve WHERE IDEleve = '22'
    
    DECLARE @CoursSuivis AS CoursType
    DECLARE @idEleve int = '22';
    
    INSERT INTO @CoursSuivis Values('2','Informatique')
    INSERT INTO @CoursSuivis Values('5','Biologie')
    INSERT INTO @CoursSuivis Values('6','Langues')
    INSERT INTO @CoursSuivis Values('1','Science')
    INSERT INTO @CoursSuivis Values('4','Mathématique')
    
    SELECT COUNT (*) FROM @CoursSuivis
    
    INSERT INTO EleveCours(IDCours,IDEleve,CoursNom) 
    	SELECT @idEleve,CoursId, CoursNom 
    	FROM @CoursSuivis 
    	WHERE CoursId  NOT IN (SELECT IDCours FROM EleveCours WHERE IDEleve=@idEleve)
    	SELECT CAST(@@ROWCOUNT AS VARCHAR)+' ajouts.'
     

    il tourne sans fin. De plus, si je mets des espions sur CoursId, CoursNom, @idEleve, il ne me met aucune valeur.

    Est-ce normal?

    Merci

    jeudi 22 novembre 2012 16:57
  • Il ya bien une inversion des colonnes :

    INSERT INTO EleveCours(IDCours,IDEleve,CoursNom)
    SELECT @idEleve,CoursId, CoursNom

    Donc @idEleve va dans IDCours et CoursId va dans IDEleve d'où sans doute le problème.


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    jeudi 22 novembre 2012 18:10
  • C'est tout le code ? Il "tourne sans fin" ? Comme il n'y a pas de boucle visible, le debugger n'attends pas que vous lui demandiez de passer à l'instruction suivante ? Sinon il y a sans doute une boucle dans le code (comme dans votre code original ?)

    Sinon cf plus haut (il y a bien une inversion des colonnes).


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    jeudi 22 novembre 2012 18:12
  • Bonjour,

    j'ai testé la procédure dans mon code C# et après plusieurs ajustages, cela fonctionne parfaitement.

    Merci pour votre aide et vos conseils ! J'étais parti sur une mauvaise piste en voulant tester le nombre de cours présents et le nombre de cours à modifier.

    Comme vous me l'avez conseillé, un simple DELETE suivi d'un INSERT fait parfaitement l’affaire.

    Je vous donne la version finale de la procédure stocké en espérant que cela puisse en aider d'autres.

    Pour rappel elle fait les 3 actions suivantes:

    1 - Modifier les informations (déjà existantes) d'un élève

    2 - Supprime les cours qu'il ne suit plus.

    3 - Ajoute les nouveaux cours auxquels il s'inscrit

    USE [Ecole]
    GO
    
    /****** Object:  StoredProcedure [dbo].[sp_EditerEleve]    Script Date: 11/24/2012 17:12:53 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    -- =============================================
    CREATE PROCEDURE [dbo].[sp_EditerEleve]
    	@idEleve int,
    	@nom varchar(50),
    	@prenom varchar(50),
    	@age int,
    	@idstatut int,
    	@annee int,
    	@actif int,
    	@cheminPhoto varchar(350),
    	@CoursSuivis CoursType READONLY
    	
    AS
    BEGIN
    
    SELECT * FROM @CoursSuivis
    
    IF @age BETWEEN 12 AND 20
    		UPDATE Eleve 
    		SET Nom=@nom , 
    		Prenom=@prenom, 
    		IDStatut=@idstatut, 
    		Annee=@annee,
    		Age=@age,  
    		Actif=@actif, 
    		Photo=@cheminPhoto
    		WHERE IDEleve = @idEleve 
    	
    
    	DELETE FROM EleveCours 
    	WHERE IDEleve=@idEleve 
    	AND IDCours 
    	NOT IN (SELECT CoursID FROM @CoursSuivis)
    	
    	SELECT CAST(@@ROWCOUNT AS VARCHAR)+' suppressions.'
    
    	INSERT INTO EleveCours(IDCours,IDEleve,CoursNom) 
    	SELECT CoursId, @idEleve,CoursNom 
    	FROM @CoursSuivis 
    	WHERE CoursId  NOT IN (SELECT IDCours FROM EleveCours WHERE IDEleve=@idEleve)
    	SELECT CAST(@@ROWCOUNT AS VARCHAR)+' ajouts.'
    
    END

    Encore un grand merci !

    • Marqué comme réponse paintbox00 samedi 24 novembre 2012 16:16
    samedi 24 novembre 2012 16:16