Video: How to Test SQL Server Functions - SQL Training Online
In this video, I show you a trick on how to test SQL Functions in SQL Server 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.
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 9th 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: /
Twitter: http://www.twitter.com/sql_by_joey
Google+:
LinkedIn: http://www.linkedin.com/in/joeyblue