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

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

Doh! I got it. The WHERE clause of the second INSERT should be WHERE
WorkoutID=@WorkoutID.

I was worried I had the basic approach wrong. But looks like it was just a
typo.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

"Jonathan Wood" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>
> --
> Jonathan Wood
> SoftCircuits Programming
> http://www.softcircuits.com
>