SQL: User Defined Functions - Scalar

The previous article on using CROSS APPLY discussed that it's use might not be something that you wouldn't use often. So a little bit of digging and one use where it has to be use is with a user defined functions. I decided to do a little bit of digging on this.

When I first started with SQL you couldn't created your own functions, but functionality was added with SQL Server 2000. That's a release I skipped and I first used them in SQL Server 2005. One type I didn't use was table functions. Before showing the options here, I will show the use of scalar user defined functions.

Again, using the Northwind database looking at the Employees table

SELECT 
   EmployeeID, LastName, City, Region, Postalcode, Country 
FROM Employees

Giving us

EmployeeID  LastName             City            Region          Postalcode Country
----------- -------------------- --------------- --------------- ---------- ---------------
1           Davolio              Seattle         WA              98122      USA
2           Fuller               Tacoma          WA              98401      USA
3           Leverling            Kirkland        WA              98033      USA
4           Peacock              Redmond         WA              98052      USA
5           Buchanan             London          NULL            SW1 8JR    UK
6           Suyama               London          NULL            EC2 7JR    UK
7           King                 London          NULL            RG1 9SP    UK
8           Callahan             Seattle         WA              98105      USA
9           Dodsworth            London          NULL            WG2 7LT    UK

Now Region can be null (for employees in the United Kingdom). Let's imagine the task that I want to solve is to return the location (city, region, postalcode and country) as one string. So the SQL rules are that if you to concatenate a null the result is a null, thus

SELECT 
    EmployeeID, LastName, City + ', ' + Region + ' ' + Postalcode + ' ' + Country 
FROM 
    Employees

Now gives us NULLS for all the addresses in the UK

EmployeeID  LastName             
----------- -------------------- -----------------------------------------------------------
1           Davolio              Seattle, WA 98122 USA
2           Fuller               Tacoma, WA 98401 USA
3           Leverling            Kirkland, WA 98033 USA
4           Peacock              Redmond, WA 98052 USA
5           Buchanan             NULL
6           Suyama               NULL
7           King                 NULL
8           Callahan             Seattle, WA 98105 USA
9           Dodsworth            NULL

The solution is to put a CASE statement in, thus this SQL

SELECT 
  EmployeeID, LastName,
  CASE
    WHEN Region IS NULL THEN City + ' ' + PostalCode
    ELSE City + ', ' + Region + ' ' + PostalCode
  END AS Location
FROM Employees

Will give us some nicely formatted text

EmployeeID  LastName             Location
----------- -------------------- -------------------------------------------
1           Davolio              Seattle, WA 98122
2           Fuller               Tacoma, WA 98401
3           Leverling            Kirkland, WA 98033
4           Peacock              Redmond, WA 98052
5           Buchanan             London SW1 8JR
6           Suyama               London EC2 7JR
7           King                 London RG1 9SP
8           Callahan             Seattle, WA 98105
9           Dodsworth            London WG2 7LT

But this might look a little messy, especially if this is something we do a lot. So we can create a user defined function

CREATE FUNCTION Location 
     (@City nvarchar(30), @Region nvarchar(30), @PostalCode nvarchar(20))
RETURNS nvarchar(80)
AS
BEGIN
 DECLARE @ret varchar(30)
 SELECT @ret=
   CASE
  WHEN @Region IS NULL THEN @City + ' ' + @PostalCode
  ELSE @City + ', ' + @Region + ' ' + @PostalCode
   END 

RETURN @ret
END
GO

So this SQL will return it

SELECT dbo.Location('Wallingford', 'Oxon', 'OX10')

Gives us

Wallingford, Oxon OX10

A function we can use in our SQL. Taking our Employees table we can now do this

SELECT 
  EmployeeID, LastName,
  dbo.Location(City, Region, PostalCode) AS Location
FROM Employees

Which gives us the expected result

EmployeeID  LastName             Location
----------- -------------------- ----------------------------------------------------------
1           Davolio              Seattle, WA 98122
2           Fuller               Tacoma, WA 98401
3           Leverling            Kirkland, WA 98033
4           Peacock              Redmond, WA 98052
5           Buchanan             London SW1 8JR
6           Suyama               London EC2 7JR
7           King                 London RG1 9SP
8           Callahan             Seattle, WA 98105
9           Dodsworth            London WG2 7LT