By Kalman Toth, M.Phil., M.Phil., MCDBA

December 24, 2005

Database developers and SQL Server 2005 DBA-s don't usually think of T-SQL as a traditional computer language like C++ with huge application libraries or API-s. The underlying reason is that the function implementation in T-SQL is a subset of the implementation in traditional computer languages.

Despite the limitation, you can use the function capacity of SQL Server 2005 T-SQL to build a very useful function library for yourself and your team. The perfect functional candidate for function implementation is a script, which can be reused again and again. If it is usable only once, it hardly pays to build a function. In that case it can stay part of a stored procedure or script.

Below is a validation function example. Validation functions are important for the health of a database and data warehouse. You want to put clean, validated data into tables. If you don't perform sufficient data validation, then bad data will come back and haunt you days, weeks even months later. The best place to validate is the source, or the first entry point of the data into the database.

This is the code sample:

USE AdventureWorks;
GO
CREATE FUNCTION fnValidateSSN( @SSN varchar(50))
RETURNS BIT
AS
BEGIN

DECLARE @ValidFlag BIT

SET @ValidFlag = 0

IF @SSN LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'
BEGIN

IF LEFT(@SSN,3) != '000' AND SUBSTRING(@SSN,5,2) != '00'
AND RIGHT(@SSN,4) != '0000'
BEGIN
SET @ValidFlag = 1
RETURN ( @ValidFlag)
END
END

IF @SSN LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
BEGIN

IF LEFT(@SSN,3) != '000' AND SUBSTRING(@SSN,4,2) != '00'
AND RIGHT(@SSN,4) != '0000'
BEGIN
SET @ValidFlag = 1
RETURN ( @ValidFlag)
END
END

RETURN ( @ValidFlag)
END
GO

Here are the usage examples:

select dbo.fnValidateSSN('123-55-7777')
go

1

select dbo.fnValidateSSN('123-00-7777')
go

0

select dbo.fnValidateSSN('123550000')
go

0

select dbo.fnValidateSSN('123557777')
go

1