Why am I getting “Error converting data type varchar to int.”

I am creating a mock database for a fictional gym. I have 3 relevant tables: Program, Workouts and Exercises (see below). Each Program contains one or many Workouts and each Workout contains one or many Exercises. I am trying to create a stored procedure to add rows to these three tables. I keep getting the error “Error converting data type varchar to int.”. I can’t understand why because the variable datatypes all match the datatypes in the tables (as far as I can see).

CREATE TABLE [Program] 
(
    [ProgramID] INT IDENTITY(1,1),
    [MemberNumber] INT,
    [TrainerID] INT,
    [ProgramStartDate] DATE,
    [TrainerReviewDate] DATE,
    [Active] CHAR(1),

    PRIMARY KEY ([ProgramID]),
    FOREIGN KEY ([MemberNumber]) 
        REFERENCES [Members] ([MemberNumber])
            ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY ([TrainerID]) 
        REFERENCES [Trainers] ([TrainerID])
            ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE [Workouts] 
(
    [WorkoutID] INT IDENTITY(1,1),
    [Description] VARCHAR(50),
    [Day] VARCHAR(10),
    [ProgramID] INT,

    PRIMARY KEY ([WorkoutID]),
    FOREIGN KEY ([ProgramID]) 
        REFERENCES [Program] ([ProgramID])
            ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE [Exercise] 
(
    [ExerciseID] INT IDENTITY(1,1),
    [ExerciseType] VARCHAR(30),
    [Equipment] VARCHAR(30),
    [Sets] INT,
    [Reps] INT,
    [WorkoutID] INT,

    PRIMARY KEY ([ExerciseID]),
    FOREIGN KEY ([WorkoutID]) 
        REFERENCES [Workouts] ([WorkoutID]) 
            ON DELETE SET NULL ON UPDATE CASCADE
)

/*Description: This stored procedure adds a new program with workout/exercise details. */
CREATE PROCEDURE usp_New_Program_4 (
    @MemberNumber int,
    @TrainerID int,
    @ProgramStartDate date,
    @TrainerReviewDate date,
    @Active char(1),
    @Description varchar(50),
    @Day varchar(10),
    @ProgramID int = SCOPE_IDENTITY,
    @ExerciseType_1 varchar(30),
    @Equipment_1 varchar(30),
    @Sets_1 int,
    @Reps_1 int,
    @ExerciseType_2 varchar(30),
    @Equipment_2 varchar(30),
    @Sets_2 int,
    @Reps_2 int,
    @ExerciseType_3 varchar(30),
    @Equipment_3 varchar(30),
    @Sets_3 int,
    @Reps_3 int,
    @ExerciseType_4 varchar(30),
    @Equipment_4 varchar(30),
    @Sets_4 int,
    @Reps_4 int,
    @WorkoutID int = SCOPE_IDENTITY
) 
AS
    SET xact_abort ON
BEGIN
    INSERT INTO Program (MemberNumber, TrainerID, ProgramStartDate, TrainerReviewDate, Active)
    VALUES (@MemberNumber, @TrainerID, @ProgramStartDate, @TrainerReviewDate, @Active);

    INSERT INTO Workouts (Description, Day, ProgramID)
    VALUES (@Description, @Day, SCOPE_IDENTITY());

    INSERT INTO Exercise (ExerciseType, Equipment, Sets, Reps, WorkoutID)
    VALUES (@ExerciseType_1, @Equipment_1, @Sets_1, @Reps_1, SCOPE_IDENTITY()),
           (@ExerciseType_2, @Equipment_2, @Sets_2, @Reps_2, SCOPE_IDENTITY()),
           (@ExerciseType_3, @Equipment_3, @Sets_3, @Reps_3, SCOPE_IDENTITY()),
           (@ExerciseType_4, @Equipment_4, @Sets_4, @Reps_4, SCOPE_IDENTITY())
END;
GO

/*Test for usp_New_Program_4
   Result: Failed - Error converting data type varchar to int*/

        Exec usp_New_Program_4 21,3,'2020-06-06','2020-07-07','Y','Chest & Arms',SCOPE_IDENTITY,'Bench 
                               Press','50kg Barbell',3,6,'Press Ups','Floor Mat',3,15,'Bicep Curls','15kg 
                               Dumbells',3,6,'Tricep Extensions','15kg Dumbells',3,6,SCOPE_IDENTITY
        Go

Answer

It looks like you may have missed a field in your line to execute the stored procedure. I quickly copied the stored proc input values into a spreadsheet followed by the values in your test line that throws the error. Have a look, it looks like you’re missing perhaps the Day value? After that is entered it should line everything up correctly. I marked up the first line that after a quick glance looks like it is causing the error (others lower will too though, until the missing value is fixed).

Advertisement

Answer