Anyway, this is for SQL Server 2022. The error only happens at run time, and only when I right click on the stored procedure in the list and select to execute.
When I click the execute button it runs without issue. When I click the parse button, it successfully finishes. So it is strictly a run-time error.
DECLARE IndexCursor CURSOR FOR
SELECT PersonID AS NewUserID
FROM Person as tblUser
OPEN IndexCursor
FETCH NEXT FROM IndexCursor INTO @UserID
INSERT INTO Workout (PersonID, ExerciseID, ExerciseTypeID)
SELECT *
FROM Exercise A
WHERE NOT EXISTS (SELECT @UserID, A.ExcerciseID, A.ExerciseTypeID
FROM Workout B
WHERE A.ExcerciseID = B.ExerciseID
)
I have narrowed the issue down to the where clause. If I comment that out, I don't get the error, but it also runs on all data.
This is running a cursor to grab each userid, and grab it so it can be used to populate the workout table. The other data is already in the exercises table.
The idea is, if an exercise is added, it will add that exercise to each person's workout in the database. All of the fields and variables are set to be uniqueidentifier, so I really don't know why it has to convert anything to something it should already be set to.
I did find one difference in the two fields for the where clause, but I don't know how to fix it. B.ExerciseID has a default value of (newid()), A.exercise column does not.
I know I have misspellings of exercise, I need to go in and fix those. lol
I have tried CAST and CONVERT on @UserID in all instances that it appears. I have tried CAST on A.Exercise and B.Exercise, both in turn and at the same time. I looked and looked with googling different ideas. I have commented out parts of code to narrow down the actual line causing the issue (it wasn't until I did that, that I realized the @UserID variable was actually not causing the issue)
Update:
CREATE TABLE [dbo].[Person]
(
[PersonID] [uniqueidentifier] NOT NULL,
[Name] [varchar](50) NOT NULL,
[Weight] [smallint] NOT NULL,
[GoalWeight] [smallint] NOT NULL,
CONSTRAINT [PK_Person]
PRIMARY KEY CLUSTERED ([PersonID] ASC)[PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Workout]
(
[WrokoutID] [uniqueidentifier] NOT NULL,
[PersonID] [uniqueidentifier] NOT NULL,
[ExerciseID] [uniqueidentifier] NOT NULL,
[ExerciseTypeID] [uniqueidentifier] NOT NULL,
[Weight] [smallint] NOT NULL,
[Reps] [smallint] NOT NULL,
[Sets] [smallint] NOT NULL,
[NumberOfTimesCompleted] [bigint] NOT NULL,
[DateOfLastCompletion] [date] NOT NULL,
[Notes] [nvarchar](max) NULL,
CONSTRAINT [PK_Workout]
PRIMARY KEY CLUSTERED ([WrokoutID] ASC)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Workout]
ADD CONSTRAINT [DF_Workout_WrokoutID]
DEFAULT (NEWID()) FOR [WrokoutID]
GO
ALTER TABLE [dbo].[Workout]
ADD CONSTRAINT [DF_Workout_ExerciseID]
DEFAULT (NEWID()) FOR [ExerciseID]
GO
ALTER TABLE [dbo].[Workout]
ADD CONSTRAINT [DF_Workout_Weight]
DEFAULT ((0)) FOR [Weight]
GO
ALTER TABLE [dbo].[Workout]
ADD CONSTRAINT [DF_Workout_Reps]
DEFAULT ((0)) FOR [Reps]
GO
ALTER TABLE [dbo].[Workout]
ADD CONSTRAINT [DF_Workout_Sets]
DEFAULT ((0)) FOR [Sets]
GO
ALTER TABLE [dbo].[Workout]
ADD CONSTRAINT [DF_Workout_NumberOfTimesCompleted]
DEFAULT ((0)) FOR [NumberOfTimesCompleted]
GO
ALTER TABLE [dbo].[Workout]
ADD CONSTRAINT [DF_Workout_DateOfLastCompletion]
DEFAULT (GETDATE()) FOR [DateOfLastCompletion]
GO
ALTER TABLE [dbo].[Workout] WITH CHECK
ADD CONSTRAINT [FK_ExcerciseNameToWorkout]
FOREIGN KEY([ExerciseID])
REFERENCES [dbo].[Exercise] ([ExcerciseID])
GO
ALTER TABLE [dbo].[Workout] CHECK CONSTRAINT [FK_ExcerciseNameToWorkout]
GO
ALTER TABLE [dbo].[Workout] WITH CHECK
ADD CONSTRAINT [FK_ExerciseTypeToWorkout]
FOREIGN KEY([ExerciseTypeID])
REFERENCES [dbo].[ExerciseType] ([ExerciseTypeID])
GO
ALTER TABLE [dbo].[Workout] CHECK CONSTRAINT [FK_ExerciseTypeToWorkout]
GO
ALTER TABLE [dbo].[Workout] WITH CHECK
ADD CONSTRAINT [FK_WorkoutToPerson]
FOREIGN KEY([PersonID])
REFERENCES [dbo].[Person] ([PersonID])
GO
ALTER TABLE [dbo].[Workout] CHECK CONSTRAINT [FK_WorkoutToPerson]
GO
USE [WorkoutTracker]
GO
/****** Object: Table [dbo].[Exercise] Script Date: 10/27/2024 10:37:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Exercise](
[ExcerciseID] [uniqueidentifier] NOT NULL,
[ExerciseName] [varchar](50) NOT NULL,
[ExerciseTypeID] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_Exercise] PRIMARY KEY CLUSTERED
(
[ExcerciseID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Exercise] ADD CONSTRAINT [DF_Exercise_ExcerciseID] DEFAULT (newid()) FOR [ExcerciseID]
GO
ALTER TABLE [dbo].[Exercise] WITH CHECK ADD CONSTRAINT [FK_ExcerciseTypeToExcercise] FOREIGN KEY([ExerciseTypeID])
REFERENCES [dbo].[ExerciseType] ([ExerciseTypeID])
GO
ALTER TABLE [dbo].[Exercise] CHECK CONSTRAINT [FK_ExcerciseTypeToExcercise]
GO
You could simplify your SQL code a lot by tossing the CURSOR, and also combining the SQL to select the two fixed variables.
I've come up with this, but obviously can't really test it, since I don't have your database at my disposal:
ALTER PROCEDURE [dbo].[UpdateUserWorkouts]
AS
SET NOCOUNT ON;
-- the IF EXISTS only checks for existence - it doesn't return any data
-- no need to specify multiple columns here - SELECT * will do fine
IF EXISTS (SELECT *
FROM dbo.Exercise
LEFT OUTER JOIN dbo.Workout ON dbo.Exercise.ExcerciseID = dbo.Workout.ExerciseID
WHERE (dbo.Workout.WrokoutID IS NULL))
BEGIN
DECLARE @ExerciseID uniqueidentifier
DECLARE @ExerciseTypeID uniqueidentifier
-- use a single SELECT, and set the two variable, in a single statement
SELECT
@ExerciseID = Exercise.ExcerciseID,
@ExerciseTypeID = Exercise.ExerciseTypeID
FROM
dbo.Exercise
LEFT OUTER JOIN
dbo.Workout ON dbo.Exercise.ExcerciseID = dbo.Workout.ExerciseID
WHERE
dbo.Workout.WrokoutID IS NULL
-- do the INSERT INTO as a single, set-based SQL statement
INSERT INTO dbo.Workout (PersonID, ExerciseID, ExerciseTypeID)
SELECT
PersonID, @ExerciseID, @ExerciseTypeID
FROM
dbo.Person
END