July 20, 2010 at 2:37 pm
looking to create function that returns the greatest single number in a fixed series of numbers.
So for exmple, select udf_GetGreatestNum(2,56,9,3,1, 0) returns 56.
Any assistance would be greatly appreciated.
Al
July 21, 2010 at 3:09 am
1. As long as your naming is consistent, use of udf_ prefix is fine, as it helps to identify UDF just from its name. Until you have insomnia, I would recommend reading something more intresting than ISO-11179 😀
2. Here is code (which can be compiled) for the function:
CREATE FUNCTION dbo.udf_GetGreatestNum
(@p1 INTEGER,
@p2 INTEGER,
@p3 INTEGER,
@p4 INTEGER,
@p5 INTEGER,
@p6 INTEGER)
RETURNS INTEGER
WITH SCHEMABINDING
AS
BEGIN
RETURN (SELECT MAX(parm)
FROM (VALUES (@p1), (@p2), (@p3), (@p4), (@p5), (@p6)) X(parm)
WHERE parm IS NOT NULL);
END
Please note couple of things:
1. I have removed "= NULL" from parameter declaration, as there is no such thing as really optional parameters in UDF, you would still need to use DEFAULT keyword in place of missing parameters when calling such function.
2. WITH SCHEMABINDING will make sure that SQLServer takes this function as deterministic, therefore its performance will be significantly better (you can test and compare yourself).
July 21, 2010 at 7:19 am
Thanks folks. VERY MUCH APPRECIATED for the assistance!
al
July 21, 2010 at 8:26 am
Hi,
I think a tally table solution is better here as it accepts any number of values in the input:
create function dbo.udf_Get_Max_From_String (
@string_input varchar(4000)
)
returns int
with schemabinding
as
begin
-- make a table to hold the split string elements
declare @string_parts table (
string_part int
)
declare @return int
-- add leading and trailing commas to the input if required
-- used to ensure correct operation of substring
if left(@string_input,1) <> ','
begin
set @string_input = ',' + @string_input
end
if right(@string_input,1) <> ','
begin
set @string_input = @string_input + ','
end
-- use a tally table to split the input
insert @string_parts (string_part)
select substring(@string_input
,Tally_Number + 1
,charindex(','
,@string_input
,Tally_Number + 1
) - Tally_Number-1
)
from dbo.tally_table -- standard numbers table
where Tally_Number < len(@string_input)
and substring(@string_input,Tally_Number,1) = ','
select @return = max(string_part) from @string_parts
return @return
end
See this article[/url] for an explanation of the tally table and how to use it!
Regards, Iain
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply