Question

Create a database with two tables as follows:- Students - StudentID, Name, Program FeePayment - StudentID, Date, Description, Amount 1. Create a stored procedure that receives all the details of a student including fees and proceeds to insert insert the student details into the student details and the fee payment into the FeePayment table. All the operations should be done within a single transaction in a stored procedure. Note that the stored procedure works like a function that receives parameters. Copy and submit your code for creating the tables as well as your stored procedure code in a word document.

Answer 1

The below statement will create the Student Table.

CREATE TABLE [dbo].[Students](
        [StudentID] [int] NOT NULL,
        [Name] [varchar](50) NOT NULL,
        [Program] [varchar](50) NOT NULL
)

The below statement will create the FeePayment Table

CREATE TABLE [dbo].[FeePayment](
        [StudentID] [int] NOT NULL,
        [Date] [datetime] NOT NULL,
        [Description] [varchar](100) NOT NULL,
        [Amount] [float] NOT NULL
)

The below statement will create the Stored Procedure to insert the details into the respective tables. The comments are provided to understand the logic of the stored procedure.

create procedure [dbo].[AddStudentDetails]
        @StudentID int, 
        @Name varchar(50), 
        @Program varchar(50),
        @Date datetime,
        @Description varchar(100),
        @FeesAmount float
as
begin
        begin transaction
                -- Check if the StudentID and the Program combination exists in the table.
                -- If it exists do not insert it again. Otherwise proceed in inserting.
                if exists(select 1 from Students where StudentID = @StudentID and Program = @Program)
                begin
                        print 'The Student ID and the Program combination already exists in the Students table.  Hence not inserting '
                end
                else
                begin
                        -- Insert the details into the Student table
                        insert into Students (StudentID, Name, Program) values (@StudentID, @Name, @Program)
                end

                -- Insert the Fees details into the FeePayment table
                insert into FeePayment (StudentID, [Date], [Description], Amount) values (@StudentID, @Date, @Description, @FeesAmount)

        -- Finally commit the transaction
        commit
end
GO

The stored procedure can be executed as follows. The sample data and other screenshots are attached.

exec AddStudentDetails 1, 'Rob Martin', 'Computer Science', '2020-10-01', 'Sem 1 Fees', 120
exec AddStudentDetails 2, 'McCarthy', 'Software Engineering', '2020-10-02', 'Sem 1 Fees', 150