I am having an issue with this stored procedure. When the user selects "Yes", it should do what it says in the if statement for the "Yes" but it does not do anything, it does not insert anything into the database. It works fine for the "No" condition. Could you please tell me what it is not working ?
ALTER PROCEDURE [dbo].[sp_ff_Insert_ProjectApproved]
(
@project_no varchar(6)
,@sequence_no int
,@grant_programme varchar(2)
,@jobs_approved int
,@grant_amount int
,@Committee varchar(4)
,@Meeting_no int
,@Minute_Item int
,@jobs_maintained int
,@approval_date date
,@approval_by int
,@comments varchar(1600)
,@created_by int
,@created_date datetime
,@updated_by int
,@updated_date datetime
,@approval varchar(5)
,@ratify varchar(3)
--,@research_type varchar(1)='T'
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
DECLARE @ReturnValue int,
@post_approval_status varchar(3),
@approval_status varchar(3),
@current_status varchar(3)
set @current_status = ( SELECT approval_status from approval_master_tbl
WHERE project_no = @project_no
AND sequence_no = @sequence_no -1)
if @approval = 'No' and @sequence_no = 1
begin
set @Approval_Status = 'DRA'
set @post_approval_status = NULL
end
else if @approval = 'No' and @sequence_no > 1
begin
set @Approval_Status = 'DRA'
set @post_approval_status = ''
end
else if @approval = 'Yes' and @sequence_no > 1 and @current_status = 'DRA'
begin
set @Approval_Status = 'APP'
set @post_approval_status = ''
end
--ISSUE IS HERE!!!!
else if @approval = Null and @sequence_no > 1 and @current_status = 'DRA'
begin
set @Approval_Status = 'APP'
set @post_approval_status = ''
end
else if @approval = 'No' and @sequence_no > 1 and @current_status = 'APP'
begin
set @Approval_Status = 'APP'
set @post_approval_status = ''
end
else if @ratify = 'YES' and @sequence_no > 1 and @current_status = 'APP'
begin
set @Approval_Status = 'APP'
set @post_approval_status = 'ARF'
end
else if @approval = 'No' and @sequence_no > 1 and @current_status = 'APP' AND @post_approval_status = 'ARF'
begin
set @Approval_Status = 'HIS'
set @post_approval_status = 'ARF'
end
else if @approval = 'YES' and @sequence_no > 1 and @current_status = 'HIS'
begin
set @Approval_Status = 'HIS'
set @post_approval_status = ''
end
else if @ratify = 'YES' and @sequence_no > 1 and @current_status = 'HIS'
begin
set @Approval_Status = 'APP'
set @post_approval_status = 'ARF'
end
SET NOCOUNT ON
BEGIN TRANSACTION
INSERT INTO [dbo].[approval_master_tbl]
([project_no]
,[sequence_no]
,[grant_programme]
,[jobs_approved]
,[approval_status]
,[committee]
,[meeting_no]
,[minute_item]
,[jobs_maintained]
,[comments]
,[approval_date]
,[approved_by]
,[post_approval_status]
,[create_date]
,[create_by]
,[update_date]
,[update_by]
)
VALUES(
@project_no
,@sequence_no
,@grant_programme
,@jobs_approved
,@approval_status
,@committee
,@meeting_no
,@minute_item
,@jobs_maintained
,@comments
,@approval_date
,@approval_by
,@post_approval_status
,@created_date
,@created_by
,@updated_date
,@updated_by)
--Update previous Approval status
IF @sequence_no > 1
UPDATE approval_master_tbl
SET approval_status = @post_approval_status
WHERE project_no = @project_no
AND sequence_no = @sequence_no - 1
--Update Project Status in project Table
if @approval = 'YES'
UPDATE PROJECT_TBL
SET PROJECT_STATUS = 'YET'
,DATE_FIRST_APPROVED = @approval_date
,actual_jobs_approved = @jobs_approved
,actual_amt_approved = @grant_amount
WHERE PROJECT_TBL.project_no = @project_no
COMMIT TRAN
SET @ReturnValue = 0
END TRY
BEGIN CATCH
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
DECLARE @ErrorMessage NVARCHAR(4000)=ERROR_MESSAGE()
DECLARE @ErrorSeverity INT = ERROR_SEVERITY()
DECLARE @ErrorState INT = ERROR_STATE()
RAISERROR (@ErrorMessage,
@ErrorSeverity,
@ErrorState
);
SET @ReturnValue = 1
SET @ErrorMessage=ERROR_MESSAGE();
SELECT ERROR_MESSAGE() as ErrorMessage;
END
END CATCH
RETURN @ReturnValue
END
