Posted by ujjc001 on 06/22/06 22:01

Hello all-
Given the following UDF, in sql 2000 can it be sped up, complied or
anything of the like. A query returning 300,000 + rows times out when
ran through the udf, inline case statements returns the rows in 5
seconds.
Thanks!
Jeff

CREATE FUNCTION dbo.TimeFormat
(
@input datetime,
@groupformat varchar(20) --DAY, WEEK, MONTH
)

RETURNS datetime

AS

BEGIN
declare @dtvar as datetime

if @groupformat = 'DAY'
set @dtvar = CAST(CONVERT(char(10), @input, 101) AS datetime)
else if @groupformat = 'WEEK'
set @dtvar = CAST(DATEADD([DAY], 1 - DATEPART(dw, CONVERT(char(10),
@input, 101)), CONVERT(char(10), @input, 101)) AS datetime)
else if @groupformat = 'MONTH'
set @dtvar = CAST(CONVERT(CHAR(6), @input, 112) + '01' AS datetime)
return @dtvar
END

[Back to original message]