Приложение 7 –процедуры добавления

CREATEPROCPHydrantInsert

@NetBIT, --0-кольцевая 1-тупиковая

@CalibINT,

@IDWSource INT

AS

INSERT INTO Hydrant

(Net, Caliber, IDWaterSource)

VALUES

(@Net, @Calib, @IDWSource )

CREATE PROC PPondInsert

@Size INT,

@IDWSource INT

AS

INSERT INTO Pond

(Size, IDWaterSource)

VALUES

(@Size, @IDWSource )

CREATE PROC PStreetInsert

@NStreet VARCHAR(40)

AS

INSERT INTO Street

(NameStreet)

VALUES

(@NStreet)

CREATE PROC PToOrganozationInsert

@NameTypeOfOrganization VARCHAR(50)

AS

INSERT INTO TypeOfOrganization

(NameTypeOfOrganization)

VALUES

(@NameTypeOfOrganization )

CREATE PROC POrganizationInsert

@NameOrganization VARCHAR(50),

@Dissolution DATE,

@IDTypeOfOrganization INT

AS

INSERT INTO Organization

(NameOrganization, Dissolution, IDTypeOfOrganization)

VALUES

(@NameOrganization, @Dissolution, @IDTypeOfOrganization )

CREATE PROC PWaterSourceInsert

@NameWaterSource VARCHAR(10),

@Building VARCHAR(10),

@IDStreet INT,

@IDOrganization INT

AS

INSERT INTO WaterSource

(NameWaterSource, Building, IDStreet, IDOrganization)

VALUES

(@NameWaterSource, @Building, @IDStreet,@IDOrganization )

CREATE PROC PInspectionInsert

@DateInspection DATE,

@IDWaterSource INT

AS

INSERT INTO Inspection

(DateInspection, IDWaterSource)

VALUES

(@DateInspection, @IDWaterSource)

CREATE PROC PDefectInsert

@NameDefect VARCHAR(100),

@RepairTime INT,

@Serviceability BIT,

@IDTypeOfOrganization INT

AS

INSERT INTO Defect

(NameDefect, RepairTime,Serviceability,IDTypeOfOrganization)

VALUES

(@NameDefect, @RepairTime,@Serviceability,@IDTypeOfOrganization)

CREATE PROC PLetterInsert

@DataSend DATE,

@LetterNumber INT,

@DateNextInspection DATE,

@TypeOfLetter VARCHAR(30),

@IDOrganization INT,

@IDInspection INT

AS

INSERT INTO Letter

(DataSend, LetterNumber,DateNextInspection,TypeOfLetter, IDOrganization, IDInspection )

VALUES

(@DataSend, @LetterNumber,@DateNextInspection,@TypeOfLetter, @IDOrganization, @IDInspection)

CREATE PROC PTestDefectInsert

@IDDefect INT,

@IDInspection INT,

@IDLetter INT

AS

INSERT INTO TestDefect

(IDDefect, IDInspection,IDLetter)

VALUES

(@IDDefect, @IDInspection,@IDLetter)

Приложение 8 – процедуры обновления

CREATEPROCPHydrantUpdate

@NNetBIT,

@NCalib INT,

@IDWSource INT

AS

UPDATE Hydrant

SET Net=@NNet, Caliber=@NCalib

WHERE IDWaterSource=@IDWSource

CREATE PROC PPondUpdate

@Size INT,

@IDWSource INT

AS

UPDATE Pond

SET Size=@Size

WHERE IDWaterSource=@IDWSource

CREATE PROC PStreetUpdate

@NStreet VARCHAR(40),

@IDStr INT

AS

UPDATE Street

SET NameStreet=@NStreet

WHERE IDStreet=@IDStr

CREATE PROC PToOrganozationUpdate

@IDTypeOfOrganization INT,

@NameTypeOfOrganization VARCHAR(50)

AS

UPDATE TypeOfOrganization

SET NameTypeOfOrganization=@NameTypeOfOrganization

WHERE IDTypeOfOrganization=@IDTypeOfOrganization

CREATE PROC POrganizationUpdate

@NameOrganization VARCHAR(50),

@Dissolution DATE,

@IDTypeOfOrganization INT,

@IDOrg INT

AS

UPDATE Organization

SET NameOrganization=@NameOrganization, Dissolution=@Dissolution, IDTypeOfOrganization=@IDTypeOfOrganization

WHERE IDOrganization=@IDOrg

CREATE PROC PWaterSourceUpdate

@NameWaterSource VARCHAR(10),

@Building VARCHAR(10),

@IDStreet INT,

@IDOrganization INT,

@IDWS INT

AS

UPDATE WaterSource

SET NameWaterSource=@NameWaterSource, Building=@Building, IDStreet=@IDStreet, IDOrganization=@IDOrganization

WHERE IDWaterSource=@IDWS

CREATE PROC PInspectionUpdate

@DateInspection DATE,

@IDWaterSource INT,

@IDIn INT

AS

UPDATE Inspection

SET DateInspection=@DateInspection,IDWaterSource=@IDWaterSource

WHERE IDInspection=@IDIn

CREATE PROC PDefectUpdate

@NameDefect VARCHAR(100),

@RepairTime INT,

@Serviceability BIT,

@IDTypeOfOrganization INT,

@IDDef INT

AS

UPDATE Defect

SET NameDefect=@NameDefect, RepairTime=@RepairTime, Serviceability=@Serviceability, IDTypeOfOrganization=@IDTypeOfOrganization

WHERE IDDefect=@IDDef

CREATE PROC PLetterUpdate

@DataSend DATE,

@LetterNumber INT,

@DateNextInspection DATE,

@TypeOfLetter VARCHAR(30),

@IDOrganization INT,

@IDInspection INT,

@IDLet INT

AS UPDATE Letter

SET DataSend=@DataSend, LetterNumber=@LetterNumber, DateNextInspection=@DateNextInspection,

TypeOfLetter=@TypeOfLetter, IDOrganization=@IDOrganization, IDInspection=@IDInspection

WHERE IDLetter=@IDLet

CREATE PROC PTestDefectUpdate

@IDDefect INT,

@IDInspection INT,

@IDLetter INT,

@NIDDefect INT,

@NIDInspection INT,

@NIDLetter INT

AS UPDATE TestDefect

SET IDDefect=@NIDDefect, IDInspection=@NIDInspection, IDLetter=@NIDLetter

WHERE (IDDefect=@IDDefect AND IDInspection=@IDInspection AND IDLetter=@IDLetter)