AwDqTxMx

1USE [ChamplainCharityLLC]
2GO
3/****** Object:  StoredProcedure [dbo].[UpdateDay]    Script Date: 10/13/2020 8:58:56 PM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8create procedure [dbo].[UpdateDay]
9(/*These variables are to ensure the day to be modified exists in the database*/
10@VolunteerScheduleDayTimeID int,
11@VolunteerScheduleWeekID int,
12@VolunteerPersonID int,
13
14/*These variabls are the values that may be modified*/
15@DayOfWeek int = null,
16@AvailableBeginTime time(7) = null,
17@AvailableEndTime time(7) = null,
18@IsNoShow bit = null,
19@ActualHoursTotal numeric(18,0) = null,
20@Modby varchar(50)
21)
22as
23begin
24	declare
25	@success varchar(50),
26	@errorstat varchar(50),
27	@TempDayID int;
28	set @TempDayID = 0;
29	begin try
30		begin transaction
31/*********************************************************************************************************************************/
32/*This checks to see if the day to be modified exists in the database*/
33
34			select @TempDayID = VolunteerScheduleDayTimeID from Volunteer.VolunteerScheduleDayTime as da
35			where @VolunteerScheduleDayTimeID = da.VolunteerScheduleDayTimeID and @VolunteerPersonID = da.VolunteerPersonID
36			and @VolunteerScheduleWeekID = da.VolunteerScheduleWeekID
37
38			if @TempDayID = 0
39			begin
40				select @errorstat = 'The day you are trying to modify is not in the database.';
41				raiserror(@errorstat, 11, 1);
42			end
43/*********************************************************************************************************************************/
44/*
45This part of the code changed the sclar values in the code to match what already exists in the table if a null value is 
46passed to the procedure
47*/
48			if @DayOfWeek = null
49			begin
50				select @DayOfWeek = da.[DayOfWeek] from Volunteer.VolunteerScheduleDayTime as da
51				where @VolunteerScheduleDayTimeID = da.VolunteerScheduleDayTimeID
52			end
53
54			if @AvailableBeginTime = null
55			begin
56				select @AvailableBeginTime = AvailableBeginTime from Volunteer.VolunteerScheduleDayTime
57				where @VolunteerScheduleDayTimeID = VolunteerScheduleDayTimeID
58			end
59
60			if @AvailableEndTime = null
61			begin
62				select @AvailableEndTime = AvailableEndTime from Volunteer.VolunteerScheduleDayTime
63				where @VolunteerScheduleDayTimeID = VolunteerScheduleDayTimeID
64			end
65
66			if @IsNoShow = null
67			begin
68				select @IsNoShow = IsNoShow from Volunteer.VolunteerScheduleDayTime
69				where @VolunteerScheduleDayTimeID = VolunteerScheduleDayTimeID
70			end
71
72			if @ActualHoursTotal = null
73			begin
74				select @ActualHoursTotal = ActualHoursTotal from Volunteer.VolunteerScheduleDayTime
75				where @VolunteerScheduleDayTimeID = VolunteerScheduleDayTimeID
76			end
77/*********************************************************************************************************************************/
78/*This part of the code is the actual update statement*/
79
80			update Volunteer.VolunteerScheduleDayTime
81			set [DayOfWeek] = @DayOfWeek, AvailableBeginTime = @AvailableBeginTime, AvailableEndTime = @AvailableEndTime, IsNoShow = @IsNoShow,
82			ActualHoursTotal = @ActualHoursTotal, ModifiedDate = sysdatetime(), ModifiedBy = @Modby
83			where VolunteerScheduleDayTimeID = @VolunteerScheduleDayTimeID;
84
85			commit transaction
86				
87/*********************************************************************************************************************************/			
88			select @success = 'The Day has been updated successfully.';
89			select @success as 'STATUS';
90		
91
92	end try
93
94	begin catch
95		select @errorstat as 'STATUS';
96		rollback transaction;
97	end catch
98end
99GO