Intentionally Adding n Empty Rows

The original MS-SQL-7.0 stored proc returned a recordset to an MS-ACCESS ADP front-end which printed a report showing five records per page.

ALTER   PROCEDURE proc_med_sheets
 (
  @PersonID [int],
  @Month  [datetime] = NULL
 )

/*
 Return the data to print a med sheet
 The input parameters are the unique ID of the individual
 and the date of the month/year to print Medication Administration
 Records for
*/
AS
SET NOCOUNT ON
DECLARE @SheetDate [datetime] -- IF no date is provided, use the first day
    -- of the month of the current system date
 Set @SheetDate = 
  ltrim(str(DATEPART (mm, isnull(@Month, GETDATE()))))
  + '/01/' +
  ltrim(str(DATEPART (yy, isnull(@Month, GETDATE()))))
 

SELECT FirstName, MiddleName, LastName, Birthdate, Allergies_Meds, Allergies_Food,
 Allergies_Envir, Other_Consult_Form_Alerts, PCP, Telephone, Fax, 
 Sequence, Name as MedName, Strength, [Special Instructions], Start, Stop,
 Time1, Time2, Time3, Time4, @SheetDate as Month
FROM dbo.VIEW_med_sheets
WHERE
 (PersonID = @PersonID )
 AND
 ((Start IS NULL) OR (Start='')
  OR (Start < DATEADD (mm,1,@SheetDate))) -- Include meds that start during this month
 AND
 ((Stop IS NULL) OR (Stop='')
  OR (Stop >= @SheetDate)) -- Include meds that stop on or after the 1st of the month
 AND Sequence > 0
ORDER BY Sequence
RETURN

Problem was, the finished product wouldn't work in practice because if the last page didn't have five rows of data (five different medication orders) then that portion would be blank. The staff administering medications preferred to have empty, formatted rows in which they could hand-write new orders that occurred throughout the month.

Yes: all formatting should happen on the client, not in the stored procedure!
No: I didn't do it that way because (1) MS-Access ADPs can't have local tables and (2) the ADP front-end is a distributed application; changes to it have to propagate through installing on many machines

Second-best: I rewrote the stored proc to add empty rows which the Access front-end would include as formatted rows on the report.

ALTER  PROCEDURE proc_med_sheets
 (
  @PersonID [int],
  @Month  [datetime] = NULL
 )

/*
 Return the data to print a med sheet
 The input parameters are the unique ID of the individual
 and the date of the month/year to print Medication Administration
 Records for
*/

/* Revised 1/27/09
 Return blank rows to print a complete med sheet
 i.e, each sheet contains 5 rows, so if the last sheet
 will only contain one row, append four blank rows,
 if the last sheet contains two rows, append three, and so on
*/

AS
SET NOCOUNT ON

DECLARE @RowsPerPage INT -- How many rows appear on a 'full' page in the final report
SET @RowsPerPage = 5
DECLARE @Remain INT -- Modulo of rows returned
DECLARE @SheetDate [datetime] -- IF no date is provided, use the first day
    -- of the month of the current system date
 Set @SheetDate =
  ltrim(str(DATEPART (mm, isnull(@Month, GETDATE()))))
  + '/01/' +
  ltrim(str(DATEPART (yy, isnull(@Month, GETDATE()))))
 

  SELECT FirstName, MiddleName, LastName, Birthdate, Allergies_Meds, Allergies_Food,
  Allergies_Envir, Other_Consult_Form_Alerts, PCP, Telephone, Fax,
  Sequence, Name as MedName, Strength, [Special Instructions], Start, Stop,
  Time1, Time2, Time3, Time4, @SheetDate as Month
    INTO #Temp 
    FROM dbo.VIEW_med_sheets
   WHERE
  (PersonID = @PersonID )
  AND
  ((Start IS NULL) OR (Start='')
   OR (Start < DATEADD (mm,1,@SheetDate))) -- Include meds that start during this month
  AND
  ((Stop IS NULL) OR (Stop='')
   OR (Stop >= @SheetDate)) -- Include meds that stop on or after the 1st of the month
  AND Sequence > 0
      ORDER BY Sequence

SET @Remain = @@ROWCOUNT % @RowsPerPage
IF @Remain > 0
BEGIN
 WHILE @Remain < @RowsPerPage
  BEGIN
  INSERT INTO #Temp
  SELECT Top 1
   T.FirstName,
   T.MiddleName,
   T.LastName,
   T.Birthdate,
   T.Allergies_Meds,
   T.Allergies_Food,
   T.Allergies_Envir,
   T.Other_Consult_Form_Alerts,
   T.PCP,
   T.Telephone,
   T.Fax,
   T.Sequence + 1,
   Null,
   Null,
   Null,
   Null,
   Null,
   Null, Null, Null, Null,
   @SheetDate
  FROM #Temp T
      ORDER BY Sequence DESC
  
  SET @Remain = @Remain + 1
  END
END

SELECT * FROM #Temp

RETURN

The magic happens in the #Temp table. The @@ROWCOUNT modulo 5 gives the 'odd' number or rows which will appear on the last page of the report. If that remainder is zero, the last page will be full and no further attention is needed. If there are from one to four records, then the temp table needs additional rows, with appropriate fields given as NULL, and a Sequence which will be utilized by the client for row ordering. To get those 'empty' rows onto the last page of the report, the Sequence field of those rows is one higher than the highest already found in #Temp.