How to create and use a table valued function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to create and use a table valued function

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-11-30 : 22:20:15

I would like a function that accepts nvarchar as @DateRange and returns StartDateRange DateTime, EndDateRange DateTime, DateRangeSelection nvarchar as one row in three columns. I would then like to know how to use. Here are my two pieces of code so far.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_DateSel](@DateRange nvarchar(30))
RETURNS @VALUES TABLE (StartDateRange DateTime, EndDateRange DateTime, DateRangeSelection nvarchar (30))

AS

BEGIN

INSERT @VALUES(StartDateRange, EndDateRange, DateRangeSelection)
SELECT StartDateRange, EndDateRange, DateRangeSelection
FROM (SELECT CAST(@DateRange AS nvarchar(30)) AS DateRangeSelection,
CASE
WHEN @DateRange = 'Current Hour' THEN DATEADD(HOUR, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last Hour' THEN DATEADD(HOUR, - 1, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
END AS StartDateRange,
CASE
WHEN @DateRange = 'Current Hour' THEN GETDATE()
WHEN @DateRange = 'Last Hour' THEN DATEADD(HOUR, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
END AS EndDateRange) AS DateRangeSel

RETURN

END

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[Test_Function_DateSel]

@DateRange nvarchar(30)

AS

SELECT StartDateRange, EndDateRange, DateRangeSelection
FROM (Select dbo.fn_DateSel (@DateRange) As DateSel) As T

Begin

Select EventType
From EventDetails
Where EventStartTime BETWEEN StartDateRange AND EndDateRange

End

Return

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-11-30 : 23:20:26

And the answer seems to be:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_DateSel](@DateRange nvarchar(30))
RETURNS @VALUES TABLE (StartDateRange DateTime, EndDateRange DateTime)

AS

BEGIN

INSERT @VALUES(StartDateRange, EndDateRange)

SELECT StartDateRange =
CASE
WHEN @DateRange = 'Current Hour' THEN DATEADD(HOUR, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last Hour' THEN DATEADD(HOUR, - 1, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 12 Hours' THEN DATEADD(HOUR, - 12, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 24 Hours' THEN DATEADD(HOUR, - 24, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Today' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN @DateRange = 'Yesterday' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), - 1)
WHEN @DateRange = 'Last Weekend' THEN DATEADD(WEEK, - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 5))
WHEN @DateRange = 'Current Week' THEN DATEADD(WEEK, 0, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 7 days' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), - 7)
WHEN @DateRange = 'Last 5 Week Days' THEN DATEADD(WEEK, - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Last Week' THEN DATEADD(WEEK, - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 2 Weeks' THEN DATEADD(WEEK, - 2, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Month' THEN DATEADD(MONTH, 0, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 30 days' THEN DATEADD(DAY, - 30, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 90 days' THEN DATEADD(DAY, - 90, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
WHEN @DateRange = 'Last Month' THEN DATEADD(MONTH, - 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 3 Months' THEN DATEADD(MONTH, - 3, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN @DateRange = 'First Quarter' THEN DATEADD(QUARTER, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
WHEN @DateRange = 'Second Quarter' THEN DATEADD(QUARTER, 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
WHEN @DateRange = 'Third Quarter' THEN DATEADD(QUARTER, 2, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
WHEN @DateRange = 'Fourth Quarter' THEN DATEADD(QUARTER, 3, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Quarter' THEN DATEADD(QUARTER, 0, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0))
WHEN @DateRange = 'Last Quarter' THEN DATEADD(QUARTER, - 1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Year' THEN DATEADD(YEAR, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Financial Year' THEN DATEADD(QUARTER, 2, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last Financial Year' THEN DATEADD(QUARTER, 2, DATEADD(YEAR, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)))
WHEN @DateRange = 'Last Year' THEN DATEADD(YEAR, - 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
END,
EndDateRange =
CASE
WHEN @DateRange = 'Current Hour' THEN GETDATE()
WHEN @DateRange = 'Last Hour' THEN DATEADD(HOUR, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 12 Hours' THEN DATEADD(HOUR, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 24 Hours' THEN DATEADD(HOUR, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Today' THEN GETDATE()
WHEN @DateRange = 'Yesterday' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN @DateRange = 'Last Weekend' THEN DATEADD(WEEK, - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 7))
WHEN @DateRange = 'Current Week' THEN GETDATE()
WHEN @DateRange = 'Last 7 days' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN @DateRange = 'Last 5 Week Days' THEN DATEADD(WEEK, - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 5))
WHEN @DateRange = 'Last Week' THEN DATEADD(WEEK, 0, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 2 Weeks' THEN DATEADD(WEEK, 0, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Month' THEN GETDATE()
WHEN @DateRange = 'Last 30 days' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN @DateRange = 'Last 90 days' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN @DateRange = 'Last Month' THEN DATEADD(MONTH, 0, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 3 Months' THEN DATEADD(MONTH, 0, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN @DateRange = 'First Quarter' THEN DATEADD(QUARTER, 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
WHEN @DateRange = 'Second Quarter' THEN DATEADD(QUARTER, 2, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
WHEN @DateRange = 'Third Quarter' THEN DATEADD(QUARTER, 3, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
WHEN @DateRange = 'Fourth Quarter' THEN DATEADD(QUARTER, 4, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Quarter' THEN GETDATE()
WHEN @DateRange = 'Last Quarter' THEN DATEADD(QUARTER, 0, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Year' THEN GETDATE()
WHEN @DateRange = 'Current Financial Year' THEN DATEADD(QUARTER, 2, DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), 0))
WHEN @DateRange = 'Last Financial Year' THEN DATEADD(QUARTER, 2, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last Year' THEN DATEADD(YEAR, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
END

RETURN

END

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[Test_Functions_DateTime]

@DateRange nvarchar(30)

AS

Declare @StartDateRange AS DateTime
Declare @EndDateRange AS DateTime

SET @StartDateRange = (Select StartDateRange From fn_DateSel (@DateRange))
SET @EndDateRange = (Select EndDateRange From fn_DateSel (@DateRange))

Begin

Select dbo.EventTypeConv (EventType) As EventType
From EventDetails
Where EventStartTime BETWEEN @StartDateRange AND @EndDateRange

End

Return

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-30 : 23:33:54

yup. that depends on what type of UDF you create. For table valued UDFs you can do like whats shown above. for scalar udfs you can use it like a field in select and where like