rendered paste body/* This SQL DDL script was generated by Microsoft Visual Studio (Release Date: LOCAL BUILD). *//* Driver Used : Microsoft Visual Studio - Microsoft SQL Server Driver. *//* Document : C:\Documents and Settings\Ryan Corrigal\My Documents\Computer Systems Technology\IST410\Database\StAlbertFoodBank.vsd. *//* Time Created: March 11, 20508 2:58 PM. *//* Operation : From Visio Generate Wizard. *//* Connected data source : No connection. *//* Connected server : No connection. *//* Connected database : Not applicable. */SET QUOTED_IDENTIFIER ONgo/* Create StAlbertFoodBank database. */use master godrop database "StAlbertFoodBank" gocreate database "StAlbertFoodBank" gouse "StAlbertFoodBank" go/* Create new table "ClientTypeInformation". *//* "ClientTypeInformation" : Table of ClientTypeInformation *//* "ClientTypeInformationId" : ClientTypeInformationId identifies ClientTypeInformation *//* "ClientId" : ClientId partly identifies ClientTypeInformation *//* "ClientTypeId" : ClientTypeId is of ClientTypeInformation */ create table "ClientTypeInformation" ( "ClientTypeInformationId" int identity(1,1) not null, "ClientId" int not null, "ClientTypeId" int not null) goalter table "ClientTypeInformation" add constraint "ClientTypeInformation_PK" primary key ("ClientTypeInformationId") go/* Create new table "ClientTypes". *//* "ClientTypes" : Table of ClientTypes *//* "ClientTypeId" : ClientTypeId identifies ClientTypes *//* "Description" : Description is of ClientTypes */ create table "ClientTypes" ( "ClientTypeId" int identity(1,1) not null, "Description" varchar(30) null) goalter table "ClientTypes" add constraint "ClientTypes_PK" primary key ("ClientTypeId") go/* Create new table "Donees". *//* "Donees" : Table of Donees *//* "DoneeId" : DoneeId identifies Donees *//* "DoneeName" : DoneeName is of Donees */ create table "Donees" ( "DoneeId" int identity(1,1) not null, "DoneeName" varchar(30) null) goalter table "Donees" add constraint "Donees_PK" primary key ("DoneeId") go/* Create new table "OutgoingDonations". *//* "OutgoingDonations" : Table of OutgoingDonations *//* "OutgoingDonationId" : OutgoingDonationId identifies OutgoingDonations *//* "OutgoingDonationDate" : OutgoingDonationDate is of OutgoingDonations *//* "WeightDonated" : WeightDonated is of OutgoingDonations *//* "DoneeId" : DoneeId is of OutgoingDonations *//* "MoneyDonated" : MoneyDonated is of OutgoingDonations */ create table "OutgoingDonations" ( "OutgoingDonationId" int identity(1,1) not null, "OutgoingDonationDate" smalldatetime null, "WeightDonated" decimal(11,2) null, "DoneeId" int null, "MoneyDonated" decimal(11,2) null) goalter table "OutgoingDonations" add constraint "OutgoingDonations_PK" primary key ("OutgoingDonationId") go/* Create new table "ReferralAgencyCategories". *//* "ReferralAgencyCategories" : Table of ReferralAgencyCategories *//* "ReferralAgencyCategoryId" : ReferralAgencyCategoryId identifies ReferralAgencyCategories *//* "Description" : Description is of ReferralAgencyCategories */ create table "ReferralAgencyCategories" ( "ReferralAgencyCategoryId" int identity(1,1) not null, "Description" varchar(30) not null) goalter table "ReferralAgencyCategories" add constraint "ReferralAgencyCategories_PK" primary key ("ReferralAgencyCategoryId") go/* Create new table "VolunteerTypes". *//* "VolunteerTypes" : Table of VolunteerTypes */ create table "VolunteerTypes" ( "VolunteerTypeId" int identity(1,1) not null, "Description" varchar(255) not null) ON 'PRIMARY' goalter table "VolunteerTypes" add constraint "VolunteerTypes_PK" primary key clustered ("VolunteerTypeId") go/* Create new table "Volunteers". *//* "Volunteers" : Table of Volunteers */ create table "Volunteers" ( "VolunteerId" int identity(1,1) not null, "LocationId" int not null, "VolunteerTypeId" int null, "Gender" char(1) not null, "FirstName" varchar(50) not null, "LastName" varchar(50) not null, "MiddleName" varchar(50) null, "Address" varchar(255) not null, "Phone1" char(10) not null, "Phone2" char(10) null, "Birthdate" smalldatetime not null, "StartDate" smalldatetime not null, "EmailAddress" varchar(255) null, "PostalCode" char(6) not null, "EmergencyContactName" varchar(255) not null, "EmergencyContactPhone" char(10) not null) ON 'PRIMARY' goalter table "Volunteers" add constraint "Volunteers_PK" primary key clustered ("VolunteerId") go/* Create new table "VolunteerHours". *//* "VolunteerHours" : Table of VolunteerHours */ create table "VolunteerHours" ( "VolunteerHourId" int identity(1,1) not null, "VolunteerId" int not null, "Date" smalldatetime not null, "HoursWorked" int not null) ON 'PRIMARY' goalter table "VolunteerHours" add constraint "VolunteerHours_PK" primary key clustered ("VolunteerHourId") go/* Create new table "TotalAssets". *//* "TotalAssets" : Table of TotalAssets */ create table "TotalAssets" ( "TotalAssetId" int not null, "TotalFood" decimal(11,2) not null, "TotalMoney" decimal(11,2) not null) ON 'PRIMARY' goalter table "TotalAssets" add constraint "TotalAssets_PK" primary key clustered ("TotalAssetId") go/* Create new table "Referrals". *//* "Referrals" : Table of Referrals */ create table "Referrals" ( "ReferralId" int identity(1,1) not null, "ReferralAgencyId" int not null, "ReferralDetails" varchar(255) null, "NumberOfAdults" smallint not null, "NumberOfChildren" smallint null, "ReferralDate" smalldatetime not null, "ClientName" varchar(255) null) ON 'PRIMARY' goalter table "Referrals" add constraint "Referrals_PK" primary key clustered ("ReferralId") go/* Create new table "ReferralAgencies". *//* "ReferralAgencies" : Table of ReferralAgencies *//* "ReferralAgencyCategoryId" : ReferralAgencyCategoryId is of ReferralAgencies */ create table "ReferralAgencies" ( "ReferralAgencyId" int identity(1,1) not null, "LocationId" int null, "Name" varchar(255) not null, "Phone1" char(10) null, "Phone2" char(10) null, "Address" varchar(255) null, "Contact" varchar(255) null, "EmailAddress" varchar(255) null, "ReferralAgencyCategoryId" int null) ON 'PRIMARY' goalter table "ReferralAgencies" add constraint "ReferralAgencies_PK" primary key clustered ("ReferralAgencyId") go/* Create new table "Locations". *//* "Locations" : Table of Locations */ create table "Locations" ( "LocationId" int identity(1,1) not null, "CityTown" varchar(50) not null, "Province" varchar(50) not null, "Country" varchar(50) not null, "JurisdictionDescription" varchar(255) null) ON 'PRIMARY' goalter table "Locations" add constraint "Locations_PK" primary key clustered ("LocationId") go/* Create new table "Households". *//* "Households" : Table of Households */ create table "Households" ( "HouseholdId" int identity(1,1) not null, "PostalCode" varchar(6) not null, "Address" varchar(255) not null, "CommunityId" int not null) ON 'PRIMARY' goalter table "Households" add constraint "Households_PK" primary key clustered ("HouseholdId") go/* Create new table "FoodHamperStates". *//* "FoodHamperStates" : Table of FoodHamperStates */ create table "FoodHamperStates" ( "StateId" int not null, "Description" varchar(255) null) ON 'PRIMARY' goalter table "FoodHamperStates" add constraint "FoodHamperStates_PK" primary key clustered ("StateId") go/* Create new table "FoodHampers". *//* "FoodHampers" : Table of FoodHampers */ create table "FoodHampers" ( "FoodHamperId" int identity(1,1) not null, "Weight" decimal(11,2) not null, "PickupDate" smalldatetime null, "PickupPerson" varchar(255) null, "ReferralId" int null, "FamilyId" int null) ON 'PRIMARY' goalter table "FoodHampers" add constraint "FoodHampers_PK" primary key clustered ("FoodHamperId") go/* Create new table "FamilyStates". *//* "FamilyStates" : Table of FamilyStates */ create table "FamilyStates" ( "FamilyStateId" int identity(1,1) not null, "Description" varchar(255) null) ON 'PRIMARY' goalter table "FamilyStates" add constraint "FamilyStates_PK" primary key clustered ("FamilyStateId") go/* Create new table "Families". *//* "Families" : Table of Families */ create table "Families" ( "FamilyId" int identity(1,1) not null, "HeadOfFamilyClientId" int null, "FamilyComment" varchar(255) null, "HouseholdId" int null, "FamilyStateId" int not null, "RedFlag" bit not null, "Phone1" char(10) null, "Phone2" char(10) null) ON 'PRIMARY' goalter table "Families" add constraint "Families_PK" primary key clustered ("FamilyId") go/* Create new table "DonorTypes". *//* "DonorTypes" : Table of DonorTypes */ create table "DonorTypes" ( "DonorTypeId" int identity(1,1) not null, "Description" varchar(255) null) ON 'PRIMARY' goalter table "DonorTypes" add constraint "DonorTypes_PK" primary key clustered ("DonorTypeId") go/* Create new table "Donors". *//* "Donors" : Table of Donors */ create table "Donors" ( "DonorId" int identity(1,1) not null, "LocationId" int null, "TypeId" int null, "Name" varchar(255) null, "Phone1" char(10) null, "Phone2" char(10) null, "Address" varchar(255) null, "EmailAddress" varchar(255) null, "Contact" varchar(255) null) ON 'PRIMARY' goalter table "Donors" add constraint "Donors_PK" primary key clustered ("DonorId") go/* Create new table "Donations". *//* "Donations" : Table of Donations */ create table "Donations" ( "DonationId" int identity(1,1) not null, "DonorId" int not null, "DonationDate" smalldatetime not null, "WeightDonated" decimal(11,2) null, "MoneyDonated" decimal(11,2) null) ON 'PRIMARY' goalter table "Donations" add constraint "Donations_PK" primary key clustered ("DonationId") go/* Create new table "Communities". *//* "Communities" : Table of Communities */ create table "Communities" ( "CommunityId" int identity(1,1) not null, "LocationId" int not null, "Name" varchar(255) not null, "JurisdictionDescription" varchar(255) null) ON 'PRIMARY' goalter table "Communities" add constraint "Communities_PK" primary key clustered ("CommunityId") go/* Create new table "Clients". *//* "Clients" : Table of Clients */ create table "Clients" ( "ClientId" int identity(1,1) not null, "HealthInfo" varchar(255) null, "Gender" char(1) not null, "FirstName" varchar(50) not null, "LastName" varchar(50) not null, "MiddleName" varchar(50) null, "Birthdate" smalldatetime not null, "EmailAddress" varchar(255) null, "AhcNumber" varchar(255) not null, "FamilyId" int not null) ON 'PRIMARY' goalter table "Clients" add constraint "Clients_PK" primary key clustered ("ClientId") go/* Create new table "ClientHouseholdHistory". *//* "ClientHouseholdHistory" : Table of ClientHouseholdHistory */ create table "ClientHouseholdHistory" ( "ClientHouseholdHistoryId" int identity(1,1) not null, "ClientId" int not null, "HouseholdId" int null, "DateMovedOut" char(10) null) ON 'PRIMARY' goalter table "ClientHouseholdHistory" add constraint "ClientHouseholdHistory_PK" primary key clustered ("ClientHouseholdHistoryId") go/* Add foreign key constraints to table "ClientTypeInformation". */alter table "ClientTypeInformation" add constraint "Clients_ClientTypeInformation_FK1" foreign key ( "ClientId") references "Clients" ( "ClientId") on update no action on delete no action goalter table "ClientTypeInformation" add constraint "ClientTypes_ClientTypeInformation_FK1" foreign key ( "ClientTypeId") references "ClientTypes" ( "ClientTypeId") on update no action on delete no action go/* Add foreign key constraints to table "OutgoingDonations". */alter table "OutgoingDonations" add constraint "Donees_OutgoingDonations_FK1" foreign key ( "DoneeId") references "Donees" ( "DoneeId") on update no action on delete no action go/* Add foreign key constraints to table "Volunteers". */alter table "Volunteers" add constraint "Locations_Volunteers_FK1" foreign key ( "LocationId") references "Locations" ( "LocationId") on update no action on delete no action goalter table "Volunteers" add constraint "VolunteerTypes_Volunteers_FK1" foreign key ( "VolunteerTypeId") references "VolunteerTypes" ( "VolunteerTypeId") on update no action on delete no action go/* Add foreign key constraints to table "VolunteerHours". */alter table "VolunteerHours" add constraint "Volunteers_VolunteerHours_FK1" foreign key ( "VolunteerId") references "Volunteers" ( "VolunteerId") on update no action on delete no action go/* Add foreign key constraints to table "Referrals". */alter table "Referrals" add constraint "ReferralAgencies_Referrals_FK1" foreign key ( "ReferralAgencyId") references "ReferralAgencies" ( "ReferralAgencyId") on update no action on delete no action go/* Add foreign key constraints to table "ReferralAgencies". */alter table "ReferralAgencies" add constraint "Locations_ReferralAgencies_FK1" foreign key ( "LocationId") references "Locations" ( "LocationId") on update no action on delete no action goalter table "ReferralAgencies" add constraint "ReferralAgencyCategories_ReferralAgencies_FK1" foreign key ( "ReferralAgencyCategoryId") references "ReferralAgencyCategories" ( "ReferralAgencyCategoryId") on update no action on delete no action go/* Add foreign key constraints to table "Households". */alter table "Households" add constraint "Communities_Households_FK1" foreign key ( "CommunityId") references "Communities" ( "CommunityId") on update no action on delete no action go/* Add foreign key constraints to table "FoodHampers". */alter table "FoodHampers" add constraint "Families_FoodHampers_FK1" foreign key ( "FamilyId") references "Families" ( "FamilyId") on update no action on delete no action goalter table "FoodHampers" add constraint "Referrals_FoodHampers_FK1" foreign key ( "ReferralId") references "Referrals" ( "ReferralId") on update no action on delete no action go/* Add foreign key constraints to table "Families". */alter table "Families" add constraint "Clients_Families_FK1" foreign key ( "HeadOfFamilyClientId") references "Clients" ( "ClientId") on update no action on delete no action goalter table "Families" add constraint "FamilyStates_Families_FK1" foreign key ( "FamilyStateId") references "FamilyStates" ( "FamilyStateId") on update no action on delete no action goalter table "Families" add constraint "Households_Families_FK1" foreign key ( "HouseholdId") references "Households" ( "HouseholdId") on update no action on delete no action go/* Add foreign key constraints to table "Donors". */alter table "Donors" add constraint "DonorTypes_Donors_FK1" foreign key ( "TypeId") references "DonorTypes" ( "DonorTypeId") on update no action on delete no action goalter table "Donors" add constraint "Locations_Donors_FK1" foreign key ( "LocationId") references "Locations" ( "LocationId") on update no action on delete no action go/* Add foreign key constraints to table "Donations". */alter table "Donations" add constraint "Donors_Donations_FK1" foreign key ( "DonorId") references "Donors" ( "DonorId") on update no action on delete no action go/* Add foreign key constraints to table "Communities". */alter table "Communities" add constraint "Locations_Communities_FK1" foreign key ( "LocationId") references "Locations" ( "LocationId") on update no action on delete no action go/* Add foreign key constraints to table "Clients". */alter table "Clients" add constraint "Families_Clients_FK1" foreign key ( "FamilyId") references "Families" ( "FamilyId") on update no action on delete no action go/* Add foreign key constraints to table "ClientHouseholdHistory". */alter table "ClientHouseholdHistory" add constraint "Clients_ClientHouseholdHistory_FK1" foreign key ( "ClientId") references "Clients" ( "ClientId") on update no action on delete no action goalter table "ClientHouseholdHistory" add constraint "Households_ClientHouseholdHistory_FK1" foreign key ( "HouseholdId") references "Households" ( "HouseholdId") on update no action on delete no action go/* This is the end of the Microsoft Visual Studio generated SQL DDL script. */create procedure AddClientHouseholdHistory(@ClientId int,@HouseholdId int,@DateMovedOut char)as--Trim Strings--Please Remove the last comma above--Set Nullsinsert into ClientHouseholdHistory(ClientId, HouseholdId, DateMovedOut)values(@ClientId, @HouseholdId, @DateMovedOut)select @@identityreturngocreate procedure DeleteClientHouseholdHistoryById(@ClientHouseholdHistoryId int)asdelete from ClientHouseholdHistorywhere ClientHouseholdHistoryId = @ClientHouseholdHistoryIdreturngocreate procedure GetClientHouseholdHistoryasselect ClientHouseholdHistoryId, ClientId, HouseholdId, DateMovedOutfrom ClientHouseholdHistoryreturngocreate procedure GetClientHouseholdHistoryById(@ClientHouseholdHistoryId int)asselect ClientHouseholdHistoryId, ClientId, HouseholdId, DateMovedOutfrom ClientHouseholdHistorywhere ClientHouseholdHistoryId = @ClientHouseholdHistoryIdreturngocreate procedure UpdateClientHouseholdHistory(@ClientHouseholdHistoryId int,@ClientId int,@HouseholdId int,@DateMovedOut char)as--Trim Strings--Set Nullsupdate ClientHouseholdHistoryset ClientId = @ClientId, HouseholdId = @HouseholdId, DateMovedOut = @DateMovedOutwhere ClientHouseholdHistoryId = @ClientHouseholdHistoryIdreturngocreate procedure AddClient(@HealthInfo varchar(255),@Gender char,@FirstName varchar(255),@LastName varchar(255),@MiddleName varchar(255),@Birthdate smalldatetime,@EmailAddress varchar(255),@AhcNumber varchar(255),@FamilyId int)as--Trim StringsSET @HealthInfo = ltrim(rtrim(@HealthInfo))SET @FirstName = ltrim(rtrim(@FirstName))SET @LastName = ltrim(rtrim(@LastName))SET @MiddleName = ltrim(rtrim(@MiddleName))SET @EmailAddress = ltrim(rtrim(@EmailAddress))SET @AhcNumber = ltrim(rtrim(@AhcNumber))--Please Remove the last comma above--Set Nullsif @HealthInfo = '' set @HealthInfo = NULLif @FirstName = '' set @FirstName = NULLif @LastName = '' set @LastName = NULLif @MiddleName = '' set @MiddleName = NULLif @EmailAddress = '' set @EmailAddress = NULLif @AhcNumber = '' set @AhcNumber = NULLinsert into Clients(HealthInfo, Gender, FirstName, LastName, MiddleName, Birthdate, EmailAddress, AhcNumber, FamilyId)values(@HealthInfo, @Gender, @FirstName, @LastName, @MiddleName, @Birthdate, @EmailAddress, @AhcNumber, @FamilyId)select @@identityreturngocreate procedure DeleteClientById(@ClientId int)asdelete from Clientswhere ClientId = @ClientIdreturngocreate procedure GetClientsasselect ClientId, HealthInfo, Gender, FirstName, LastName, MiddleName, Birthdate, EmailAddress, AhcNumber, FamilyIdfrom Clientsreturngocreate procedure GetClientById(@ClientId int)asselect ClientId, HealthInfo, Gender, FirstName, LastName, MiddleName, Birthdate, EmailAddress, AhcNumber, FamilyIdfrom Clientswhere ClientId = @ClientIdreturngocreate procedure UpdateClient(@ClientId int,@HealthInfo varchar(255),@Gender char,@FirstName varchar(255),@LastName varchar(255),@MiddleName varchar(255),@Birthdate smalldatetime,@EmailAddress varchar(255),@AhcNumber varchar(255),@FamilyId int)as--Trim StringsSET @HealthInfo = ltrim(rtrim(@HealthInfo))SET @FirstName = ltrim(rtrim(@FirstName))SET @LastName = ltrim(rtrim(@LastName))SET @MiddleName = ltrim(rtrim(@MiddleName))SET @EmailAddress = ltrim(rtrim(@EmailAddress))SET @AhcNumber = ltrim(rtrim(@AhcNumber))--Set Nullsif @HealthInfo = '' set @HealthInfo = NULLif @FirstName = '' set @FirstName = NULLif @LastName = '' set @LastName = NULLif @MiddleName = '' set @MiddleName = NULLif @EmailAddress = '' set @EmailAddress = NULLif @AhcNumber = '' set @AhcNumber = NULLupdate Clientsset HealthInfo = @HealthInfo, Gender = @Gender, FirstName = @FirstName, LastName = @LastName, MiddleName = @MiddleName, Birthdate = @Birthdate, EmailAddress = @EmailAddress, AhcNumber = @AhcNumber, FamilyId = @FamilyIdwhere ClientId = @ClientIdreturngocreate procedure AddClientTypeInformation(@ClientId int,@ClientTypeId int)as--Trim Strings--Please Remove the last comma above--Set Nullsinsert into ClientTypeInformation(ClientId, ClientTypeId)values(@ClientId, @ClientTypeId)select @@identityreturngocreate procedure DeleteClientTypeInformationById(@ClientTypeInformationId int)asdelete from ClientTypeInformationwhere ClientTypeInformationId = @ClientTypeInformationIdreturngocreate procedure GetClientTypeInformationasselect ClientTypeInformationId, ClientId, ClientTypeIdfrom ClientTypeInformationreturngocreate procedure GetClientTypeInformationById(@ClientTypeInformationId int)asselect ClientTypeInformationId, ClientId, ClientTypeIdfrom ClientTypeInformationwhere ClientTypeInformationId = @ClientTypeInformationIdreturngocreate procedure UpdateClientTypeInformation(@ClientTypeInformationId int,@ClientId int,@ClientTypeId int)as--Trim Strings--Set Nullsupdate ClientTypeInformationset ClientId = @ClientId, ClientTypeId = @ClientTypeIdwhere ClientTypeInformationId = @ClientTypeInformationIdreturngocreate procedure AddClientType(@Description varchar(255))as--Trim StringsSET @Description = ltrim(rtrim(@Description))--Please Remove the last comma above--Set Nullsif @Description = '' set @Description = NULLinsert into ClientTypes(Description)values(@Description)select @@identityreturngocreate procedure DeleteClientTypeById(@ClientTypeId int)asdelete from ClientTypeswhere ClientTypeId = @ClientTypeIdreturngocreate procedure GetClientTypesasselect ClientTypeId, Descriptionfrom ClientTypesreturngocreate procedure GetClientTypeById(@ClientTypeId int)asselect ClientTypeId, Descriptionfrom ClientTypeswhere ClientTypeId = @ClientTypeIdreturngocreate procedure UpdateClientType(@ClientTypeId int,@Description varchar(255))as--Trim StringsSET @Description = ltrim(rtrim(@Description))--Set Nullsif @Description = '' set @Description = NULLupdate ClientTypesset Description = @Descriptionwhere ClientTypeId = @ClientTypeIdreturngocreate procedure AddCommunity(@LocationId int,@Name varchar(255),@JurisdictionDescription varchar(255))as--Trim StringsSET @Name = ltrim(rtrim(@Name))SET @JurisdictionDescription = ltrim(rtrim(@JurisdictionDescription))--Please Remove the last comma above--Set Nullsif @Name = '' set @Name = NULLif @JurisdictionDescription = '' set @JurisdictionDescription = NULLinsert into Communities(LocationId, Name, JurisdictionDescription)values(@LocationId, @Name, @JurisdictionDescription)select @@identityreturngocreate procedure DeleteCommunityById(@CommunityId int)asdelete from Communitieswhere CommunityId = @CommunityIdreturngocreate procedure GetCommunitiesasselect CommunityId, LocationId, Name, JurisdictionDescriptionfrom Communitiesreturngocreate procedure GetCommunityById(@CommunityId int)asselect CommunityId, LocationId, Name, JurisdictionDescriptionfrom Communitieswhere CommunityId = @CommunityIdreturngocreate procedure UpdateCommunity(@CommunityId int,@LocationId int,@Name varchar(255),@JurisdictionDescription varchar(255))as--Trim StringsSET @Name = ltrim(rtrim(@Name))SET @JurisdictionDescription = ltrim(rtrim(@JurisdictionDescription))--Set Nullsif @Name = '' set @Name = NULLif @JurisdictionDescription = '' set @JurisdictionDescription = NULLupdate Communitiesset LocationId = @LocationId, Name = @Name, JurisdictionDescription = @JurisdictionDescriptionwhere CommunityId = @CommunityIdreturngocreate procedure AddDonation(@DonorId int,@DonationDate smalldatetime,@WeightDonated decimal,@MoneyDonated decimal)as--Trim Strings--Please Remove the last comma above--Set Nullsinsert into Donations(DonorId, DonationDate, WeightDonated, MoneyDonated)values(@DonorId, @DonationDate, @WeightDonated, @MoneyDonated)select @@identityreturngocreate procedure DeleteDonationById(@DonationId int)asdelete from Donationswhere DonationId = @DonationIdreturngocreate procedure GetDonationsasselect DonationId, DonorId, DonationDate, WeightDonated, MoneyDonatedfrom Donationsreturngocreate procedure GetDonationById(@DonationId int)asselect DonationId, DonorId, DonationDate, WeightDonated, MoneyDonatedfrom Donationswhere DonationId = @DonationIdreturngocreate procedure UpdateDonation(@DonationId int,@DonorId int,@DonationDate smalldatetime,@WeightDonated decimal,@MoneyDonated decimal)as--Trim Strings--Set Nullsupdate Donationsset DonorId = @DonorId, DonationDate = @DonationDate, WeightDonated = @WeightDonated, MoneyDonated = @MoneyDonatedwhere DonationId = @DonationIdreturngocreate procedure AddDonee(@DoneeName varchar(255))as--Trim StringsSET @DoneeName = ltrim(rtrim(@DoneeName))--Please Remove the last comma above--Set Nullsif @DoneeName = '' set @DoneeName = NULLinsert into Donees(DoneeName)values(@DoneeName)select @@identityreturngocreate procedure DeleteDoneeById(@DoneeId int)asdelete from Doneeswhere DoneeId = @DoneeIdreturngocreate procedure GetDoneesasselect DoneeId, DoneeNamefrom Doneesreturngocreate procedure GetDoneeById(@DoneeId int)asselect DoneeId, DoneeNamefrom Doneeswhere DoneeId = @DoneeIdreturngocreate procedure UpdateDonee(@DoneeId int,@DoneeName varchar(255))as--Trim StringsSET @DoneeName = ltrim(rtrim(@DoneeName))--Set Nullsif @DoneeName = '' set @DoneeName = NULLupdate Doneesset DoneeName = @DoneeNamewhere DoneeId = @DoneeIdreturngocreate procedure AddDonor(@LocationId int,@TypeId int,@Name varchar(255),@Phone1 char,@Phone2 char,@Address varchar(255),@EmailAddress varchar(255),@Contact varchar(255))as--Trim StringsSET @Name = ltrim(rtrim(@Name))SET @Address = ltrim(rtrim(@Address))SET @EmailAddress = ltrim(rtrim(@EmailAddress))SET @Contact = ltrim(rtrim(@Contact))--Please Remove the last comma above--Set Nullsif @Name = '' set @Name = NULLif @Address = '' set @Address = NULLif @EmailAddress = '' set @EmailAddress = NULLif @Contact = '' set @Contact = NULLinsert into Donors(LocationId, TypeId, Name, Phone1, Phone2, Address, EmailAddress, Contact)values(@LocationId, @TypeId, @Name, @Phone1, @Phone2, @Address, @EmailAddress, @Contact)select @@identityreturngocreate procedure DeleteDonorById(@DonorId int)asdelete from Donorswhere DonorId = @DonorIdreturngocreate procedure GetDonorsasselect DonorId, LocationId, TypeId, Name, Phone1, Phone2, Address, EmailAddress, Contactfrom Donorsreturngocreate procedure GetDonorById(@DonorId int)asselect DonorId, LocationId, TypeId, Name, Phone1, Phone2, Address, EmailAddress, Contactfrom Donorswhere DonorId = @DonorIdreturngocreate procedure UpdateDonor(@DonorId int,@LocationId int,@TypeId int,@Name varchar(255),@Phone1 char,@Phone2 char,@Address varchar(255),@EmailAddress varchar(255),@Contact varchar(255))as--Trim StringsSET @Name = ltrim(rtrim(@Name))SET @Address = ltrim(rtrim(@Address))SET @EmailAddress = ltrim(rtrim(@EmailAddress))SET @Contact = ltrim(rtrim(@Contact))--Set Nullsif @Name = '' set @Name = NULLif @Address = '' set @Address = NULLif @EmailAddress = '' set @EmailAddress = NULLif @Contact = '' set @Contact = NULLupdate Donorsset LocationId = @LocationId, TypeId = @TypeId, Name = @Name, Phone1 = @Phone1, Phone2 = @Phone2, Address = @Address, EmailAddress = @EmailAddress, Contact = @Contactwhere DonorId = @DonorIdreturngocreate procedure AddDonorType(@Description varchar(255))as--Trim StringsSET @Description = ltrim(rtrim(@Description))--Please Remove the last comma above--Set Nullsif @Description = '' set @Description = NULLinsert into DonorTypes(Description)values(@Description)select @@identityreturngocreate procedure DeleteDonorTypeById(@DonorTypeId int)asdelete from DonorTypeswhere DonorTypeId = @DonorTypeIdreturngocreate procedure GetDonorTypesasselect DonorTypeId, Descriptionfrom DonorTypesreturngocreate procedure GetDonorTypeById(@DonorTypeId int)asselect DonorTypeId, Descriptionfrom DonorTypeswhere DonorTypeId = @DonorTypeIdreturngocreate procedure UpdateDonorType(@DonorTypeId int,@Description varchar(255))as--Trim StringsSET @Description = ltrim(rtrim(@Description))--Set Nullsif @Description = '' set @Description = NULLupdate DonorTypesset Description = @Descriptionwhere DonorTypeId = @DonorTypeIdreturngocreate procedure AddFamily(@HeadOfFamilyClientId int,@FamilyComment varchar(255),@HouseholdId int,@FamilyStateId int,@RedFlag bit,@Phone1 char,@Phone2 char)as--Trim StringsSET @FamilyComment = ltrim(rtrim(@FamilyComment))--Please Remove the last comma above--Set Nullsif @FamilyComment = '' set @FamilyComment = NULLinsert into Families(HeadOfFamilyClientId, FamilyComment, HouseholdId, FamilyStateId, RedFlag, Phone1, Phone2)values(@HeadOfFamilyClientId, @FamilyComment, @HouseholdId, @FamilyStateId, @RedFlag, @Phone1, @Phone2)select @@identityreturngocreate procedure DeleteFamilyById(@FamilyId int)asdelete from Familieswhere FamilyId = @FamilyIdreturngocreate procedure GetFamiliesasselect FamilyId, HeadOfFamilyClientId, FamilyComment, HouseholdId, FamilyStateId, RedFlag, Phone1, Phone2from Familiesreturngocreate procedure GetFamilyById(@FamilyId int)asselect FamilyId, HeadOfFamilyClientId, FamilyComment, HouseholdId, FamilyStateId, RedFlag, Phone1, Phone2from Familieswhere FamilyId = @FamilyIdreturngocreate procedure UpdateFamily(@FamilyId int,@HeadOfFamilyClientId int,@FamilyComment varchar(255),@HouseholdId int,@FamilyStateId int,@RedFlag bit,@Phone1 char,@Phone2 char)as--Trim StringsSET @FamilyComment = ltrim(rtrim(@FamilyComment))--Set Nullsif @FamilyComment = '' set @FamilyComment = NULLupdate Familiesset HeadOfFamilyClientId = @HeadOfFamilyClientId, FamilyComment = @FamilyComment, HouseholdId = @HouseholdId, FamilyStateId = @FamilyStateId, RedFlag = @RedFlag, Phone1 = @Phone1, Phone2 = @Phone2where FamilyId = @FamilyIdreturngocreate procedure AddFamilyState(@Description varchar(255))as--Trim StringsSET @Description = ltrim(rtrim(@Description))--Please Remove the last comma above--Set Nullsif @Description = '' set @Description = NULLinsert into FamilyStates(Description)values(@Description)select @@identityreturngocreate procedure DeleteFamilyStateById(@FamilyStateId int)asdelete from FamilyStateswhere FamilyStateId = @FamilyStateIdreturngocreate procedure GetFamilyStatesasselect FamilyStateId, Descriptionfrom FamilyStatesreturngocreate procedure GetFamilyStateById(@FamilyStateId int)asselect FamilyStateId, Descriptionfrom FamilyStateswhere FamilyStateId = @FamilyStateIdreturngocreate procedure UpdateFamilyState(@FamilyStateId int,@Description varchar(255))as--Trim StringsSET @Description = ltrim(rtrim(@Description))--Set Nullsif @Description = '' set @Description = NULLupdate FamilyStatesset Description = @Descriptionwhere FamilyStateId = @FamilyStateIdreturngocreate procedure AddFoodHamper(@Weight decimal,@PickupDate smalldatetime,@PickupPerson varchar(255),@ReferralId int,@FamilyId int)as--Trim StringsSET @PickupPerson = ltrim(rtrim(@PickupPerson))--Please Remove the last comma above--Set Nullsif @PickupPerson = '' set @PickupPerson = NULLinsert into FoodHampers(Weight, PickupDate, PickupPerson, ReferralId, FamilyId)values(@Weight, @PickupDate, @PickupPerson, @ReferralId, @FamilyId)select @@identityreturngocreate procedure DeleteFoodHamperById(@FoodHamperId int)asdelete from FoodHamperswhere FoodHamperId = @FoodHamperIdreturngocreate procedure GetFoodHampersasselect FoodHamperId, Weight, PickupDate, PickupPerson, ReferralId, FamilyIdfrom FoodHampersreturngocreate procedure GetFoodHamperById(@FoodHamperId int)asselect FoodHamperId, Weight, PickupDate, PickupPerson, ReferralId, FamilyIdfrom FoodHamperswhere FoodHamperId = @FoodHamperIdreturngocreate procedure UpdateFoodHamper(@FoodHamperId int,@Weight decimal,@PickupDate smalldatetime,@PickupPerson varchar(255),@ReferralId int,@FamilyId int)as--Trim StringsSET @PickupPerson = ltrim(rtrim(@PickupPerson))--Set Nullsif @PickupPerson = '' set @PickupPerson = NULLupdate FoodHampersset Weight = @Weight, PickupDate = @PickupDate, PickupPerson = @PickupPerson, ReferralId = @ReferralId, FamilyId = @FamilyIdwhere FoodHamperId = @FoodHamperIdreturngocreate procedure AddFoodHamperState(@Description varchar(255))as--Trim StringsSET @Description = ltrim(rtrim(@Description))--Please Remove the last comma above--Set Nullsif @Description = '' set @Description = NULLinsert into FoodHamperStates(Description)values(@Description)select @@identityreturngocreate procedure DeleteFoodHamperStateById(@StateId int)asdelete from FoodHamperStateswhere StateId = @StateIdreturngocreate procedure GetFoodHamperStatesasselect StateId, Descriptionfrom FoodHamperStatesreturngocreate procedure GetFoodHamperStateById(@StateId int)asselect StateId, Descriptionfrom FoodHamperStateswhere StateId = @StateIdreturngocreate procedure UpdateFoodHamperState(@StateId int,@Description varchar(255))as--Trim StringsSET @Description = ltrim(rtrim(@Description))--Set Nullsif @Description = '' set @Description = NULLupdate FoodHamperStatesset Description = @Descriptionwhere StateId = @StateIdreturngocreate procedure AddHousehold(@PostalCode varchar(255),@Address varchar(255),@CommunityId int)as--Trim StringsSET @PostalCode = ltrim(rtrim(@PostalCode))SET @Address = ltrim(rtrim(@Address))--Please Remove the last comma above--Set Nullsif @PostalCode = '' set @PostalCode = NULLif @Address = '' set @Address = NULLinsert into Households(PostalCode, Address, CommunityId)values(@PostalCode, @Address, @CommunityId)select @@identityreturngocreate procedure DeleteHouseholdById(@HouseholdId int)asdelete from Householdswhere HouseholdId = @HouseholdIdreturngocreate procedure GetHouseholdsasselect HouseholdId, PostalCode, Address, CommunityIdfrom Householdsreturngocreate procedure GetHouseholdById(@HouseholdId int)asselect HouseholdId, PostalCode, Address, CommunityIdfrom Householdswhere HouseholdId = @HouseholdIdreturngocreate procedure UpdateHousehold(@HouseholdId int,@PostalCode varchar(255),@Address varchar(255),@CommunityId int)as--Trim StringsSET @PostalCode = ltrim(rtrim(@PostalCode))SET @Address = ltrim(rtrim(@Address))--Set Nullsif @PostalCode = '' set @PostalCode = NULLif @Address = '' set @Address = NULLupdate Householdsset PostalCode = @PostalCode, Address = @Address, CommunityId = @CommunityIdwhere HouseholdId = @HouseholdIdreturngocreate procedure AddLocation(@CityTown varchar(255),@Province varchar(255),@Country varchar(255),@JurisdictionDescription varchar(255))as--Trim StringsSET @CityTown = ltrim(rtrim(@CityTown))SET @Province = ltrim(rtrim(@Province))SET @Country = ltrim(rtrim(@Country))SET @JurisdictionDescription = ltrim(rtrim(@JurisdictionDescription))--Please Remove the last comma above--Set Nullsif @CityTown = '' set @CityTown = NULLif @Province = '' set @Province = NULLif @Country = '' set @Country = NULLif @JurisdictionDescription = '' set @JurisdictionDescription = NULLinsert into Locations(CityTown, Province, Country, JurisdictionDescription)values(@CityTown, @Province, @Country, @JurisdictionDescription)select @@identityreturngocreate procedure DeleteLocationById(@LocationId int)asdelete from Locationswhere LocationId = @LocationIdreturngocreate procedure GetLocationsasselect LocationId, CityTown, Province, Country, JurisdictionDescriptionfrom Locationsreturngocreate procedure GetLocationById(@LocationId int)asselect LocationId, CityTown, Province, Country, JurisdictionDescriptionfrom Locationswhere LocationId = @LocationIdreturngocreate procedure UpdateLocation(@LocationId int,@CityTown varchar(255),@Province varchar(255),@Country varchar(255),@JurisdictionDescription varchar(255))as--Trim StringsSET @CityTown = ltrim(rtrim(@CityTown))SET @Province = ltrim(rtrim(@Province))SET @Country = ltrim(rtrim(@Country))SET @JurisdictionDescription = ltrim(rtrim(@JurisdictionDescription))--Set Nullsif @CityTown = '' set @CityTown = NULLif @Province = '' set @Province = NULLif @Country = '' set @Country = NULLif @JurisdictionDescription = '' set @JurisdictionDescription = NULLupdate Locationsset CityTown = @CityTown, Province = @Province, Country = @Country, JurisdictionDescription = @JurisdictionDescriptionwhere LocationId = @LocationIdreturngocreate procedure AddOutgoingDonation(@OutgoingDonationDate smalldatetime,@WeightDonated decimal,@DoneeId int,@MoneyDonated decimal)as--Trim Strings--Please Remove the last comma above--Set Nullsinsert into OutgoingDonations(OutgoingDonationDate, WeightDonated, DoneeId, MoneyDonated)values(@OutgoingDonationDate, @WeightDonated, @DoneeId, @MoneyDonated)select @@identityreturngocreate procedure DeleteOutgoingDonationById(@OutgoingDonationId int)asdelete from OutgoingDonationswhere OutgoingDonationId = @OutgoingDonationIdreturngocreate procedure GetOutgoingDonationsasselect OutgoingDonationId, OutgoingDonationDate, WeightDonated, DoneeId, MoneyDonatedfrom OutgoingDonationsreturngocreate procedure GetOutgoingDonationById(@OutgoingDonationId int)asselect OutgoingDonationId, OutgoingDonationDate, WeightDonated, DoneeId, MoneyDonatedfrom OutgoingDonationswhere OutgoingDonationId = @OutgoingDonationIdreturngocreate procedure UpdateOutgoingDonation(@OutgoingDonationId int,@OutgoingDonationDate smalldatetime,@WeightDonated decimal,@DoneeId int,@MoneyDonated decimal)as--Trim Strings--Set Nullsupdate OutgoingDonationsset OutgoingDonationDate = @OutgoingDonationDate, WeightDonated = @WeightDonated, DoneeId = @DoneeId, MoneyDonated = @MoneyDonatedwhere OutgoingDonationId = @OutgoingDonationIdreturngocreate procedure AddReferralAgency(@LocationId int,@Name varchar(255),@Phone1 char,@Phone2 char,@Address varchar(255),@Contact varchar(255),@EmailAddress varchar(255),@ReferralAgencyCategoryId int)as--Trim StringsSET @Name = ltrim(rtrim(@Name))SET @Address = ltrim(rtrim(@Address))SET @Contact = ltrim(rtrim(@Contact))SET @EmailAddress = ltrim(rtrim(@EmailAddress))--Please Remove the last comma above--Set Nullsif @Name = '' set @Name = NULLif @Address = '' set @Address = NULLif @Contact = '' set @Contact = NULLif @EmailAddress = '' set @EmailAddress = NULLinsert into ReferralAgencies(LocationId, Name, Phone1, Phone2, Address, Contact, EmailAddress, ReferralAgencyCategoryId)values(@LocationId, @Name, @Phone1, @Phone2, @Address, @Contact, @EmailAddress, @ReferralAgencyCategoryId)select @@identityreturngocreate procedure DeleteReferralAgencyById(@ReferralAgencyId int)asdelete from ReferralAgencieswhere ReferralAgencyId = @ReferralAgencyIdreturngocreate procedure GetReferralAgenciesasselect ReferralAgencyId, LocationId, Name, Phone1, Phone2, Address, Contact, EmailAddress, ReferralAgencyCategoryIdfrom ReferralAgenciesreturngocreate procedure GetReferralAgencyById(@ReferralAgencyId int)asselect ReferralAgencyId, LocationId, Name, Phone1, Phone2, Address, Contact, EmailAddress, ReferralAgencyCategoryIdfrom ReferralAgencieswhere ReferralAgencyId = @ReferralAgencyIdreturngocreate procedure UpdateReferralAgency(@ReferralAgencyId int,@LocationId int,@Name varchar(255),@Phone1 char,@Phone2 char,@Address varchar(255),@Contact varchar(255),@EmailAddress varchar(255),@ReferralAgencyCategoryId int)as--Trim StringsSET @Name = ltrim(rtrim(@Name))SET @Address = ltrim(rtrim(@Address))SET @Contact = ltrim(rtrim(@Contact))SET @EmailAddress = ltrim(rtrim(@EmailAddress))--Set Nullsif @Name = '' set @Name = NULLif @Address = '' set @Address = NULLif @Contact = '' set @Contact = NULLif @EmailAddress = '' set @EmailAddress = NULLupdate ReferralAgenciesset LocationId = @LocationId, Name = @Name, Phone1 = @Phone1, Phone2 = @Phone2, Address = @Address, Contact = @Contact, EmailAddress = @EmailAddress, ReferralAgencyCategoryId = @ReferralAgencyCategoryIdwhere ReferralAgencyId = @ReferralAgencyIdreturngocreate procedure AddReferralAgencyCategory(@Description varchar(255))as--Trim StringsSET @Description = ltrim(rtrim(@Description))--Please Remove the last comma above--Set Nullsif @Description = '' set @Description = NULLinsert into ReferralAgencyCategories(Description)values(@Description)select @@identityreturngocreate procedure DeleteReferralAgencyCategoryById(@ReferralAgencyCategoryId int)asdelete from ReferralAgencyCategorieswhere ReferralAgencyCategoryId = @ReferralAgencyCategoryIdreturngocreate procedure GetReferralAgencyCategoriesasselect ReferralAgencyCategoryId, Descriptionfrom ReferralAgencyCategoriesreturngocreate procedure GetReferralAgencyCategoryById(@ReferralAgencyCategoryId int)asselect ReferralAgencyCategoryId, Descriptionfrom ReferralAgencyCategorieswhere ReferralAgencyCategoryId = @ReferralAgencyCategoryIdreturngocreate procedure UpdateReferralAgencyCategory(@ReferralAgencyCategoryId int,@Description varchar(255))as--Trim StringsSET @Description = ltrim(rtrim(@Description))--Set Nullsif @Description = '' set @Description = NULLupdate ReferralAgencyCategoriesset Description = @Descriptionwhere ReferralAgencyCategoryId = @ReferralAgencyCategoryIdreturngocreate procedure AddReferral(@ReferralAgencyId int,@ReferralDetails varchar(255),@NumberOfAdults smallint,@NumberOfChildren smallint,@ReferralDate smalldatetime,@ClientName varchar(255))as--Trim StringsSET @ReferralDetails = ltrim(rtrim(@ReferralDetails))SET @ClientName = ltrim(rtrim(@ClientName))--Please Remove the last comma above--Set Nullsif @ReferralDetails = '' set @ReferralDetails = NULLif @ClientName = '' set @ClientName = NULLinsert into Referrals(ReferralAgencyId, ReferralDetails, NumberOfAdults, NumberOfChildren, ReferralDate, ClientName)values(@ReferralAgencyId, @ReferralDetails, @NumberOfAdults, @NumberOfChildren, @ReferralDate, @ClientName)select @@identityreturngocreate procedure DeleteReferralById(@ReferralId int)asdelete from Referralswhere ReferralId = @ReferralIdreturngocreate procedure GetReferralsasselect ReferralId, ReferralAgencyId, ReferralDetails, NumberOfAdults, NumberOfChildren, ReferralDate, ClientNamefrom Referralsreturngocreate procedure GetReferralById(@ReferralId int)asselect ReferralId, ReferralAgencyId, ReferralDetails, NumberOfAdults, NumberOfChildren, ReferralDate, ClientNamefrom Referralswhere ReferralId = @ReferralIdreturngocreate procedure UpdateReferral(@ReferralId int,@ReferralAgencyId int,@ReferralDetails varchar(255),@NumberOfAdults smallint,@NumberOfChildren smallint,@ReferralDate smalldatetime,@ClientName varchar(255))as--Trim StringsSET @ReferralDetails = ltrim(rtrim(@ReferralDetails))SET @ClientName = ltrim(rtrim(@ClientName))--Set Nullsif @ReferralDetails = '' set @ReferralDetails = NULLif @ClientName = '' set @ClientName = NULLupdate Referralsset ReferralAgencyId = @ReferralAgencyId, ReferralDetails = @ReferralDetails, NumberOfAdults = @NumberOfAdults, NumberOfChildren = @NumberOfChildren, ReferralDate = @ReferralDate, ClientName = @ClientNamewhere ReferralId = @ReferralIdreturngocreate procedure AddTotalAsset(@TotalFood decimal,@TotalMoney decimal)as--Trim Strings--Please Remove the last comma above--Set Nullsinsert into TotalAssets(TotalFood, TotalMoney)values(@TotalFood, @TotalMoney)select @@identityreturngocreate procedure DeleteTotalAssetById(@TotalAssetId int)asdelete from TotalAssetswhere TotalAssetId = @TotalAssetIdreturngocreate procedure GetTotalAssetsasselect TotalAssetId, TotalFood, TotalMoneyfrom TotalAssetsreturngocreate procedure GetTotalAssetById(@TotalAssetId int)asselect TotalAssetId, TotalFood, TotalMoneyfrom TotalAssetswhere TotalAssetId = @TotalAssetIdreturngocreate procedure UpdateTotalAsset(@TotalAssetId int,@TotalFood decimal,@TotalMoney decimal)as--Trim Strings--Set Nullsupdate TotalAssetsset TotalFood = @TotalFood, TotalMoney = @TotalMoneywhere TotalAssetId = @TotalAssetIdreturngocreate procedure AddVolunteerHour(@VolunteerId int,@Date smalldatetime,@HoursWorked int)as--Trim Strings--Please Remove the last comma above--Set Nullsinsert into VolunteerHours(VolunteerId, Date, HoursWorked)values(@VolunteerId, @Date, @HoursWorked)select @@identityreturngocreate procedure DeleteVolunteerHourById(@VolunteerHourId int)asdelete from VolunteerHourswhere VolunteerHourId = @VolunteerHourIdreturngocreate procedure GetVolunteerHoursasselect VolunteerHourId, VolunteerId, Date, HoursWorkedfrom VolunteerHoursreturngocreate procedure GetVolunteerHourById(@VolunteerHourId int)asselect VolunteerHourId, VolunteerId, Date, HoursWorkedfrom VolunteerHourswhere VolunteerHourId = @VolunteerHourIdreturngocreate procedure UpdateVolunteerHour(@VolunteerHourId int,@VolunteerId int,@Date smalldatetime,@HoursWorked int)as--Trim Strings--Set Nullsupdate VolunteerHoursset VolunteerId = @VolunteerId, Date = @Date, HoursWorked = @HoursWorkedwhere VolunteerHourId = @VolunteerHourIdreturngocreate procedure AddVolunteer(@LocationId int,@VolunteerTypeId int,@Gender char,@FirstName varchar(255),@LastName varchar(255),@MiddleName varchar(255),@Address varchar(255),@Phone1 char,@Phone2 char,@Birthdate smalldatetime,@StartDate smalldatetime,@EmailAddress varchar(255),@PostalCode char,@EmergencyContactName varchar(255),@EmergencyContactPhone char)as--Trim StringsSET @FirstName = ltrim(rtrim(@FirstName))SET @LastName = ltrim(rtrim(@LastName))SET @MiddleName = ltrim(rtrim(@MiddleName))SET @Address = ltrim(rtrim(@Address))SET @EmailAddress = ltrim(rtrim(@EmailAddress))SET @EmergencyContactName = ltrim(rtrim(@EmergencyContactName))--Please Remove the last comma above--Set Nullsif @FirstName = '' set @FirstName = NULLif @LastName = '' set @LastName = NULLif @MiddleName = '' set @MiddleName = NULLif @Address = '' set @Address = NULLif @EmailAddress = '' set @EmailAddress = NULLif @EmergencyContactName = '' set @EmergencyContactName = NULLinsert into Volunteers(LocationId, VolunteerTypeId, Gender, FirstName, LastName, MiddleName, Address, Phone1, Phone2, Birthdate, StartDate, EmailAddress, PostalCode, EmergencyContactName, EmergencyContactPhone)values(@LocationId, @VolunteerTypeId, @Gender, @FirstName, @LastName, @MiddleName, @Address, @Phone1, @Phone2, @Birthdate, @StartDate, @EmailAddress, @PostalCode, @EmergencyContactName, @EmergencyContactPhone)select @@identityreturngocreate procedure DeleteVolunteerById(@VolunteerId int)asdelete from Volunteerswhere VolunteerId = @VolunteerIdreturngocreate procedure GetVolunteersasselect VolunteerId, LocationId, VolunteerTypeId, Gender, FirstName, LastName, MiddleName, Address, Phone1, Phone2, Birthdate, StartDate, EmailAddress, PostalCode, EmergencyContactName, EmergencyContactPhonefrom Volunteersreturngocreate procedure GetVolunteerById(@VolunteerId int)asselect VolunteerId, LocationId, VolunteerTypeId, Gender, FirstName, LastName, MiddleName, Address, Phone1, Phone2, Birthdate, StartDate, EmailAddress, PostalCode, EmergencyContactName, EmergencyContactPhonefrom Volunteerswhere VolunteerId = @VolunteerIdreturngocreate procedure UpdateVolunteer(@VolunteerId int,@LocationId int,@VolunteerTypeId int,@Gender char,@FirstName varchar(255),@LastName varchar(255),@MiddleName varchar(255),@Address varchar(255),@Phone1 char,@Phone2 char,@Birthdate smalldatetime,@StartDate smalldatetime,@EmailAddress varchar(255),@PostalCode char,@EmergencyContactName varchar(255),@EmergencyContactPhone char)as--Trim StringsSET @FirstName = ltrim(rtrim(@FirstName))SET @LastName = ltrim(rtrim(@LastName))SET @MiddleName = ltrim(rtrim(@MiddleName))SET @Address = ltrim(rtrim(@Address))SET @EmailAddress = ltrim(rtrim(@EmailAddress))SET @EmergencyContactName = ltrim(rtrim(@EmergencyContactName))--Set Nullsif @FirstName = '' set @FirstName = NULLif @LastName = '' set @LastName = NULLif @MiddleName = '' set @MiddleName = NULLif @Address = '' set @Address = NULLif @EmailAddress = '' set @EmailAddress = NULLif @EmergencyContactName = '' set @EmergencyContactName = NULLupdate Volunteersset LocationId = @LocationId, VolunteerTypeId = @VolunteerTypeId, Gender = @Gender, FirstName = @FirstName, LastName = @LastName, MiddleName = @MiddleName, Address = @Address, Phone1 = @Phone1, Phone2 = @Phone2, Birthdate = @Birthdate, StartDate = @StartDate, EmailAddress = @EmailAddress, PostalCode = @PostalCode, EmergencyContactName = @EmergencyContactName, EmergencyContactPhone = @EmergencyContactPhonewhere VolunteerId = @VolunteerIdreturngocreate procedure AddVolunteerType(@Description varchar(255))as--Trim StringsSET @Description = ltrim(rtrim(@Description))--Please Remove the last comma above--Set Nullsif @Description = '' set @Description = NULLinsert into VolunteerTypes(Description)values(@Description)select @@identityreturngocreate procedure DeleteVolunteerTypeById(@VolunteerTypeId int)asdelete from VolunteerTypeswhere VolunteerTypeId = @VolunteerTypeIdreturngocreate procedure GetVolunteerTypesasselect VolunteerTypeId, Descriptionfrom VolunteerTypesreturngocreate procedure GetVolunteerTypeById(@VolunteerTypeId int)asselect VolunteerTypeId, Descriptionfrom VolunteerTypeswhere VolunteerTypeId = @VolunteerTypeIdreturngocreate procedure UpdateVolunteerType(@VolunteerTypeId int,@Description varchar(255))as--Trim StringsSET @Description = ltrim(rtrim(@Description))--Set Nullsif @Description = '' set @Description = NULLupdate VolunteerTypesset Description = @Descriptionwhere VolunteerTypeId = @VolunteerTypeIdreturngouse mastergouse StAlbertFoodBank-- reseed all tables to not mess up insertsexec sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 BEGIN DBCC CHECKIDENT (''?'',RESEED,1) END'goINSERT INTO TotalAssets (TotalAssetId, TotalFood, TotalMoney) VALUES (1, 6940.16, 850.74)goINSERT INTO Locations (CityTown, Province, Country) VALUES ('St. Albert', 'Alberta', 'Canada')INSERT INTO Locations (CityTown, Province, Country) VALUES ('Morinville', 'Alberta', 'Canada')INSERT INTO Locations (CityTown, Province, Country) VALUES ('Alexander', 'Alberta', 'Canada')INSERT INTO Locations (CityTown, Province, Country) VALUES ('Edmonton', 'Alberta', 'Canada')goINSERT INTO VolunteerTypes (Description) VALUES ('Skilled')INSERT INTO VolunteerTypes (Description) VALUES ('Unskilled')goINSERT INTO Volunteers (LocationId, VolunteerTypeId, Gender, FirstName, LastName, MiddleName, Address, Phone1, Phone2, Birthdate, StartDate, EmailAddress, PostalCode, EmergencyContactName, EmergencyContactPhone) VALUES (1, 2, 'M', 'Luke', 'Skywalker', '', '6602 Millenium Way', '7809654435', '7804641153', CONVERT(DATETIME, '1961-08-24',120), CONVERT(DATETIME, '2006-12-05',120), 'iamjedi@force.net', 'P2U9Z7', 'Han Solo', '7802486653')INSERT INTO Volunteers (LocationId, VolunteerTypeId, Gender, FirstName, LastName, MiddleName, Address, Phone1, Phone2, Birthdate, StartDate, EmailAddress, PostalCode, EmergencyContactName, EmergencyContactPhone) VALUES (4, 1, 'F', 'Marge', 'Simpson', '', '742 Evergreen Terrace', '7805512256', '', CONVERT(DATETIME, '1956-02-06',120), CONVERT(DATETIME, '1998-05-25',120), 'marjorie@springfield.com', 'N3O9S8', 'Homer Simpson', '7805512256')INSERT INTO Volunteers (LocationId, VolunteerTypeId, Gender, FirstName, LastName, MiddleName, Address, Phone1, Phone2, Birthdate, StartDate, EmailAddress, PostalCode, EmergencyContactName, EmergencyContactPhone) VALUES (1, 1, 'M', 'Jean', 'Picard', 'Luc', '314 Galaxy Dr.', '7806431687', '', CONVERT(DATETIME, '1948-07-13',120), CONVERT(DATETIME, '2002-08-14',120), 'captain@enterprise.ufo', 'R3I7B1', 'Diana Troi', '7806685530')INSERT INTO Volunteers (LocationId, VolunteerTypeId, Gender, FirstName, LastName, MiddleName, Address, Phone1, Phone2, Birthdate, StartDate, EmailAddress, PostalCode, EmergencyContactName, EmergencyContactPhone) VALUES (2, 1, 'F', 'Judge', 'Judy', '', '1199 Justice Court', '780728391', '', CONVERT(DATETIME, '1965-06-11',120), CONVERT(DATETIME, '2008-01-09',120), 'judy@gavel.com', 'P9M4N7', 'Officer Dan', '7804486209')INSERT INTO Volunteers (LocationId, VolunteerTypeId, Gender, FirstName, LastName, MiddleName, Address, Phone1, Phone2, Birthdate, StartDate, EmailAddress, PostalCode, EmergencyContactName, EmergencyContactPhone) VALUES (1, 2, 'F', 'Olive', 'Oyl', '', '62 Oceanview Gardens', '7805575216', '', CONVERT(DATETIME, '1919-12-19',120), CONVERT(DATETIME, '2005-05-16',120), 'damsel@distress.net', 'Y3M8S2', 'Popeye', '7807469234')goINSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked) VALUES (2, CONVERT(DATETIME, '1998-05-25',120), 6)INSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked) VALUES (2, CONVERT(DATETIME, '2000-02-27',120), 8)INSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked) VALUES (3, CONVERT(DATETIME, '2002-08-14',120), 8)INSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked) VALUES (5, CONVERT(DATETIME, '2005-05-16',120), 4)INSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked) VALUES (1, CONVERT(DATETIME, '2006-12-05',120), 6)INSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked) VALUES (2, CONVERT(DATETIME, '2007-11-30',120), 5)INSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked) VALUES (4, CONVERT(DATETIME, '2008-01-09',120), 8)INSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked) VALUES (2, CONVERT(DATETIME, '2008-01-09',120), 8)goINSERT INTO Communities (LocationId, Name) VALUES (1, 'Akinsdale')INSERT INTO Communities (LocationId, Name) VALUES (1, 'Braeside')INSERT INTO Communities (LocationId, Name) VALUES (1, 'Deer Ridge')INSERT INTO Communities (LocationId, Name) VALUES (1, 'Grandin')INSERT INTO Communities (LocationId, Name) VALUES (4, 'Edmonton')INSERT INTO Communities (LocationId, Name) VALUES (2, 'Morinville')INSERT INTO Communities (LocationId, Name) VALUES (3, 'Alexander')goINSERT INTO Households (PostalCode, Address, CommunityId) VALUES ('F9M4B7', '28 Rodeo Dr.', 1)INSERT INTO Households (PostalCode, Address, CommunityId) VALUES ('S5D9Y3', '6543 Pine St.', 2)INSERT INTO Households (PostalCode, Address, CommunityId) VALUES ('I3Q8D6', '16 Lakewood Dr.', 6)INSERT INTO Households (PostalCode, Address, CommunityId) VALUES ('H6D5S9', '591 Eastwood', 7)INSERT INTO Households (PostalCode, Address, CommunityId) VALUES ('U1D6E4', '93 Akinsdale Estates', 1)goINSERT INTO FamilyStates (Description) VALUES ('Single Parent')INSERT INTO FamilyStates (Description) VALUES ('Two Parents')INSERT INTO FamilyStates (Description) VALUES ('Single Person')INSERT INTO FamilyStates (Description) VALUES ('Couple without Children')goINSERT INTO Families (HeadOfFamilyClientId, FamilyComment, HouseholdId, FamilyStateId, RedFlag, Phone1, Phone2) VALUES (null, '', 2, 3, 0, '7803468164', '')INSERT INTO Families (HeadOfFamilyClientId, FamilyComment, HouseholdId, FamilyStateId, RedFlag, Phone1, Phone2) VALUES (null, 'Sometimes tries to double-dip.', 5, 2, 0, '7804684611', '7809953315')INSERT INTO Families (HeadOfFamilyClientId, FamilyComment, HouseholdId, FamilyStateId, RedFlag, Phone1, Phone2) VALUES (null, '', 4, 1, 0, '7809643687', '')goINSERT INTO Clients (HealthInfo, Gender, FirstName, LastName, MiddleName, Birthdate, EmailAddress, AhcNumber, FamilyId) VALUES ('Peanut allergy', 'M', 'Lester','Arthur','A', CONVERT(DATETIME, '1968-06-18',120), '', '64256-6684', 1)INSERT INTO Clients (HealthInfo, Gender, FirstName, LastName, MiddleName, Birthdate, EmailAddress, AhcNumber, FamilyId) VALUES ('', 'M', 'John','Fogerty','', CONVERT(DATETIME, '1956-11-04',120), '', '24653-4460', 2)INSERT INTO Clients (HealthInfo, Gender, FirstName, LastName, MiddleName, Birthdate, EmailAddress, AhcNumber, FamilyId) VALUES ('', 'F', 'Julia','Roberts','', CONVERT(DATETIME, '1964-08-07',120), '', '64256-6684', 2)INSERT INTO Clients (HealthInfo, Gender, FirstName, LastName, MiddleName, Birthdate, EmailAddress, AhcNumber, FamilyId) VALUES ('Diabetic', 'F', 'Susie','Fogerty','Q', CONVERT(DATETIME, '1992-03-24',120), '', '92207-8034', 2)INSERT INTO Clients (HealthInfo, Gender, FirstName, LastName, MiddleName, Birthdate, EmailAddress, AhcNumber, FamilyId) VALUES ('', 'F', 'Edna','Krabapple','', CONVERT(DATETIME, '1974-06-18',120), '', '64256-6519', 3)INSERT INTO Clients (HealthInfo, Gender, FirstName, LastName, MiddleName, Birthdate, EmailAddress, AhcNumber, FamilyId) VALUES ('', 'M', 'Teddy','Krabapple','', CONVERT(DATETIME, '2004-10-20',120),'', '83614-6481', 3)INSERT INTO Clients (HealthInfo, Gender, FirstName, LastName, MiddleName, Birthdate, EmailAddress, AhcNumber, FamilyId) VALUES ('', 'M', 'Billy','Krabapple','', CONVERT(DATETIME, '2006-07-02',120), '', '64256-8608', 3)goUpdate Families Set HeadOfFamilyClientId = (select clientid from clients where firstname = 'Lester') Where phone1 = '7803468164'Update Families Set HeadOfFamilyClientId = (select clientid from clients where firstname = 'Julia') Where phone1 = '7804684611'Update Families Set HeadOfFamilyClientId = (select clientid from clients where firstname = 'Edna') Where phone1 = '7809643687'goINSERT INTO ClientHouseholdHistory (ClientId, HouseholdId, DateMovedOut) VALUES (1, 2, '')INSERT INTO ClientHouseholdHistory (ClientId, HouseholdId, DateMovedOut) VALUES (3, 1, CONVERT(DATETIME, '2005-05-22',120))INSERT INTO ClientHouseholdHistory (ClientId, HouseholdId, DateMovedOut) VALUES (3, 5, '')INSERT INTO ClientHouseholdHistory (ClientId, HouseholdId, DateMovedOut) VALUES (5, 4, '')goINSERT INTO ReferralAgencyCategories (Description) VALUES ('Social Agencies')INSERT INTO ReferralAgencyCategories (Description) VALUES ('Churches')INSERT INTO ReferralAgencyCategories (Description) VALUES ('Doctors')INSERT INTO ReferralAgencyCategories (Description) VALUES ('Others')goINSERT INTO ReferralAgencies (LocationId, Name, Phone1, Phone2, Address, Contact, EmailAddress) VALUES (1, 'St. Albert Health Unit', '7809643152', '', '642 Pleasantview St.', 'Janice', 'janicet@sahu.org')INSERT INTO ReferralAgencies (LocationId, Name, Phone1, Phone2, Address, Contact, EmailAddress) VALUES (1, 'FCSS (St. Albert)', '7806842615', '', '23 Charcoal Rd.', 'Humphrey', 'hbogart@fcss.net')INSERT INTO ReferralAgencies (LocationId, Name, Phone1, Phone2, Address, Contact, EmailAddress) VALUES (3, 'Alexander Reserve', '7805386617', '7809643153', '83 Crestwood Way', 'Lisa', 'alexander@reserve.net')INSERT INTO ReferralAgencies (LocationId, Name, Phone1, Phone2, Address, Contact, EmailAddress) VALUES (1, 'Other', '', '', '', '', '')goINSERT INTO Referrals (ReferralAgencyId, ReferralDetails, NumberOfAdults, NumberOfChildren, ReferralDate, ClientName) VALUES (1, 'Lester is allergic to peanuts', 1, 0, CONVERT(DATETIME, '2007-08-14',120), 'Lester Arthur')INSERT INTO Referrals (ReferralAgencyId, ReferralDetails, NumberOfAdults, NumberOfChildren, ReferralDate, ClientName) VALUES (1, '', 2, 1, CONVERT(DATETIME, '2007-11-18',120), 'Julia Roberts')INSERT INTO Referrals (ReferralAgencyId, ReferralDetails, NumberOfAdults, NumberOfChildren, ReferralDate, ClientName) VALUES (2, '', 1, 0, CONVERT(DATETIME, '2007-10-27',120), 'Lester Arthur')INSERT INTO Referrals (ReferralAgencyId, ReferralDetails, NumberOfAdults, NumberOfChildren, ReferralDate, ClientName) VALUES (3, '', 1, 2, CONVERT(DATETIME, '2008-01-05',120), 'Edna Krabapple')INSERT INTO Referrals (ReferralAgencyId, ReferralDetails, NumberOfAdults, NumberOfChildren, ReferralDate, ClientName) VALUES (4, '', 1, 2, CONVERT(DATETIME, '2008-02-24',120), 'Edna Krabapple')goINSERT INTO FoodHampers (Weight, PickupDate, PickupPerson, ReferralId, FamilyId) VALUES (35, CONVERT(DATETIME, '2007-08-18',120), 'Lester Arthur', 1, 1)INSERT INTO FoodHampers (Weight, PickupDate, PickupPerson, ReferralId, FamilyId) VALUES (40, CONVERT(DATETIME, '2007-11-24',120), 'John Fogerty', 2, 2)INSERT INTO FoodHampers (Weight, PickupDate, PickupPerson, ReferralId, FamilyId) VALUES (35, CONVERT(DATETIME, '2007-08-18',120), 'Lester Arthur', 3, 1)INSERT INTO FoodHampers (Weight, PickupDate, PickupPerson, ReferralId, FamilyId) VALUES (50, CONVERT(DATETIME, '2008-01-09',120), 'Edna Krabapple', 4, 3)goINSERT INTO DonorTypes (Description) VALUES ('Corporate')INSERT INTO DonorTypes (Description) VALUES ('Individual')INSERT INTO DonorTypes (Description) VALUES ('Anonymous')goINSERT INTO Donors (LocationId, TypeId, Name, Phone1, Phone2, Address, EmailAddress, Contact) VALUES (1, 2, 'Fred Savage', '7806243150', '', '681 Forester Ave.', 'fsavage@wonder.net', '')INSERT INTO Donors (LocationId, TypeId, Name, Phone1, Phone2, Address, EmailAddress, Contact) VALUES (1, 3, 'Anonymous', '', '', '', '', '')INSERT INTO Donors (LocationId, TypeId, Name, Phone1, Phone2, Address, EmailAddress, Contact) VALUES (2, 1, 'Safeway Ltd.', '7809564481', '', '21 Possum St.', '', 'Felicity')INSERT INTO Donors (LocationId, TypeId, Name, Phone1, Phone2, Address, EmailAddress, Contact) VALUES (3, 2, 'Sharon Stone', '7807224688', '7804681213', '55 Neptune Way', 'sharon.stone@movies.com', '')goINSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated) VALUES (1, CONVERT(DATETIME, '2007-05-09',120), null, 100)INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated) VALUES (2, CONVERT(DATETIME, '2007-06-15',120), null, 550)INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated) VALUES (3, CONVERT(DATETIME, '2007-10-01',120), 1000, null)INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated) VALUES (3, CONVERT(DATETIME, '2008-01-01',120), 1500, null)INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated) VALUES (4, CONVERT(DATETIME, '2008-01-24',120), null, 200)goINSERT INTO Donees (DoneeName) VALUES ('Salvation Army')INSERT INTO Donees (DoneeName) VALUES ('Unity Surplus')INSERT INTO Donees (DoneeName) VALUES ('Garbage')INSERT INTO Donees (DoneeName) VALUES ('Other')goINSERT INTO OutgoingDonations (DoneeId, OutgoingDonationDate, WeightDonated, MoneyDonated) VALUES (1, CONVERT(DATETIME, '2007-05-09',120), null, 100)INSERT INTO OutgoingDonations (DoneeId, OutgoingDonationDate, WeightDonated, MoneyDonated) VALUES (2, CONVERT(DATETIME, '2007-06-15',120), null, 550)INSERT INTO OutgoingDonations (DoneeId, OutgoingDonationDate, WeightDonated, MoneyDonated) VALUES (3, CONVERT(DATETIME, '2007-10-01',120), 1000, null)INSERT INTO OutgoingDonations (DoneeId, OutgoingDonationDate, WeightDonated, MoneyDonated) VALUES (3, CONVERT(DATETIME, '2008-01-01',120), 1500, null)INSERT INTO OutgoingDonations (DoneeId, OutgoingDonationDate, WeightDonated, MoneyDonated) VALUES (4, CONVERT(DATETIME, '2008-01-24',120), null, 200)goINSERT INTO ClientTypes(Description) VALUES ('Aboriginal')INSERT INTO ClientTypes(Description) VALUES ('New Immigrant / Refugee')INSERT INTO ClientTypes(Description) VALUES ('Post Secondary')goINSERT INTO ClientTypeInformation (ClientId, ClientTypeId) VALUES (1, 1)INSERT INTO ClientTypeInformation (ClientId, ClientTypeId) VALUES (4, 1)INSERT INTO ClientTypeInformation (ClientId, ClientTypeId) VALUES (1, 3)INSERT INTO ClientTypeInformation (ClientId, ClientTypeId) VALUES (2, 3)INSERT INTO ClientTypeInformation (ClientId, ClientTypeId) VALUES (2, 1)INSERT INTO ClientTypeInformation (ClientId, ClientTypeId) VALUES (3, 3)INSERT INTO ClientTypeInformation (ClientId, ClientTypeId) VALUES (1, 2)--Added 2008.03.12 11:38 - MathieuINSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated) VALUES (1, CONVERT(DATETIME, '2008-03-02',120), 50, null)INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated) VALUES (2, CONVERT(DATETIME, '2008-03-03',120), 10, null)INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated) VALUES (3, CONVERT(DATETIME, '2008-03-08',120), 945, null)INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated) VALUES (3, CONVERT(DATETIME, '2008-03-08',120), 1500, null)INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated) VALUES (4, CONVERT(DATETIME, '2008-03-10',120), 670, null)INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated) VALUES (1, CONVERT(DATETIME, '2007-01-02',120), 25, null)INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated) VALUES (2, CONVERT(DATETIME, '2007-01-18',120), 60, null)INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated) VALUES (3, CONVERT(DATETIME, '2007-02-08',120), 226, null)INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated) VALUES (3, CONVERT(DATETIME, '2007-03-05',120), 1290, null)INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated) VALUES (4, CONVERT(DATETIME, '2007-03-10',120), 670, null)INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated) VALUES (1, CONVERT(DATETIME, '2007-03-28',120), 62, null)INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated) VALUES (2, CONVERT(DATETIME, '2008-01-21',120), 39, null)INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated) VALUES (3, CONVERT(DATETIME, '2008-01-30',120), 278, null)INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated) VALUES (3, CONVERT(DATETIME, '2008-02-17',120), 920, null)INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated) VALUES (4, CONVERT(DATETIME, '2008-02-25',120), 75, null)INSERT INTO Volunteers (LocationId, VolunteerTypeId, Gender, FirstName, LastName, MiddleName, Address, Phone1, Phone2, Birthdate, StartDate, EmailAddress, PostalCode, EmergencyContactName, EmergencyContactPhone) VALUES (1, 2, 'F', 'Jaina', 'Proudmoore', '', '45 Elwyn Forest', '7802468495', '', CONVERT(DATETIME, '1961-03-17',120), CONVERT(DATETIME, '2006-03-15',120), 'jaina@nature.net', 'U6S9F1', 'Lilly Proudmoore', '7807468543')INSERT INTO Referrals (ReferralAgencyId, ReferralDetails, NumberOfAdults, NumberOfChildren, ReferralDate, ClientName) VALUES (1, '', 2, 3, CONVERT(DATETIME, '2008-03-05',120), 'Sweeney Todd')-- #1 return columns: Volunteer, Birthday, Upcoming Agegodrop procedure GetVolunteerBirthdaysgoCREATE PROCEDURE GetVolunteerBirthdaysASSELECT FirstName + ' ' + LastName as "Volunteer", datename(month,Birthdate) + ' ' + convert(varchar, datepart(dd,Birthdate)) as "Birthday", datediff(yy, Birthdate, getdate()) as "UpcomingAge"FROM VolunteersWHERE datediff(dd, getdate(), dateadd(yy, datediff(yy, Birthdate, getdate()),Birthdate)) < 7AND datediff(dd, getdate(), dateadd(yy, datediff(yy, Birthdate, getdate()),Birthdate)) >=0RETURN @@identityGO-- #2 return columns: Agency, Date Referred, Clientdrop procedure GetOutstandingReferralsgoCREATE PROCEDURE GetOutstandingReferralsASSELECT ra.Name "Agency", datename(month,ReferralDate) + ' ' + convert(varchar, datepart(dd,ReferralDate)) +', '+ convert(varchar, datepart(yy,ReferralDate)) "DateReferred", ClientName "Client"FROM Referrals r, ReferralAgencies raWHERE ReferralId NOT IN(SELECT ReferralId FROM FoodHampers)AND r.ReferralAgencyId = ra.ReferralAgencyIdRETURN @@identityGO-- #3 return columns: drop procedure GetTotalServedgoCREATE PROCEDURE GetTotalServedASDECLARE @children int, @adults int, @total intSET @children =(SELECT sum(NumberOfChildren) FROM Referrals WHERE ReferralId in (SELECT ReferralId FROM FoodHampers))SET @adults =( SELECT sum(NumberOfAdults) FROM Referrals WHERE ReferralId in (SELECT ReferralId FROM FoodHampers))SET @total =( SELECT sum(NumberOfAdults+NumberOfChildren) FROM Referrals WHERE ReferralId in (SELECT ReferralId FROM FoodHampers))SELECT @children "Children", @adults "Adults", @total "Total"RETURN @@identityGO-- #4 return columns: TotalAssetId, TotalFood, TotalMoney-- exec GetTotalAssets-- #5 return columns: drop procedure GetTotalSurplusgoCREATE PROCEDURE GetTotalSurplusASSELECT sum(WeightDonated) "TotalFoodSurplus"FROM OutgoingDonationsRETURN @@identityGO-- Added 2008.03.12 15:00 - MathieuINSERT INTO OutgoingDonations (DoneeId, OutgoingDonationDate, WeightDonated, MoneyDonated) VALUES (1, CONVERT(DATETIME, '2007-03-09',120), 240, 0)INSERT INTO OutgoingDonations (DoneeId, OutgoingDonationDate, WeightDonated, MoneyDonated) VALUES (2, CONVERT(DATETIME, '2007-03-15',120), 100, 0)INSERT INTO OutgoingDonations (DoneeId, OutgoingDonationDate, WeightDonated, MoneyDonated) VALUES (3, CONVERT(DATETIME, '2007-01-08',120), 850, 0)INSERT INTO OutgoingDonations (DoneeId, OutgoingDonationDate, WeightDonated, MoneyDonated) VALUES (3, CONVERT(DATETIME, '2007-01-16',120), 400, 0)INSERT INTO OutgoingDonations (DoneeId, OutgoingDonationDate, WeightDonated, MoneyDonated) VALUES (4, CONVERT(DATETIME, '2008-02-24',120), 300, 0)INSERT INTO OutgoingDonations (DoneeId, OutgoingDonationDate, WeightDonated, MoneyDonated) VALUES (1, CONVERT(DATETIME, '2008-02-04',120), 400, 0)INSERT INTO OutgoingDonations (DoneeId, OutgoingDonationDate, WeightDonated, MoneyDonated) VALUES (2, CONVERT(DATETIME, '2008-03-01',120), 150, 0)INSERT INTO OutgoingDonations (DoneeId, OutgoingDonationDate, WeightDonated, MoneyDonated) VALUES (1, CONVERT(DATETIME, '2008-03-10',120), 180, 0)INSERT INTO Referrals (ReferralAgencyId, ReferralDetails, NumberOfAdults, NumberOfChildren, ReferralDate, ClientName) VALUES (1, '', 1, 0, CONVERT(DATETIME, '2007-01-14',120), 'Lester Arthur')INSERT INTO Referrals (ReferralAgencyId, ReferralDetails, NumberOfAdults, NumberOfChildren, ReferralDate, ClientName) VALUES (1, '', 1, 2, CONVERT(DATETIME, '2007-02-05',120), 'Edna Krabapple')INSERT INTO Referrals (ReferralAgencyId, ReferralDetails, NumberOfAdults, NumberOfChildren, ReferralDate, ClientName) VALUES (1, '', 2, 1, CONVERT(DATETIME, '2007-02-18',120), 'Julia Roberts')INSERT INTO Referrals (ReferralAgencyId, ReferralDetails, NumberOfAdults, NumberOfChildren, ReferralDate, ClientName) VALUES (3, '', 1, 2, CONVERT(DATETIME, '2008-03-05',120), 'Edna Krabapple')INSERT INTO Referrals (ReferralAgencyId, ReferralDetails, NumberOfAdults, NumberOfChildren, ReferralDate, ClientName) VALUES (4, '', 2, 1, CONVERT(DATETIME, '2008-03-08',120), 'Julia Roberts')INSERT INTO Referrals (ReferralAgencyId, ReferralDetails, NumberOfAdults, NumberOfChildren, ReferralDate, ClientName) VALUES (2, '', 1, 0, CONVERT(DATETIME, '2007-03-14',120), 'Lester Arthur')INSERT INTO Referrals (ReferralAgencyId, ReferralDetails, NumberOfAdults, NumberOfChildren, ReferralDate, ClientName) VALUES (1, '', 1, 2, CONVERT(DATETIME, '2007-03-27',120), 'Edna Krabapple')INSERT INTO FoodHampers (Weight, PickupDate, PickupPerson, ReferralId, FamilyId) VALUES (50, CONVERT(DATETIME, '2007-03-27',120), 'Edna Krabapple', 6, 3)INSERT INTO FoodHampers (Weight, PickupDate, PickupPerson, ReferralId, FamilyId) VALUES (30, CONVERT(DATETIME, '2007-01-16',120), 'Lester Arthur', 7, 1)INSERT INTO FoodHampers (Weight, PickupDate, PickupPerson, ReferralId, FamilyId) VALUES (50, CONVERT(DATETIME, '2007-02-07',120), 'Edna Krabapple', 8, 3)INSERT INTO FoodHampers (Weight, PickupDate, PickupPerson, ReferralId, FamilyId) VALUES (40, CONVERT(DATETIME, '2007-02-18',120), 'Julia Roberts', 9, 2)INSERT INTO FoodHampers (Weight, PickupDate, PickupPerson, ReferralId, FamilyId) VALUES (55, CONVERT(DATETIME, '2008-03-05',120), 'Edna Krabapple', 10, 3)INSERT INTO FoodHampers (Weight, PickupDate, PickupPerson, ReferralId, FamilyId) VALUES (25, CONVERT(DATETIME, '2008-03-08',120), 'Julia Roberts', 11, 2)INSERT INTO FoodHampers (Weight, PickupDate, PickupPerson, ReferralId, FamilyId) VALUES (35, CONVERT(DATETIME, '2007-03-14',120), 'Lester Arthur', 12, 1)INSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked) VALUES (2, CONVERT(DATETIME, '2007-03-04',120), 7)INSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked) VALUES (2, CONVERT(DATETIME, '2007-03-09',120), 5)INSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked) VALUES (3, CONVERT(DATETIME, '2007-03-14',120), 4)INSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked) VALUES (5, CONVERT(DATETIME, '2007-03-21',120), 8)INSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked) VALUES (1, CONVERT(DATETIME, '2008-03-04',120), 5)INSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked) VALUES (2, CONVERT(DATETIME, '2008-03-09',120), 6)INSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked) VALUES (4, CONVERT(DATETIME, '2008-03-11',120), 6)INSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked) VALUES (2, CONVERT(DATETIME, '2008-03-12',120), 8)use master