Window function not working on non selected rows
LEAD() is calculated after the WHERE clause has limited the result to one row (I'm assuming that (PNumber) is unique). That's why you always get NULL for @NextPartitionRowNum.

You could use your select, only if you are sure that there are no gaps in the PNumber values:

SELECT @NextPartitionRowNum = MaxRows 
    FROM #MaxPartitionRows
    WHERE PNumber = @iMinPartitionNumberToSwitchOut + 1 ;


Alternatively, you could use this (gaps or not, doesn't affect it):

SELECT @vcPartitionFunction    = p.PFName,
    @vcBoundaryValue        = p.Value,
    @NextPartitionRowNum    = n.MaxRows
FROM #MaxPartitionRows AS p
  OUTER APPLY
    ( SELECT TOP (1) n.MaxRows
      FROM #MaxPartitionRows AS n
      WHERE n.PNumber &gt; p.PNumber
      ORDER BY n.PNumber
    ) AS n
WHERE p.PNumber = @iMinPartitionNumberToSwitchOut ;


If you prefer window functions, this would work, too. First calculate LEAD() over the entire table, then restrict with WHERE:

WITH cte AS
  ( SELECT PFName, Value, PNumber, 
           NextMaxRows = LEAD(MaxRows) OVER (ORDER BY PNumber)
    FROM #MaxPartitionRows
  )
SELECT @vcPartitionFunction    = PFName,
       @vcBoundaryValue        = Value,
       @NextPartitionRowNum    = NextMaxRows
FROM cte
WHERE PNumber = @iMinPartitionNumberToSwitchOut ;