I have two tables, mc_Workouts and mc_WorkoutDetails, that have a
one-to-many relationship with each other.

I use the stored procedure below to copy a workout. It needs to create a new
workout with a new name and then copy all the workout details from the
original workout to the new one.

What it does is create a new workout with the new name but it does not copy
any of the workout details to the new workout. It returns 0, which appears
to indicate success.

Can anyone help spot what I'm missing? Thanks.

ALTER PROCEDURE dbo.mc_Workouts_CopyWorkout
@WorkoutID    bigint,
@UserID     uniqueidentifier,
@NewName    varchar(100)
AS
BEGIN
SET NOCOUNT ON

DECLARE @NewWorkoutID bigint
SELECT @NewWorkoutID=0

BEGIN TRY

 BEGIN TRANSACTION

 -- Create new workout
 INSERT INTO dbo.mc_Workouts (UserID, [Name], Comments)
 SELECT UserID, @NewName, Comments
 FROM dbo.mc_Workouts
 WHERE ID=@WorkoutID AND UserID=@UserID

 -- Get workout ID
 SELECT @NewWorkoutID = SCOPE_IDENTITY()

 -- Copy workout activities
 INSERT INTO dbo.mc_WorkoutDetails (WorkoutID, ActivityID, Reps, Sets,
Minutes, Comments)
 SELECT @NewWorkoutID, ActivityID, Reps, Sets, Minutes, Comments
 FROM mc_WorkoutDetails
 WHERE ID=@WorkoutID

 COMMIT TRANSACTION

END TRY
BEGIN CATCH
 IF @@TRANCOUNT > 0
  ROLLBACK TRANSACTION
 RETURN -1
END CATCH

RETURN @@ERROR
END