The SQL procedure loops through the results. Why?

Question

SQL

Good afternoon! There is a procedure that displays data from several tables.
If it is executed several times in a row, it shows a different data sample each time.
To be more precise, it outputs the result cyclically in parts.
I can’t understand where the error is, because with the same initial parameters, the result should be the same. I feel that there is a catch in the implementation of paging, but my knowledge is not enough to find an error.
Here is the code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AllSitesGet]
    --Params
  @PageStart      INT          = 0,           --Used for paging
  @PageSize       INT          = 200,         --Used for paging
  @SortColumn     INT = 5,  --Used for Sorting
  @SortDirection  INT   = 1        --Used for Sorting

AS
BEGIN
  SET NOCOUNT ON

  --=========================================================
  --Paging Variables
  --=========================================================
  DECLARE @TotalRows  INT
  
  --=========================================================
  --Temporary table for paging...
  --=========================================================
  CREATE TABLE #Results (      
    RowID              INT IDENTITY(1,1),
    SiteName           VARCHAR(250),
    OID                VARCHAR(256),
    IPAddress          VARCHAR(256),
    Enabled            BIT,
    ProtocolID         INT,
    PSVersion          VARCHAR(32),
    Port               INT,
    RCMServiceAddress  VARCHAR(256),
    RCM_Enabled        BIT
  )  
  --=========================================================
  --Select all records in the Solution table
  --=========================================================
  BEGIN
    INSERT #Results (
      SiteName,
      OID,
      IPAddress,
      Enabled,
      ProtocolID,
      PSVersion,
      Port,
      RCMServiceAddress,
      RCM_Enabled
    )
    SELECT
      SiteName,
      OID,
      IPAddress,
      Enabled,
      ProtocolID,
      PSVersion,
      Port,
      RCMServiceAddress,
      RCM_Enabled
      FROM dbo.Site
     ORDER BY
      CASE WHEN @SortColumn = 5 AND @SortDirection = 2
        THEN SiteName END DESC,
      CASE WHEN @SortColumn = 5 AND @SortDirection != 2
        THEN SiteName END,
      CASE WHEN @SortColumn = 2 AND @SortDirection = 2
        THEN OID END DESC,
      CASE WHEN @SortColumn = 2 AND @SortDirection != 2
        THEN OID END,
      CASE WHEN @SortColumn = 1 AND @SortDirection = 2
        THEN IPAddress END DESC,
      CASE WHEN @SortColumn = 1 AND @SortDirection != 2
        THEN IPAddress END,
      CASE WHEN @SortColumn = 6 AND @SortDirection = 2
        THEN Enabled END DESC,
      CASE WHEN @SortColumn = 6 AND @SortDirection != 2
        THEN Enabled END,
      CASE WHEN @SortColumn = 7 AND @SortDirection = 2
        THEN ProtocolID END DESC,
      CASE WHEN @SortColumn = 7 AND @SortDirection != 2
        THEN ProtocolID END,
      CASE WHEN @SortColumn = 3 AND @SortDirection = 2
        THEN PSVersion END DESC,
      CASE WHEN @SortColumn = 3 AND @SortDirection != 2
        THEN PSVersion END,
      CASE WHEN @SortColumn = 8 AND @SortDirection = 2
        THEN Port END DESC,
      CASE WHEN @SortColumn = 8 AND @SortDirection != 2
        THEN Port END,
      CASE WHEN @SortColumn = 4 AND @SortDirection = 2
        THEN RCMServiceAddress END DESC,
      CASE WHEN @SortColumn = 4 AND @SortDirection != 2
        THEN RCMServiceAddress END,
      CASE WHEN @SortColumn = 9 AND @SortDirection = 2
        THEN RCM_Enabled END DESC,
      CASE WHEN @SortColumn = 9 AND @SortDirection != 2
        THEN RCM_Enabled END
  END  

  --=========================================================      
  --Paging Logic..      
  --=========================================================        
  SELECT @TotalRows = COUNT(*)
    FROM #Results
  SET ROWCOUNT @PageSize
  

  --=========================================================      
  --Select with paging      
  --=========================================================        
  SELECT
    RowID,
    SiteName,
    OID,
    IPAddress,
    Enabled,
    ProtocolID,
    PSVersion,
    Port,
    RCMServiceAddress,
    RCM_Enabled,
    @TotalRows TotalRows
    FROM #Results
   WHERE RowID > @PageStart

  --=========================================================
  --Cleanup
  --=========================================================
  DROP TABLE #Results
    
  --=========================================================
  --Check for any flags caused by SQL
  --=========================================================
  IF @@ERROR <> 0
  BEGIN
    RAISERROR ('AllSitesGet failed - Statement Aborted',16,1)
    RETURN
  END

  SET NOCOUNT OFF
END

Answer 1

Sql does not guarantee storing rows in insertion order, in a temporary table declare RowID as a key or change the condition to

WHERE RowID > @PageStart AND RowID <= @PageStart + @PageSize