Some Useful UDFs for SQL Server 2000

,


Introduction

SQL Server 2000 supports User-Defined Functions (UDFs) - one or more

Transact-SQL statements that can be used to encapsulate code for reuse.

User-defined functions cannot make a permanent changes to the data or

modify database tables. UDF can change only local objects for this UDF,

such as local cursors or variables.

There are three types of UDF in SQL Server 2000:

  • Scalar functions

  • Inline table-valued functions

  • Multistatement table-valued functions

Scalar functions return a single data value (not a table)

with RETURNS clause. Scalar functions can use all scalar

data types, with exception of timestamp and user-defined data types.

Inline table-valued functions return the result set of a single

SELECT statement.

Multistatement table-valued functions return a table, that was

built with many TRANSACT-SQL statements.

User-defined functions can be invoked from a query like built-in

functions such as OBJECT_ID, LEN, DATEDIFF, or can be executed

through an EXECUTE statement like stored procedures.

UDF examples

Database creation date

This UDF will return the creation date for a given database (you should

specify database name as parameter for this UDF):

CREATE FUNCTION dbo.DBCreationDate

( @dbname sysname )

RETURNS datetime

AS

BEGIN

DECLARE @crdate datetime

SELECT @crdate = crdate FROM master.dbo.sysdatabases

WHERE name = @dbname

RETURN ( @crdate )

END

GO

This is the example for use:

SELECT dbo.DBCreationDate('pubs')

GO

Date the object was created

This UDF will return the creation date for a given object in the

current database:

CREATE FUNCTION dbo.ObjCreationDate

( @objname sysname)

RETURNS datetime

AS

BEGIN

DECLARE @crdate datetime

SELECT @crdate = crdate FROM sysobjects WHERE name = @objname

RETURN ( @crdate )

END

GO

This is the example for use:

SELECT dbo.ObjCreationDate('authors')

GO

Get date part of datetime value

This UDF will return the date part of datetime value:

CREATE FUNCTION dbo.DatePart

( @fDate datetime )

RETURNS varchar(10)

AS

BEGIN

RETURN ( CONVERT(varchar(10),@fDate,101) )

END

GO

This is the example for use:

SELECT dbo.DatePart('11/11/2000 11:15AM')

GO

Get time part of datetime value

This UDF will return the time part of datetime value:

CREATE FUNCTION dbo.TimePart

( @fDate datetime )

RETURNS varchar(10)

AS

BEGIN

RETURN ( CONVERT(varchar(7),right(@fDate,7),101) )

END

GO

This is the example for use:

SELECT dbo.TimePart('11/11/2000 11:15AM')

GO

Get the number of working days between two dates

This UDF will return the number of working days between two dates

(not including these dates):

CREATE FUNCTION dbo.GetWorkingDays

( @StartDate datetime,

@EndDate datetime )

RETURNS INT

AS

BEGIN

DECLARE @WorkDays int, @FirstPart int

DECLARE @FirstNum int, @TotalDays int

DECLARE @LastNum int, @LastPart int

IF (DATEDIFF(day, @StartDate, @EndDate) < 2)

BEGIN

RETURN ( 0 )

END

SELECT

@TotalDays = DATEDIFF(day, @StartDate, @EndDate) - 1,

@FirstPart = CASE DATENAME(weekday, @StartDate)

WHEN 'Sunday' THEN 6

WHEN 'Monday' THEN 5

WHEN 'Tuesday' THEN 4

WHEN 'Wednesday' THEN 3

WHEN 'Thursday' THEN 2

WHEN 'Friday' THEN 1

WHEN 'Saturday' THEN 0

END,

@FirstNum = CASE DATENAME(weekday, @StartDate)

WHEN 'Sunday' THEN 5

WHEN 'Monday' THEN 4

WHEN 'Tuesday' THEN 3

WHEN 'Wednesday' THEN 2

WHEN 'Thursday' THEN 1

WHEN 'Friday' THEN 0

WHEN 'Saturday' THEN 0

END

IF (@TotalDays < @FirstPart)

BEGIN

SELECT @WorkDays = @TotalDays

END

ELSE

BEGIN

SELECT @WorkDays = (@TotalDays - @FirstPart) / 7

SELECT @LastPart = (@TotalDays - @FirstPart) % 7

SELECT @LastNum = CASE

WHEN (@LastPart < 7) AND (@LastPart > 0) THEN @LastPart - 1

ELSE 0

END

SELECT @WorkDays = @WorkDays * 5 + @FirstNum + @LastNum

END

RETURN ( @WorkDays )

END

GO

This is the example for use:

SELECT dbo.GetWorkingDays ('11/13/2000', '12/27/2000')

GO

Literature

  1. SQL Server Books Online
  2. Super Administrator: Granting Wishes with UDF

    http://msdn.microsoft.com/library/periodic/period00/sql00i11.htm

Rate

Share

Share

Rate