Printable Version of Topic

Posted by: PsychoCoder 22 May, 2008 - 07:04 AM

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:

1. Scalar
2. Inline Table valued
3. 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.