Snippet Name: Refactor_SQL_with_Tally
Tags:
Created Date: 2025-06-16 13:12:43
Last Modified Date: 2025-05-31 10:32:04
Dependencies:
DBA Notes:
Sample Output:
Title: Refactor SQL with Tally
Author: user
the attached SQL server stored procedure has a while loop, which i believe can be eliminated with a Tally or Numbers table. can you refactor teh code for me to use an inline tally table, something like this? WITH
T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
, (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
, (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
, (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
, (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
, (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
, (0),(0),(0),(0)) AS X(N))
, Tally AS (SELECT TOP(@Count)
rw = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM T T1 -- Max = 64
, T T2 -- Max = 4,096
, T T3 -- Max = 262,144
, T T4 -- Max = 16,777,216
)
SELECT
rw
, seqNum = @low + rw - 1
FROM Tally;
Author: ChatGPT
Thanks! Based on the contents of `[GYMT_ODB].[GYMT_stp_GetExercisePastStats]`, the procedure uses a `WHILE` loop-like pattern with a temp table and a `Row_Number()` to page through a result set.
Your request is to eliminate that approach and use a Tally table for the `RowNum BETWEEN @Start AND @End` part. Good news: you don???t need a `WHILE` loop at all ??? this can be entirely handled with an inline tally table as you proposed.
Here???s the **refactored version** of the procedure using a **CTE-based Tally table** for paging, replacing the logic that previously used `RowNum BETWEEN @Start AND @End` and a temp table:
---
```sql
IF OBJECT_ID('[GYMT_ODB].[GYMT_stp_GetExercisePastStats]') IS NOT NULL
DROP PROCEDURE [GYMT_ODB].[GYMT_stp_GetExercisePastStats]
GO
CREATE PROCEDURE [GYMT_ODB].[GYMT_stp_GetExercisePastStats]
@UserID bigint,
@ExerciseID int,
@Start int,
@Count int
WITH RECOMPILE
AS
BEGIN
DECLARE @TotalFinished int
-- Total Count
SELECT @TotalFinished = TotalFinished
FROM GYMT_Exercise_Stats
WHERE ForUser = @UserID AND ForExercise = @ExerciseID
SELECT ISNULL(@TotalFinished, 0) AS 'Total'
IF (@TotalFinished > 0)
BEGIN
;WITH t AS
(
SELECT
de.DailyExerciseID,
dw.DailyWorkoutID,
dw.DailyWorkout,
dw.TCalories,
dw.ACalories,
CAST(dw.[Date] AS datetime) AS 'Date',
de.ExerciseLocation,
ROW_NUMBER() OVER (ORDER BY dw.[Date] DESC, dw.DailyWorkoutID DESC) AS RowNum
FROM GYMT_DailyExercise de
JOIN GYMT_DailyWorkout dw ON de.ForDailyWorkout = dw.DailyWorkoutID
WHERE
de.ForExercise = @ExerciseID
AND de.ForUser = @UserID
AND de.FinishTime IS NOT NULL
AND dw.WState = 3
)
, T(N) AS
(
SELECT N FROM
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0)) AS X(N)
)
, Tally AS
(
SELECT TOP (@Count)
RowNum = @Start + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
FROM T T1, T T2, T T3
)
SELECT t.*
FROM t
JOIN Tally ON t.RowNum = Tally.RowNum
OPTION (OPTIMIZE FOR (@ExerciseID UNKNOWN, @UserID UNKNOWN));
-- Set detail
SELECT
ds.ForDailyExercise,
ds.SetID,
ds.Reps,
ds.Weight,
ds.Distance,
ds.Time,
ds.Calories,
ds.Level,
ds.Speed
FROM GYMT_DailySet ds
JOIN
(
SELECT t.DailyExerciseID
FROM t
JOIN Tally ON t.RowNum = Tally.RowNum
) x ON ds.ForDailyExercise = x.DailyExerciseID
END
END
GO
```
---
??? **Changes made**:
- Removed use of `#PreDailyExerciseIDs` temp table.
- Replaced `RowNum BETWEEN` logic with a `Tally` CTE that generates the row numbers to retrieve.
- Reused the `t` CTE so we don't calculate the full result set twice.
- Ensured compatibility by limiting Tally to `@Count` and starting at `@Start`.
Let me know if you'd like to adjust the max rows supported, optimize the join, or build this as a reusable function!
Author: user
this was really close. the logic at the top was flawless, but since the procedure returns two data sets, the second SELECT does top have access to the CTE aliased as "t"
Author: ChatGPT
Ah, you're absolutely right ??? the `t` and `Tally` CTEs go out of scope after the first `SELECT`. To fix that while maintaining clean and efficient logic, we can refactor the **paging logic into a derived table** (or a temp table if needed), so it's accessible to both result sets.
Here???s the updated version that materializes the result of the first query into a temporary table (`#PagedExercises`), so it's reusable:
---
```sql
IF OBJECT_ID('[GYMT_ODB].[GYMT_stp_GetExercisePastStats]') IS NOT NULL
DROP PROCEDURE [GYMT_ODB].[GYMT_stp_GetExercisePastStats]
GO
CREATE PROCEDURE [GYMT_ODB].[GYMT_stp_GetExercisePastStats]
@UserID bigint,
@ExerciseID int,
@Start int,
@Count int
WITH RECOMPILE
AS
BEGIN
DECLARE @TotalFinished int
-- Total Count
SELECT @TotalFinished = TotalFinished
FROM GYMT_Exercise_Stats
WHERE ForUser = @UserID AND ForExercise = @ExerciseID
SELECT ISNULL(@TotalFinished, 0) AS 'Total'
IF (@TotalFinished > 0)
BEGIN
-- Inline Tally CTE
;WITH T(N) AS
(
SELECT N FROM
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0)) AS X(N)
)
, Tally AS
(
SELECT TOP (@Count)
RowNum = @Start + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
FROM T T1, T T2, T T3
)
SELECT
de.DailyExerciseID,
dw.DailyWorkoutID,
dw.DailyWorkout,
dw.TCalories,
dw.ACalories,
CAST(dw.[Date] AS datetime) AS [Date],
de.ExerciseLocation,
t.RowNum
INTO #PagedExercises
FROM
(
SELECT
de.DailyExerciseID,
dw.DailyWorkoutID,
dw.DailyWorkout,
dw.TCalories,
dw.ACalories,
dw.[Date],
de.ExerciseLocation,
ROW_NUMBER() OVER (ORDER BY dw.[Date] DESC, dw.DailyWorkoutID DESC) AS RowNum
FROM GYMT_DailyExercise de
JOIN GYMT_DailyWorkout dw ON de.ForDailyWorkout = dw.DailyWorkoutID
WHERE
de.ForExercise = @ExerciseID
AND de.ForUser = @UserID
AND de.FinishTime IS NOT NULL
AND dw.WState = 3
) de
JOIN Tally t ON de.RowNum = t.RowNum
OPTION (OPTIMIZE FOR (@ExerciseID UNKNOWN, @UserID UNKNOWN));
-- First result set: paged workouts
SELECT * FROM #PagedExercises;
-- Second result set: related sets
SELECT
ds.ForDailyExercise,
ds.SetID,
ds.Reps,
ds.Weight,
ds.Distance,
ds.Time,
ds.Calories,
ds.Level,
ds.Speed
FROM GYMT_DailySet ds
JOIN #PagedExercises pe ON ds.ForDailyExercise = pe.DailyExerciseID;
DROP TABLE #PagedExercises;
END
END
GO
```
---
??? **Fixes applied:**
- Moved `t` logic into a derived table (`de`) that gets joined with `Tally`, and output stored in `#PagedExercises`.
- Now both the summary and detailed sets can use the same data.
- Still benefits from the inline tally approach ??? no loops, and pagination remains efficient.
Let me know if you'd prefer a table variable over a temp table, or if you want to encapsulate tally generation into a view or iTVF!