-- =============================================
-- Author:    Zsiga Attila
-- Create date: 2016.10.21.
-- Description:  Az input órarendi órákhoz tartozó tanárokat adja vissza.
-- =============================================
DROP PROCEDURE IF EXISTS sp_GetOrarendiOrakhozTartozoTanarok
GO

CREATE PROCEDURE sp_GetOrarendiOrakhozTartozoTanarok
  @pXml XML,
  @pTanevId INT
AS
BEGIN
  SET NOCOUNT ON;
  SET XACT_ABORT ON

  SELECT 
    sor.value('.', 'int') AS OrarendiOraId  
  INTO 
    #TEMPORARENDIORAID
  FROM 
    @pXml.nodes('/OrarendiOrak/IdLista/Id') as sorok(sor)

  --TEMP TABLES
  SELECT * INTO #TEMPFELHASZNALO FROM T_FELHASZNALO 
  WHERE TOROLT = 'F' AND C_TANEVID = @pTanevId
  ---------------
  SELECT * INTO #TEMPORARENDIORA FROM T_ORARENDIORA
  WHERE TOROLT = 'F' AND C_TANEVID = @pTanevId
  ---------------
  SELECT * INTO #TEMPORARENDIORAK 
  FROM #TEMPORARENDIORAID
  LEFT JOIN #TEMPORARENDIORA ON #TEMPORARENDIORA.ID = #TEMPORARENDIORAID.OrarendiOraId
  ---------------
  SELECT 
    * 
  INTO 
    #TEMPERINTETTTANAROK 
  FROM 
    #TEMPFELHASZNALO 
  WHERE 
    ID IN (SELECT C_TANARID FROM #TEMPORARENDIORAK)
  ---------------
  
  SELECT DISTINCT
    ID TanarId,
    C_NYOMTATASINEV Nev
  FROM
    #TEMPERINTETTTANAROK

  DROP TABLE #TEMPORARENDIORAID
  DROP TABLE #TEMPFELHASZNALO
  DROP TABLE #TEMPORARENDIORA
  DROP TABLE #TEMPORARENDIORAK
  DROP TABLE #TEMPERINTETTTANAROK
END
GO