GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Zsiga Attila
-- Create date: 2016.10.21.
-- Description:	Az input órarendi órákhoz tartozó tanárokat adja vissza.
-- =============================================
IF OBJECT_ID('sp_GetOrarendiOrakhozTartozoTanarok') IS NOT NULL BEGIN
  DROP PROCEDURE sp_GetOrarendiOrakhozTartozoTanarok
END
GO

CREATE PROCEDURE sp_GetOrarendiOrakhozTartozoTanarok
	@pXml XML,
	@pTanevId INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	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