LesserOf function
-
Today I wanted a function (in SQL Server 2008) to return the lesser of two integers, but it didn't seem to exist, so I wrote one, then decided that it might be somewhat more useful sometime in the future if it wasn't coded for integers, so I changed it to use numeric instead:
CREATE FUNCTION dbo.LesserOf
(
@Op1 NUMERIC
,
@Op2 NUMERIC
)
RETURNS NUMERIC
AS
BEGIN
DECLARE @result NUMERICIF ( @Op1 < @Op2 )
SET @result = @Op1
ELSE
SET @result = @Op2RETURN @result
ENDIs there some odd limitation involved with numeric that I don't know about (I never use them)? Is there a better way to implement this simple function (not a CASE)? Is there a built-in LesserOf function?
-
Today I wanted a function (in SQL Server 2008) to return the lesser of two integers, but it didn't seem to exist, so I wrote one, then decided that it might be somewhat more useful sometime in the future if it wasn't coded for integers, so I changed it to use numeric instead:
CREATE FUNCTION dbo.LesserOf
(
@Op1 NUMERIC
,
@Op2 NUMERIC
)
RETURNS NUMERIC
AS
BEGIN
DECLARE @result NUMERICIF ( @Op1 < @Op2 )
SET @result = @Op1
ELSE
SET @result = @Op2RETURN @result
ENDIs there some odd limitation involved with numeric that I don't know about (I never use them)? Is there a better way to implement this simple function (not a CASE)? Is there a built-in LesserOf function?
Try this as an exercise....
DECLARE @N NUMERIC -- No precision or dcale
SET @N = 12.345PRINT @N
DECLARE @N2 NUMERIC(5,3)
SET @N2 = 12.345PRINT @N2
DECLARE @N3 NUMERIC(4,3)
SET @N3 = 12.345PRINT @N3
The precision and scale parameters are important!
I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife
-
Today I wanted a function (in SQL Server 2008) to return the lesser of two integers, but it didn't seem to exist, so I wrote one, then decided that it might be somewhat more useful sometime in the future if it wasn't coded for integers, so I changed it to use numeric instead:
CREATE FUNCTION dbo.LesserOf
(
@Op1 NUMERIC
,
@Op2 NUMERIC
)
RETURNS NUMERIC
AS
BEGIN
DECLARE @result NUMERICIF ( @Op1 < @Op2 )
SET @result = @Op1
ELSE
SET @result = @Op2RETURN @result
ENDIs there some odd limitation involved with numeric that I don't know about (I never use them)? Is there a better way to implement this simple function (not a CASE)? Is there a built-in LesserOf function?
I think T-SQL needs the
Leastfunction from PL-SQL. It's more generic.Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
Try this as an exercise....
DECLARE @N NUMERIC -- No precision or dcale
SET @N = 12.345PRINT @N
DECLARE @N2 NUMERIC(5,3)
SET @N2 = 12.345PRINT @N2
DECLARE @N3 NUMERIC(4,3)
SET @N3 = 12.345PRINT @N3
The precision and scale parameters are important!
I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife
Ah, I thought that might be the case, thanks. So what do you suggest?
real? -
Ah, I thought that might be the case, thanks. So what do you suggest?
real?You could do this...
CREATE FUNCTION dbo.LesserOf
(
@Op1 NUMERIC(18,7)
,
@Op2 NUMERIC(18,7)
)
RETURNS NUMERIC(18,7)
AS
BEGIN
DECLARE @result NUMERIC(18,7)IF ( @Op1 < @Op2 )
SET @result = @Op1
ELSE
SET @result = @Op2RETURN @result
END...or set the precision and scale to something bigger (20,7)...will give 13 significant numbers and 7 decimal places. (I don't usually use real.)
I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife