Other Approaches for Older Versions
If you have the older versions then you cannot use the in-built function and for that, you have to use other approaches. There are different ways present but here we are going to discuss the approaches listed below.
- Using UDF
- Using CASE
- Using IIF
Before moving to this approach, we have to look at one problem.
While writing any user-defined approach we will be in a dilemma of using the data type because we cannot write the same logic for every different datatype. And for that, we are going to use “sql_variant”, this will help us to avoid the above problem because this will be ok with every datatype.
1. Using User-Defined Function
So, in UDF we will create a simple function that will take two input parameters and one will be the output parameter which is the result variable. In the body, we will compare the variables and return the maximum value. For comparison, we will use the if statement.
CREATE FUNCTION Maxi(@var1 sql_variant, @var2 sql_variant)
RETURNS sql_variant
AS
BEGIN
IF @var1 > @var2
RETURN @var1
RETURN isnull(@var2,@var1)
END
Go
Now we can use this function for finding the max just like Math.Max() in .NET.
Declare @var1 float = 1.2;
Declare @var2 float = 2.2;
select [dbo].Maxi(@var1, @var2)
go
Output:
Explanation: The declared variables @var1 and @var2 have values 1.2 and 2.2, respectively. The SELECT statement calls the user-defined function [dbo].Maxi(), comparing the variables and returning the maximum value, which is 2.2 in this case.
2. Using Case
In this case, we can give the expression in the when clause and the output, or the maximum value can be given with the then and else statement.
Declare @var1 float = 1.2;
Declare @var2 float = 2.2;
select case when @var1 > @var1
then @var1
else @var2
end
Output:
Using this we can compare many variables with different cases. But only the problem with this is that as the no. of variable increases the size of the query will increase and also we have to write so many clauses to get one outcome.
3. Using IIF
IIF comes with three parameters, first, the expression will give true or false, and based on that other two parameters are being used. For example, if the expression returns true then the first variable will be returned and if it returns false then the third variable will be given as output.
Syntax:
SELECT IIF (condition, true_value, false_value);
Example
SELECT IIF(1>2,1,2)
Output:
This is the simplest way of getting the maximum number if the count is 2. But again if the no. of variables is more than two then the process of finding would be tough.
SQL Server Max Function Takes Two Values Like Math.Max in .NET
In SQL Server 2022, the GREATEST() function simplifies finding the maximum value among a set of parameters. However, for older versions without this function, we explore alternative approaches akin to the Math.Max() function in .NET. This article presents the syntax of GREATEST() and introduces three alternative methods: using a User-Defined Function (UDF), employing the CASE statement, and leveraging the IIF function.