User Defined Functions, the way to a SQL Developers heart. As you can tell from the title in this article we will be looking at User Defined Functions in MSSQL 2005 (I will soon write one for SQL 2005, as they have have added much more functionality for us to use). In this we will also look at a couple User Defined Functions that I find invaluable in my development life.
User Defined Functions are an intrical part of becoming a professional, efficient SQL programmer. These functions make it possible to write almost scalar code, and allowing for complex calculations without cluttering your stored procedures. The benefits of using User-Defined functions are countless. We can use these functions in so many different places, unlike the traditional Stored Procedure. Having the ability to have a function to act like a table gives the developer the ability to break complex logic into smaller, more maintainable pieces of code.
The ability to pass parameters into these objects (UDF's are database objects, much like classes in, say C#) makes the User Defined Function (UDF) not only invaluable but also an extremely powerful tool for a SQL developer.
There are three types of functions that you can create:
- Scalar
- Inline Table valued
- Multi-Statement Table valued
A
Scalar function is one that returns one of the
scalar data types, such as VARCHAR, CHAR, BOOLEAN, INT and others. Data types Text, NTEXT, IMAGE and TIMESTAMP aren't supported by scalar functions.
An
Inline Table Valued function is one which returns a table data type populated with data. The Inline Table Valued user defined function is a great alternative to say a view.
A
Multi-Statement Table Value also returns a table, and is also an excellent alternative to a View as it can handle multiple statements, whereas a View can support but a single statement. With a Multi-Statement Table Value user defined function you can actually use that in the WHERE statement of your stored procedure.
There are so many benefits to using user defined functions, the first one being that they can be used in any number of places. A user defined function can actually take the place of a physical table in your queries. User Defined Functions allow the SQL Developer to break down their code into smaller and more manageable sections, making for easier maintainability, which is the overall goal of any type of programming.
I am of the belief that all SQL Developers need to have their own code repository which contains some basic functions that perform many useful functions. In this I will be sharing a couple User Defined Functions that I take from employer (or contract) to employer(contract) as they do indeed make my life easier. I learned a long time ago that it is
OK to allow your SQL Server do some of your work for you, thats what it's there for, it's not there to
just store data.
The first User Defined function I have is the ability to format currency, either before storing it in a table, or before passing it back to the method that called it. This functions is of the
Scalar variety as it returns one of the scalar data types. Lets take a look at it
sql
CREATE FUNCTION dbo.udfFormatCurrency (@value decimal(20,2),@dollar_sign BIT = NULL)
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @results VARCHAR(20)
DECLARE @negative BIT
SELECT @negative = CASE WHEN @value < 0 THEN 1 ELSE 0 END
IF @dollar_sign IS NULL SET @dollar_sign = 0
IF @negative = 1 SET @value = -1 * @value
SET @results = CONVERT(VARCHAR, ISNULL(@value, 0))
DECLARE @before VARCHAR(20), @after VARCHAR(20)
IF CHARINDEX ('.', @results )>0
BEGIN
SET @after = SUBSTRING(@results, CHARINDEX ('.', @results ), LEN(@results))
SET @before = SUBSTRING(@results,1, CHARINDEX ('.', @results )-1)
END
ELSE
BEGIN
SET @before = @results
SET @after = ''
END
-- after every third character:
DECLARE @counter INT
IF LEN(@before) > 3
BEGIN
SET @counter = 3
while @counter > 1 AND @counter < LEN(@before)
BEGIN
SET @before = SUBSTRING(@before, 1 ,LEN(@before) - @counter) + ',' + RIGHT(@before,@counter)
SET @counter = @counter + 4
END
END
SET @results = @before + @after
IF @negative = 1 SET @results = '-' + @results
RETURN CASE WHEN @dollar_sign = 1 THEN '$' + @results ELSE @results END
END
As you see this function even offers the option of adding a dollar sign ($) to your formatted results, this comes in handy when the data is being returned for displaying in an application.
The next function I will be showing is one for formatting a US phone number, sorry for all the international readers, I am working on one for international numbers and will add it soon. With this function you can pass it a phone number, either a non-formatted string, or a formatted string, and it will return the formatted phone number in a
(555) 555-1212 format. You can, of course, tweak this function to apply a format needed by your organization.
sql
CREATE FUNCTION dbo.udfFormatPhoneNumber(@PhoneNumber NVARCHAR(60))
RETURNS NVARCHAR(60)
AS
BEGIN
DECLARE @Length INT
DECLARE @TempStr NVARCHAR(30)
--Strip all non-numeric characters from string
WHILE PATINDEX('%[^0-9]%', @PhoneNumber) > 0
SET @PhoneNumber = REPLACE(@PhoneNumber, SUBSTRING(@PhoneNumber,PATINDEX('%[^0-9]%', @PhoneNumber),1),'')
SET @Length = LEN(@PhoneNumber)-10
--Remove a leading "1" if exists (15555551212)
IF SUBSTRING(@PhoneNumber,1,1) ='1' and LEN(@PhoneNumber) >10
BEGIN
SET @PhoneNumber=SUBSTRING(@PhoneNumber,2,LEN(@PhoneNumber) -1)
END
--Format our phone number to the (555) 555-1212 format
SET @TempStr = @PhoneNumber
SET @PhoneNumber = '(' + SUBSTRING(@PhoneNumber,1,3)+ ')' + SUBSTRING(@PhoneNumber,4,3) + '-'+ SUBSTRING(@PhoneNumber,7,4)
-- Returns Value
RETURN @PhoneNumber
END
Next we will take a look at a Table Values function. This is a UDF (User Defined Function) that I use to parse a delimited string. This comes in very handy if in your Stored procedure you need to use the
IN keyword to check if something is in a provided value. With this function you can have comma delimited values passed to your stored procedure.
sql
CREATE FUNCTION udfParseDelimitedString
(
@source VARCHAR(8000)
,@delimiter VARCHAR(100) = ',' --default to comma delimited.
)
RETURNS @parsed TABLE (pos INT identity(1,1),i_value INT ,n_value NUMERIC(18,3),t_value VARCHAR(2000))
AS
BEGIN
DECLARE @continue INT
,@start INT
,@length INT
,@pos INT
,@int INT
,@num NUMERIC(18,3)
,@txt VARCHAR(2000)
,@del_length tinyINT
IF LEN(@source) = 0
BEGIN
SET @continue = 0 -- force early exit
END
ELSE
BEGIN
-- parse the original @source array INTo a temp table
SET @continue = 1
SET @start = 1
SET @source = RTRIM( LTRIM( @source))
SET @length = DATALENGTH( RTRIM( LTRIM( @source)))
SET @del_length = LEN(@delimiter)
END
WHILE @continue = 1
BEGIN
SET @pos = CHARINDEX( @delimiter ,(SUBSTRING( @source, @start ,((@length - @start) + @del_length))))
IF @pos > 0 -- delimeter(s) found, get the value
BEGIN
SET @txt = LTRIM(RTRIM( SUBSTRING( @source, @start ,(@pos - 1)) ))
IF ISNUMERIC(@txt) = 1
BEGIN
set @int = CAST( CAST(@txt AS NUMERIC) AS INT)
set @num = CAST( @txt AS NUMERIC(18,3))
END
ELSE
BEGIN
set @int = NULL
set @num = NULL
END
SET @start = @pos + @start + (@del_length- 1)
END
ELSE -- No more delimeters, get last value
BEGIN
SET @txt = LTRIM(RTRIM( SUBSTRING( @source, @start ,((@length - @start) + @del_length)) ))
IF ISNUMERIC(@txt) = 1
BEGIN
set @int = CAST( CAST(@txt AS NUMERIC) AS INT)
set @num = CAST( @txt AS NUMERIC(18,3))
END
ELSE
BEGIN
set @int = NULL
set @num = NULL
END
SELECT @continue = 0
END
INSERT INTO @parsed VALUES( @int, @num, @txt )
END
RETURN
END
With this UDF you pass it a delimited string and what it is delimited with and it will return a table with the split values in it. With a
Table Value you can use this in your stored procedure like you would a normal table, like this\
sql
SELECT
COlumn1,
Column2,
Column3
FROM
Table1
WHERE
ID IN (SELECT i_value FROM db.udfParseDelimitedString('1,23,104,99'))
This table values function will actually return the integer value of the items in the string, the number value and the text value, all the values you would need. Unfortunately at this time I do not have a
Multiple-Statement Table Valued UDF to show, but will be updating this with one at some point.
The last UDF we will look at is one I find completely invaluable. After you've done SQL development as I have in time you get tired of having to write
RTRIM(LTRIM(YourValue)), so I decided to create a
Scalar UDF for this. With this UDF you pass it a string and it will remove trailing and leading spaces for you
sql
CREATE FUNCTION dbo.udfTrimString (@value VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
--variable to hold the final result
DECLARE @Trimmed_Value VARCHAR(8000)
--user LTRIM & RTRIM to set the vaoue of our final result
SET @Trimmed_Value = RTRIM(LTRIM(@value))
--return the trimmed string
RETURN @Trimmed_Value
END
GO
As mentioned, this UDF performs your
RTRIM() and your
LTRIM() and returns the trimmed value.
So, as you can see User Defined Functions can and will be an invaluable asset to your code repository. User Defined Functions allow as close to possible Object Orientated Programming in both SQL 2000 and SQL 2005. I know some are going to argue this, but that is OK with me, you can make your own determination. Bottom line is UDF's will make your life as a SQL developer much easier, and allow you to write scalable and maintainable code.
In Part II of this series on Microsoft SQL Server I will cover User Defined Data Types, what they are, how they work, how to create them and how they, like User Defined Functions, can make your job much easier.
User Defined Functions in MSSQL, The way to a developers heart
22 May, 2008 - 07:04 AM
| 