Use RAND() in User Defined Function

Here is the issue,  you cannot call a non-deterministic function from inside a user-defined function.

So there is a workaround, By creating a view to return RAND(), call RAND() function inside a view and use that view inside your function, something like below.

CREATE VIEW ReturnRANDValue
AS
SELECT RAND() AS Value
  

Here is the function where we call above view inside this function

CREATE FUNCTION [dbo].[ReturnMobileNotificationCount] (
	@MobilityOrderID INT,
	@MobilityOrderItemID INT,
	@LineStatusMasterID INT,
	@LineSubStatusMasterID INT
	)
RETURNS BIT
AS
BEGIN
	DECLARE @LineSubStatusMatch BIT = 0

	SELECT MobilityOrderID,
		MobilityOrderItemID,
		LineStatusMasterID,
		LineSubStatusMasterID,
		HistoryID,
		ROW_NUMBER() OVER (
			ORDER BY (
					SELECT Value
					FROM ReturnRANDValue
					) DESC
			) SerialNo
	FROM MobileNotificationCriteriaHistory MNC(NOLOCK)
	WHERE MobilityOrderID = @MobilityOrderID
		AND MobilityOrderItemID = @MobilityOrderItemID
	ORDER BY HistoryID DESC

	RETURN @LineSubStatusMatch
END