Execute the following
Microsoft SQL Server T-SQL script in SSMS Query Editor to create a minmax table-valued user-defined function (UDF) which returns
a table:
USE AdventureWorks;
GO
-- Minimum function - Maximum function - SQL minmax function
-- T-SQL table-valued function - T-SQL inline function
CREATE FUNCTION fnMinMax
(@Input1 MONEY,
@Input2 MONEY)
RETURNS TABLE
AS
RETURN
(SELECT CASE
WHEN @Input1 < @Input2 THEN @Input1
WHEN @Input2 < @Input1 THEN @Input2
ELSE COALESCE(@Input1,@Input2)
END AS Minimum,
CASE
WHEN @Input1 > @Input2 THEN @Input1
WHEN @Input2 > @Input1 THEN @Input2
ELSE COALESCE(@Input1,@Input2)
END AS Maximum);
GO
SELECT * FROM fnMinMax(101, 101.1)
/* Minimum Maximum
101.00 101.10
*/
SELECT * FROM fnMinMax(101.2, 101.1)
/* Minimum Maximum
101.10 101.20
*/
SELECT * FROM fnMinMax(NULL, 101.1)
/* Minimum Maximum
101.10 101.10
*/
SELECT * FROM fnMinMax(101.2, NULL)
/* Minimum Maximum
101.20 101.20
*/
|