GreaterOf Series

SQL Server has no built-in way to return the greatest of two or more values passed as arguments. This family of four tiny functions fills that gap: GreaterOfTheTwo, GreaterOfTheThree, GreaterOfTheFour and GreaterOfTheFive. Each one unions its arguments into an inline row-set and returns MAX - handy inside SELECT lists when you need a single "best" value without writing nested CASE statements.

Example: SELECT dbo.GreaterOfTheThree(10, 42, 7) -- returns 42

CREATE FUNCTION GreaterOfTheTwo
(
	@One BigInt,
	@Two BigInt
)
RETURNS INT
AS
BEGIN
	RETURN
	(
		SELECT
			MAX(V.Value)
		FROM
		(
			SELECT @One AS Value
			UNION ALL SELECT @Two
		) AS V
	)
END
GO

CREATE FUNCTION GreaterOfTheThree
(
	@One BigInt,
	@Two BigInt,
	@Three BigInt
)
RETURNS INT
AS
BEGIN
	RETURN
	(
		SELECT
			MAX(V.Value)
		FROM
		(
			SELECT @One AS Value
			UNION ALL SELECT @Two
			UNION ALL SELECT @Three
		) AS V
	)
END
GO

CREATE FUNCTION GreaterOfTheFour
(
	@One BigInt,
	@Two BigInt,
	@Three BigInt,
	@Four BigInt
)
RETURNS INT
AS
BEGIN
	RETURN
	(
		SELECT
			MAX(V.Value)
		FROM
		(
			SELECT @One AS Value
			UNION ALL SELECT @Two
			UNION ALL SELECT @Three
			UNION ALL SELECT @Four
		) AS V
	)
END
GO

CREATE FUNCTION GreaterOfTheFive
(
	@One BigInt,
	@Two BigInt,
	@Three BigInt,
	@Four BigInt,
	@Five BigInt
)
RETURNS INT
AS
BEGIN
	RETURN
	(
		SELECT
			MAX(V.Value)
		FROM
		(
			SELECT @One AS Value
			UNION ALL SELECT @Two
			UNION ALL SELECT @Three
			UNION ALL SELECT @Four
			UNION ALL SELECT @Five
		) AS V
	)
END
GO