Need help on a SProc
From: Don (anonymous_at_discussions.microsoft.com)
Date: 06/03/04
- Next message: Geoff N. Hiten: "Re: T-sql and jobs"
- Previous message: Michael: "T-sql and jobs"
- In reply to: Stan Sainte-Rose: "Need help on a SProc"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 3 Jun 2004 13:55:32 -0700
Hey mon Stan,
pété ton affaire
>-----Original Message-----
>Hi,
>
>I need some help on a Stored Proc..
>I think I can optimize it.
>I thank you in advance for your help
>
>Here's the stored proc
>
>CREATE PROCEDURE T_SELECT_QUESTION_BY_QUESTIONNAIRE_NEW
>(
> @ID_QUESTIONNAIRE INT,
> @ID_PERE int
>)
>AS
>
>SET NOCOUNT ON
>CREATE TABLE #TBL (
>IDCYCLE int,
>CYCLE_FR varchar(100),
>CYCLE_EN varchar(100),
>CYCLE_ES varchar(100)
>)
>
>
>CREATE TABLE #RS2 (
>IDCYCLE int,
>CYCLE_FR varchar(100),
>CYCLE_EN varchar(100),
>CYCLE_ES varchar(100),
>IDRS2 int
>
>)
>
>CREATE TABLE #RS3 (
>IDCYCLE int,
>CYCLE_FR varchar(100),
>CYCLE_EN varchar(100),
>CYCLE_ES varchar(100),
>IDRS2 int,
>IDRS3 int
>)
>
>
>CREATE TABLE #RESULTAT (
>
>IDCHAPITRE int,
>NomChapitre_FR varchar(100),
>NomChapitre_EN varchar(100),
>NomChapitre_ES varchar(100),
>BONNE_REPONSE char(1),
>id_famille int,
>Cycle_FR varchar(500),
>Cycle_EN varchar(500),
>Cycle_ES varchar(500),
>ID_QUESTION int,
>Question_FR text,
>Question_EN text,
>Question_ES text
>)
>
>
>
>
>INSERT INTO #TBL (idcycle,cycle_fr,cycle_en,cycle_es)
>select ID_FAMILLE,LIBELLE_FR,LIBELLE_EN,LIBELLE_ES from
T_FAMILLES
>where FK_ID_QUESTIONNAIRE=@ID_QUESTIONNAIRE
>and FK_ID_PERE=@ID_PERE
>and (ID_FAMILLE in
> (select FK_ID_FAMILLE from T_QUESTIONS
> )
>or ID_FAMILLE in
> (select FK_ID_PERE from T_FAMILLES
> where ID_FAMILLE in
> (select FK_ID_FAMILLE from T_QUESTIONS
> )
> or ID_FAMILLE in
> (select FK_ID_PERE from T_FAMILLES
> where ID_FAMILLE in
> (select FK_ID_FAMILLE from T_QUESTIONS
> )
> )
> ))
>
>
>INSERT INTO #RS2
(idcycle,cycle_fr,cycle_en,cycle_es,idrs2)
>select idcycle,cycle_fr,cycle_en,cycle_es,id_famille from
#TBL
>LEFT OUTER JOIN T_FAMILLES ON
T_FAMILLES.FK_ID_PERE=#TBL.idcycle
>WHERE FK_ID_QUESTIONNAIRE=@ID_QUESTIONNAIRE
>and FK_ID_PERE=#TBL.idcycle
>and (ID_FAMILLE in
> (select FK_ID_FAMILLE from T_QUESTIONS
> )
>or ID_FAMILLE in
> (select FK_ID_PERE from T_FAMILLES
> where ID_FAMILLE in
> (select FK_ID_FAMILLE from T_QUESTIONS
> )
> or ID_FAMILLE in
> (select FK_ID_PERE from T_FAMILLES
> where ID_FAMILLE in
> (select FK_ID_FAMILLE from T_QUESTIONS
> )
> )
> ))
>
>
>INSERT INTO #RS3
(idcycle,cycle_fr,cycle_en,cycle_es,idrs2,idrs3)
>select
idcycle,cycle_fr,cycle_en,cycle_es,idrs2,id_famille from
#RS2
>LEFT OUTER JOIN T_FAMILLES ON
T_FAMILLES.FK_ID_PERE=#RS2.idrs2
>WHERE FK_ID_QUESTIONNAIRE=@ID_QUESTIONNAIRE
>and FK_ID_PERE=#RS2.idrs2
>and (ID_FAMILLE in
> (select FK_ID_FAMILLE from T_QUESTIONS
> )
>or ID_FAMILLE in
> (select FK_ID_PERE from T_FAMILLES
> where ID_FAMILLE in
> (select FK_ID_FAMILLE from T_QUESTIONS
> )
> or ID_FAMILLE in
> (select FK_ID_PERE from T_FAMILLES
> where ID_FAMILLE in
> (select FK_ID_FAMILLE from T_QUESTIONS
> )
> )
> ))
>
>
>
>-- SELECT * from #rs3
>
>
>
>
>INSERT #RESULTAT(
>IDCHAPITRE,
>NomChapitre_FR,
>NomChapitre_EN,
>NomChapitre_ES,
>BONNE_REPONSE,
>id_famille,
>Cycle_FR,
>Cycle_EN,
>Cycle_ES,
>ID_QUESTION,
>Question_FR,
>Question_EN,
>Question_ES
>)
>
>
>select
>idcycle AS IDCHAPITRE,
>cycle_fr AS NomChapitre_FR,
>cycle_en AS NomChapitre_EN,
>cycle_es AS NomChapitre_ES,
>BONNE_REPONSE,
>
>famille1.id_famille AS IDCYCLE,
>famille1.libelle_fr AS Cycle_FR,
>famille1.libelle_en AS Cycle_EN,
>famille1.libelle_es AS Cycle_ES,
>ID_QUESTION,
>T_QUESTIONS.libelle_fr AS Question_FR,
>T_QUESTIONS.libelle_en AS Question_EN,
>T_QUESTIONS.libelle_es AS Question_ES
>
>FROM #RS3 LEFT OUTER JOIN T_QUESTIONS ON
>t_questions.fk_id_famille=#RS3.idrs3
>
>inner join t_familles as famille3 on
>t_questions.fk_id_famille=famille3.id_famille
>inner join t_familles as famille2 on
famille3.fk_id_pere=famille2.id_famille
>inner join t_familles as famille1 on
famille2.fk_id_pere=famille1.id_famille
>
>
>INSERT #RESULTAT(
>IDCHAPITRE,
>NomChapitre_FR,
>NomChapitre_EN,
>NomChapitre_ES,
>BONNE_REPONSE,
>id_famille,
>Cycle_FR,
>Cycle_EN,
>Cycle_ES,
>ID_QUESTION,
>Question_FR,
>Question_EN,
>Question_ES
>)
>
>select
>idcycle AS IDCHAPITRE,
>cycle_fr AS NomChapitre_FR,
>cycle_en AS NomChapitre_EN,
>cycle_es AS NomChapitre_ES,
>BONNE_REPONSE,
>
>famille1.id_famille AS IDCYCLE,
>famille1.libelle_fr AS Cycle_FR,
>famille1.libelle_en AS Cycle_EN,
>famille1.libelle_es AS Cycle_ES,
>ID_QUESTION,
>T_QUESTIONS.libelle_fr AS Question_FR,
>T_QUESTIONS.libelle_en AS Question_EN,
>T_QUESTIONS.libelle_es AS Question_ES
>
>
>
>
>FROM #RS2 LEFT OUTER JOIN T_QUESTIONS ON
>t_questions.fk_id_famille=#RS2.idrs2
>
>inner join t_familles as famille3 on
>t_questions.fk_id_famille=famille3.id_famille
>inner join t_familles as famille2 on
famille3.fk_id_pere=famille2.id_famille
>inner join t_familles as famille1 on
famille2.fk_id_pere=famille1.id_famille
>
>
>SET NOCOUNT OFF
>SELECT
>
>IDCHAPITRE,
>NomChapitre_FR,
>NomChapitre_EN,
>NomChapitre_ES,
>ID_FAMILLE,
>Cycle_FR,
>Cycle_EN,
>Cycle_ES,
>ID_QUESTION,
>Question_FR,
>Question_EN,
>Question_ES,
>BONNE_REPONSE
>
> FROM #Resultat ORDER BY IDCHAPITRE,ID_QUESTION
>GO
>
>
>
>
>
>
>
>.
>
- Next message: Geoff N. Hiten: "Re: T-sql and jobs"
- Previous message: Michael: "T-sql and jobs"
- In reply to: Stan Sainte-Rose: "Need help on a SProc"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
