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
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.
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.
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\
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
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.
MultiQuote
| 