Need Help with Stored Procedure

  • Thread starter Thread starter Jonathan Wood
  • Start date Start date
J

Jonathan Wood

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
 
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.
 
Back
Top