Q) Create a stored procedure that enrolls a student in a course. The procedure should take StudentID and CourseID as input parameter

Answer:

Stored Procedure to Enroll a Student in a Course

A stored procedure is a set of precompiled SQL statements that can be executed by calling its name. In this case, we'll create a stored procedure named `EnrollStudentInCourse` that takes two input parameters: `StudentID` and `CourseID`.

SQL Code:
```sql
CREATE PROCEDURE EnrollStudentInCourse
@StudentID int,
@CourseID int
AS
BEGIN
-- Check if the student is already enrolled in the course
IF EXISTS (SELECT 1 FROM Enrollments WHERE StudentID = @StudentID AND CourseID = @CourseID)
BEGIN
RAISERROR('Student is already enrolled in this course.', 16, 1);
RETURN;
END

-- Insert a new enrollment record
INSERT INTO Enrollments (StudentID, CourseID, EnrollmentDate)
VALUES (@StudentID, @CourseID, GETDATE());
END
GO
```

Explanation:

1. The procedure starts by checking if the student is already enrolled in the course using a `WHERE` clause in the `EXISTS` statement. If the student is already enrolled, it raises an error with a custom message.
2. If the student is not enrolled, a new record is inserted into the `Enrollments` table with the provided `StudentID`, `CourseID`, and the current date (using `GETDATE()`) as the enrollment date.

Calling the Stored Procedure:

To call this stored procedure, you would use the following syntax:
```sql
EXEC EnrollStudentInCourse @StudentID = 123, @CourseID = 456;
```
Replace `123` and `456` with the actual `StudentID` and `CourseID` values you want to use.