Archives for 2012

How do you test a SQL Server Function?

Testing a new function is easier to do when you don’t actually try to query a table to do it.

I will show you how to trim off the front part of an email address by first testing the functions, then putting them into the actual SQL query.

Let’s start with the following SQL query that is written against the Adventure Works database.

SELECT
s.[BusinessEntityID]
,s.[Name]
,ct.[Name] AS [ContactType]
,p.[Title]
,p.[FirstName]
,p.[MiddleName]
,p.[LastName]
,p.[Suffix]
,pp.[PhoneNumber]
,pnt.[Name] AS [PhoneNumberType]
,ea.[EmailAddress]
,p.[EmailPromotion]
FROM [Sales].[Store] s
INNER JOIN [Person].[BusinessEntityContact] bec
ON bec.[BusinessEntityID] = s.[BusinessEntityID]
INNER JOIN [Person].[ContactType] ct
ON ct.[ContactTypeID] = bec.[ContactTypeID]
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = bec.[PersonID]
LEFT OUTER JOIN [Person].[EmailAddress] ea
ON ea.[BusinessEntityID] = p.[BusinessEntityID]
LEFT OUTER JOIN [Person].[PersonPhone] pp
ON pp.[BusinessEntityID] = p.[BusinessEntityID]
LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID];

This query joins multiple tables and can be intimidating to the beginner. The main thing to look at is that it returns an email address.

I want to trim off the front part of the email address.

So, I will copy an email address to work on as an example.

‘[email protected]’

Now, SQL Server allows you to run a Select query without a FROM clause.

We will use that capability to test our function. Here is an example.

select '[email protected]'

Now, I want to find the ‘@’ character in the string, so I will use the CharIndex function. Now it is time to test the SQL Server Function:

select charindex('@','[email protected]')

This returns the number 9, since the ‘@’ sign is the 9^th character.

Now, we need to use the substring to pull off all of the character until the ‘@’ sign. So, let’s test the substring function:

select charindex('@','[email protected]')
,substring('[email protected]',1,9-1)

This does what we want, so now we need to test both SQL Server Functions together.

select substring('[email protected]',1,charindex('@','[email protected]')-1)

This is exactly what we want. So, we just need to put the function into the main query.

SELECT
s.[BusinessEntityID]
,s.[Name]
,ct.[Name] AS [ContactType]
,p.[Title]
,p.[FirstName]
,p.[MiddleName]
,p.[LastName]
,p.[Suffix]
,pp.[PhoneNumber]
,pnt.[Name] AS [PhoneNumberType]
,ea.[EmailAddress]
,substring(ea.[EmailAddress] ,1,charindex('@',ea.[EmailAddress] )-1)
,p.[EmailPromotion]
FROM [Sales].[Store] s
INNER JOIN [Person].[BusinessEntityContact] bec
ON bec.[BusinessEntityID] = s.[BusinessEntityID]
INNER JOIN [Person].[ContactType] ct
ON ct.[ContactTypeID] = bec.[ContactTypeID]
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = bec.[PersonID]
LEFT OUTER JOIN [Person].[EmailAddress] ea
ON ea.[BusinessEntityID] = p.[BusinessEntityID]
LEFT OUTER JOIN [Person].[PersonPhone] pp
ON pp.[BusinessEntityID] = p.[BusinessEntityID]
LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID];

There it is. That is how you Test SQL Server Functions.

Let me know what you think by commenting or sharing on twitter, facebook, google+, etc.

If you enjoy the video, please give it a like, comment, or subscribe to my channel.

You can visit me at any of the following:

SQL Training Online: https://www.sqltrainingonline.com

Twitter: http://www.twitter.com/sql_by_joey

Google+: https://plus.google.com/#100925239624117719658/posts

LinkedIn: http://www.linkedin.com/in/joeyblue

Facebook: http://www.facebook.com/sqltrainingonline