ERROR:
Incorrect syntax near the keyword 'PROCEDURE'.
Incorrect syntax near 'LIMIT'.
Incorrect syntax near 'LIMIT'.
Incorrect syntax near '/'.
--sql
DELIMITER
CREATE PROCEDURE Sp_FinalSinavi()
BEGIN
-- Summation of building capacities in the buildings table
UPDATE Binalar
SET Kapasite = (
SELECT SUM(Salonlar.Kapasite)
FROM Salonlar
WHERE Salonlar.BinaID = Binalar.BinaID
GROUP BY Salonlar.BinaID
)
WHERE Kapasite IS NULL;
-- Filling the Exam Buildings table according to the number of students
INSERT INTO SinavBinalari (BinaID)
SELECT BinaID
FROM Binalar
ORDER BY Kapasite DESC
LIMIT (
SELECT SUM(OgrenciSayisi)
FROM OgrenciSayilari
);
-- Filling the Exam Halls table according to the number of students
INSERT INTO [dbo].[SinavSalonlari] (SalonID)
SELECT SalonID
FROM Salonlar
WHERE BinaID IN (
SELECT BinaID
FROM SinavBinalari
)
ORDER BY Kapasite DESC
LIMIT (
SELECT SUM(OgrenciSayisi)
FROM OgrenciSayilari
);
-- Fill in the quotas table
TRUNCATE TABLE [dbo].[Kontenjanlar];
--Adding a Building Examiner quota
INSERT INTO Kontenjanlar (KontenjanID, GorevAdı)
SELECT 'Bina Sınav Sorumlusu', COUNT(*)
FROM SinavBinalari;
--Adding a Building Examiner quota
INSERT INTO Kontenjanlar (KontenjanID, GorevAdı)
SELECT 'Salon Başkanı', COUNT(*)
FROM SinavSalonlari;
-- Add supervisor quota
INSERT INTO Kontenjanlar (KontenjanID, GorevAdı)
SELECT 'Gözetmen', COUNT(*)
FROM SinavSalonlari
GROUP BY SalonID
HAVING COUNT(*) > 0;
END //
DELIMITER ;