Files

60 lines
1.2 KiB
SQL

CREATE TABLE `VolunteerPositions` (
`VolunteerPositionId` INT NOT NULL AUTO_INCREMENT,
`VolunteerId` INT NOT NULL,
`PositionId` INT NOT NULL,
`GeographyId` INT NOT NULL,
`Open` TINYINT NOT NULL DEFAULT '1',
`Assigned` TINYINT NOT NULL DEFAULT '0',
PRIMARY KEY (`VolunteerPositionId`),
INDEX `Index_Position` (`PositionId` ASC),
INDEX `Index_Geography` (`GeographyId` ASC),
INDEX `Index_Open` (`Open` ASC),
INDEX `Index_Volunteer` (`VolunteerId` ASC))
#
DROP procedure IF EXISTS `CreateVolunteerPosition`
#
CREATE PROCEDURE `CreateVolunteerPosition`(
IN volunteerId INT,
IN positionId INT,
IN geographyId INT
)
BEGIN
INSERT INTO VolunteerPositions (VolunteerId,PositionId,GeographyId,Open,Assigned)
VALUES (volunteerId,positionId,geographyId,1,0);
SELECT LAST_INSERT_ID() AS Identity;
END
#
DROP procedure IF EXISTS `CloseVolunteerPosition`
#
CREATE PROCEDURE `CloseVolunteerPosition`(
volunteerPositionId INTEGER,
assigned TINYINT
)
BEGIN
UPDATE VolunteerPositions
SET VolunteerPositions.Open=0,VolunteerRoles.Assigned=assigned
WHERE VolunteerPositions.VolunteerPositionId=volunteerPositionId AND VolunteerPositions.Open=1;
SELECT ROW_COUNT() AS RowsUpdated;
END