DBMS stored procedure- get values from existing table

Question

I have a table Age_cal, Vaccine Information and Medical_information table as follows:

I am trying to write a stored procedure where, I will be able to insert the values into a table named vaccine based on the vaccine_information table.

In med_info table we have DOB, and the age is calculated and stored in age_cal table along with the age grp and medicalID (PK). The vaccine information table has name of vaccine and to which age group it needs to be given to.

So while inserting a value into Vaccine table, I want to give the values medicalId, date it was given on , dose no and facility name as parameters. But I want the vaccine name to be automatically selected based on the age group they belong to.

I have written the following stored procedure, but null value is getting inserted into the vaccine_name. Please help me with inserting the vaccine name using the stored procedure.

ALTER PROCEDURE [dbo].[allot_v]
    @MedicalID int,
    @VaccineDate date,
    @DoseNo int,
    @facilityName varchar(20)
AS
BEGIN
    DECLARE @injectvaccine varchar(20)

    SET @injectvaccine = (SELECT dbo.VaccineInformation.VaccineName 
                          FROM dbo.VaccineInformation, dbo.Age_Cal 
                          WHERE dbo.Age_Cal.grp = dbo.VaccineInformation.grp 
                            AND dbo.Age_Cal.MedicalID = @MedicalID)

    INSERT INTO [dbo].[Vaccination]
        ([MedicalID], [VaccineName], [VaccineDate], [DoseNo], [FacilityName])
     VALUES (@MedicalID, @injectvaccine, @VaccineDate, @DoseNo, @facilityName)
END

Answer 1

The stored procedure worked with the following code:

ALTER PROCEDURE [dbo].[allot_v]
    @MedicalID int,
    @VaccineDate date,
    @DoseNo int,
    @facilityName varchar(20)
AS
BEGIN
    DECLARE @injectvaccine varchar(20)

    SET @injectvaccine = (SELECT dbo.VaccineInformation.VaccineName 
                          FROM dbo.VaccineInformation, dbo.Age_Cal 
                          WHERE dbo.Age_Cal.grp = dbo.VaccineInformation.grp 
                            AND dbo.Age_Cal.MedicalID = @MedicalID)

    -- Insert statements for procedure here
    INSERT INTO [dbo].[Vaccination]
        ([MedicalID], [VaccineName], [VaccineDate], [DoseNo], [FacilityName])
     VALUES (@MedicalID, @injectvaccine, @VaccineDate, @DoseNo, @facilityName)
END