Skip Navigation LinksХранимые-процедуры

Хранимые процедуры на ТSQL

USE [F101]
GO
/****** Object:  StoredProcedure [dbo].[NeraspNagr]    Script Date: 06/04/2012 06:05:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[NeraspNagr] --(@Prep varchar(50),@id tinyint)
AS
BEGIN
SELECT id_101,Дисциплина,Семестр,Группа,[Вид работы],[Количество часов в неделю],[Количество часов за семестр]
FROM T101
WHERE T101.id_101 not in (
                SELECT id_101
                FROM NagrPrep)
END
 
USE [F101]
GO
/****** Object:  StoredProcedure [dbo].[T101_Fill]    Script Date: 06/04/2012 06:12:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[T101_Fill]
AS
BEGIN
DBCC CHECKIDENT (T101,RESEED,0)
INSERT INTO T101
SELECT Discip.Дисциплина,Groups.Специальность,Groups.Курс,NagrDis.Семестр,Groups.Группа,Groups.[Численность группы],TypeWork.[Вид работы],NagrWork.[Количество часов],(NagrWork.[Количество часов]*18)
FROM Discip INNER JOIN NagrDis ON Discip.id_d=NagrDis.id_d INNER JOIN NagrD_Gr ON NagrDis.id_d=NagrD_Gr.id_d AND NagrDis.Семестр=NagrD_Gr.Семестр INNER JOIN Groups ON NagrD_Gr.id_g=Groups.id_g INNER JOIN TypeWork ON NagrDis.id_d=TypeWork.id_d AND NagrDis.Семестр=TypeWork.Семестр AND Groups.id_g=TypeWork.id_g INNER JOIN NagrWork ON TypeWork.id_w=NagrWork.id_w
END
BEGIN
INSERT INTO T101
SELECT Дисциплина,Специальность,Курс,Семестр,Группа,[Численность группы],[Вид работы],[Количество часов в неделю],[Количество часов за семестр]
FROM T101 --INNER JOIN Groups ON T101.Группа=Groups.Группа
WHERE T101.[Вид работы]='ЛР' AND T101.[Численность группы]>12
END
 
USE [F101]
GO
/****** Object:  StoredProcedure [dbo].[Clr]    Script Date: 06/04/2012 06:22:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Clr]
AS
BEGIN
DELETE FROM NagrPrep
DELETE FROM T101
DELETE FROM Discip
DELETE FROM Groups
DELETE FROM NagrD_Gr
DELETE FROM NagrDis
DELETE FROM NagrWork
DELETE FROM TypeWork
DELETE FROM OtchDis
DELETE FROM TypeOtch
DELETE FROM Otchet
DELETE FROM OtchPrep
DBCC CHECKIDENT (Discip,RESEED,0)
DBCC CHECKIDENT (Groups,RESEED,0)
DBCC CHECKIDENT (OtchDis,RESEED,0)
DBCC CHECKIDENT (TypeWork,RESEED,0)
END
 
USE [F101]
GO
/****** Object:  StoredProcedure [dbo].[XImp]    Script Date: 06/04/2012 06:23:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[XImp] (@Dis nvarchar(50),@Spec nchar(10),@Course tinyint,@Sem tinyint,@Gro nvarchar(50),@GCount tinyint,@WType nchar(10),@HCount float,@Z float,@E float,@K float)
AS
BEGIN
IF @Dis NOT IN(SELECT Дисциплина FROM Discip WHERE Дисциплина=@Dis)
                INSERT INTO Discip VALUES(@Dis)
IF @Gro NOT IN(SELECT Группа FROM Groups WHERE Группа=@Gro)
                INSERT INTO Groups VALUES(@Gro,@GCount,@Course,@Spec)
IF NOT EXISTS (SELECT NagrDis.id_d,NagrDis.Семестр FROM NagrDis INNER JOIN Discip ON NagrDis.id_d=Discip.id_d WHERE Discip.Дисциплина=@Dis AND NagrDis.Семестр=@Sem)
                INSERT INTO NagrDis VALUES((SELECT id_d FROM Discip WHERE Дисциплина=@Dis),@Sem)
IF NOT EXISTS (SELECT NagrD_Gr.id_d,NagrD_Gr.Семестр,NagrD_Gr.id_g FROM NagrD_Gr INNER JOIN NagrDis ON NagrD_Gr.id_d=NagrDis.id_d AND NagrD_Gr.Семестр=NagrDis.Семестр INNER JOIN Discip ON Discip.id_d=NagrDis.id_d INNER JOIN Groups ON NagrD_Gr.id_g=Groups.id_g WHERE Discip.Дисциплина=@Dis AND NagrDis.Семестр=@Sem AND Groups.Группа=@Gro)
                INSERT INTO NagrD_Gr SELECT Discip.id_d,Семестр,Groups.id_g FROM NagrDis INNER JOIN Discip ON Discip.id_d=NagrDis.id_d,Groups WHERE Discip.Дисциплина=@Dis AND NagrDis.Семестр=@Sem AND Groups.Группа=@Gro
IF NOT EXISTS (SELECT TypeWork.id_d,TypeWork.Семестр,TypeWork.id_g,TypeWork.[Вид работы] FROM TypeWork INNER JOIN Groups ON TypeWork.id_g=Groups.id_g INNER JOIN NagrDis ON TypeWork.Семестр=NagrDis.Семестр AND TypeWork.id_d=NagrDis.id_d INNER JOIN Discip ON NagrDis.id_d=Discip.id_d WHERE Discip.Дисциплина=@Dis AND NagrDis.Семестр=@Sem AND Groups.Группа=@Gro AND TypeWork.[Вид работы]=@WType)
                INSERT INTO TypeWork VALUES((SELECT id_d FROM Discip WHERE Discip.Дисциплина=@Dis),(SELECT Семестр FROM NagrDis WHERE NagrDis.Семестр=@Sem),(SELECT id_g FROM Groups WHERE Группа=@Gro),@WType)
INSERT INTO NagrWork VALUES((SELECT id_w FROM TypeWork INNER JOIN NagrDis ON TypeWork.id_d=NagrDis.id_d AND TypeWork.Семестр=NagrDis.Семестр INNER JOIN Discip ON NagrDis.id_d=Discip.id_d INNER JOIN Groups ON TypeWork.id_g=Groups.id_g WHERE TypeWork.[Вид работы]=@WType AND Discip.Дисциплина=@Dis AND NagrDis.Семестр=@Sem AND Groups.Группа=@Gro),@HCount)
IF NOT EXISTS (SELECT OtchDis.id_d,OtchDis.Семестр FROM OtchDis INNER JOIN NagrDis ON OtchDis.id_d=NagrDis.id_d AND OtchDis.Семестр=NagrDis.Семестр INNER JOIN Discip ON NagrDis.id_d=Discip.id_d WHERE Discip.Дисциплина=@Dis AND NagrDis.Семестр=@Sem)
                INSERT INTO OtchDis SELECT Discip.id_d,NagrDis.Семестр,Groups.id_g FROM NagrDis INNER JOIN Discip ON Discip.id_d=NagrDis.id_d INNER JOIN NagrD_Gr ON NagrDis.id_d=NagrD_Gr.id_d AND NagrDis.Семестр=NagrD_Gr.Семестр INNER JOIN Groups ON NagrD_Gr.id_g=Groups.id_g WHERE Discip.Дисциплина=@Dis AND NagrDis.Семестр=@Sem AND Groups.Группа=@Gro
IF NOT EXISTS (SELECT TypeOtch.id_o FROM TypeOtch INNER JOIN OtchDis ON TypeOtch.id_o=OtchDis.id_o)
                INSERT INTO TypeOtch VALUES((SELECT id_o FROM OtchDis),@Z,@E,@K)
END
 
USE [F101]
GO
/****** Object:  StoredProcedure [dbo].[Otchet_Fill]    Script Date: 06/04/2012 06:24:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Otchet_Fill]
AS
BEGIN
DBCC CHECKIDENT (T101,RESEED,0)
INSERT INTO Otchet
SELECT Discip.Дисциплина,NagrDis.Семестр,Groups.Группа,TypeOtch.Зачеты,'0',TypeOtch.Экзамены,'0',TypeOtch.[КР/КП],'0'
FROM Discip INNER JOIN NagrDis ON Discip.id_d=NagrDis.id_d INNER JOIN OtchDis ON NagrDis.id_d=OtchDis.id_d AND NagrDis.Семестр=OtchDis.Семестр INNER JOIN TypeOtch ON OtchDis.id_o=TypeOtch.id_o INNER JOIN Groups ON Groups.id_g=OtchDis.id_g
END
 
USE [F101]
GO
/****** Object:  StoredProcedure [dbo].[OtchetAdd]    Script Date: 06/04/2012 06:24:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[OtchetAdd] (@id tinyint,@Z float,@E float,@K float)
AS
BEGIN
                INSERT INTO OtchPrep
                SELECT id_ot,Дисциплина,Семестр,Группа,@Z AS Зачеты,@E AS Экзамены,@K AS [КР/КП] FROM Otchet WHERE id_ot=@id
                UPDATE Otchet SET Зачеты=Зачеты-@Z,Экзамены=Экзамены-@E,[КР/КП]=[КР/КП]-@K WHERE Otchet.id_ot=@id
END
 
USE [F101]
GO
/****** Object:  StoredProcedure [dbo].[OtchetDel]    Script Date: 06/04/2012 06:24:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[OtchetDel] (@id tinyint,@Z float,@E float,@K float)
AS
BEGIN
                UPDATE Otchet SET Зачеты=Зачеты+@Z,Экзамены=Экзамены+@E,[КР/КП]=[КР/КП]+@K WHERE Otchet.id_ot=@id
                DELETE FROM OtchPrep WHERE id_ot=@id
END​​