Posted by ujjc001 on 06/23/06 14:22

how would I use an inline udf in an existing stored procedure? I would
need to pass in a field such as table.date below :

SELECT
(
SELECT XDate FROM TimeFormat (table.Date, 'Month')
) AS formattedXDate,
table.Title,
table2.Title AS title2.....



Mike C# wrote:
> How about an inline table-valued UDF? And getting rid of all those CASTs
> and CONVERTs... After all you're inputting a DATETIME and returning a
> DATETIME:
>
> CREATE FUNCTION dbo.TimeFormat
> (
> @input DATETIME,
> @groupformat VARCHAR(20)
> )
> RETURNS TABLE
> AS
> RETURN
> (
> SELECT CASE @groupformat
> WHEN 'DAY' THEN @input
> WHEN 'WEEK' THEN DATEADD(day, 1 - DATEPART(dw, @input), @input)
> WHEN 'MONTH' THEN DATEADD(day, -DATEPART(d, @input)+1, @input)
> END AS [XDate]
> )
>
> SELECT XDate
> FROM dbo.TimeFormat ('2006-06-22', 'DAY')
>
> SELECT XDate
> FROM dbo.TimeFormat ('2006-06-22', 'WEEK')
>
> SELECT XDate
> FROM dbo.TimeFormat ('2006-06-22', 'MONTH')
>
> "ujjc001" <ujjc001@gmail.com> wrote in message
> news:1151013715.191251.45940@i40g2000cwc.googlegroups.com...
> > 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]