72 lines
1.8 KiB
Transact-SQL
72 lines
1.8 KiB
Transact-SQL
GO
|
|
/****** Object: StoredProcedure [dbo].[sp_GetTermek] Script Date: 2016.02.19. 10:53:07 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
IF OBJECT_ID('[dbo].[sp_GetTermek]') IS NOT NULL
|
|
BEGIN
|
|
DROP PROCEDURE [dbo].[sp_GetTermek]
|
|
END
|
|
GO
|
|
|
|
-- =============================================
|
|
-- Author: Hoffmann Zsolt
|
|
-- Create date: 2016-02-19
|
|
-- Description: Termek lekérdezés
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[sp_GetTermek]
|
|
@FoglalkozasID int,
|
|
@OsztCsopID int,
|
|
@TanevID int
|
|
AS
|
|
BEGIN
|
|
|
|
SET NOCOUNT ON;
|
|
|
|
WITH q (ID,Nev,Tipus) AS
|
|
(
|
|
SELECT
|
|
terem.ID,
|
|
terem.C_NEV Nev,
|
|
'1' Tipus
|
|
FROM T_TEREM_OSSZES terem
|
|
LEFT JOIN T_TEREM_FOGLALKOZAS ON T_TEREM_FOGLALKOZAS.C_TEREMID = terem.ID
|
|
LEFT JOIN T_FOGLALKOZAS_OSSZES foglalkozas ON foglalkozas.ID = T_TEREM_FOGLALKOZAS.C_FOGLALKOZASID
|
|
WHERE terem.TOROLT = 'F' AND
|
|
terem.C_TANEVID = @TanevID AND
|
|
foglalkozas.TOROLT = 'F' AND
|
|
foglalkozas.ID = @FoglalkozasID
|
|
EXCEPT
|
|
(
|
|
SELECT
|
|
terem.ID,
|
|
terem.C_NEV Nev,
|
|
'1' Tipus
|
|
FROM T_TEREM_OSSZES terem
|
|
INNER JOIN (SELECT C_TEREMID FROM T_OSZTALYCSOPORT_OSSZES WHERE TOROLT = 'F' AND ID = @OsztCsopID) ocs ON ocs.C_TEREMID = terem.ID
|
|
WHERE terem.TOROLT = 'F' AND terem.C_TANEVID = @TanevID)
|
|
UNION all
|
|
SELECT
|
|
terem.ID,
|
|
terem.C_NEV Nev,
|
|
'2' Tipus
|
|
FROM T_TEREM_OSSZES terem
|
|
INNER JOIN
|
|
(SELECT C_TEREMID FROM T_OSZTALYCSOPORT_OSSZES WHERE TOROLT = 'F' AND ID = @OsztCsopID) ocs ON ocs.C_TEREMID = terem.ID
|
|
WHERE terem.TOROLT = 'F' AND terem.C_TANEVID = @TanevID
|
|
)
|
|
SELECT ID,Nev,Tipus FROM q
|
|
UNION
|
|
SELECT
|
|
terem.ID,
|
|
terem.C_NEV Nev,
|
|
'3' Tipus
|
|
FROM T_TEREM_OSSZES terem
|
|
WHERE terem.TOROLT = 'F' AND terem.C_TANEVID = @TanevID
|
|
AND terem.ID NOT IN (SELECT ID FROM q)
|
|
|
|
END
|