Database Reference
In-Depth Information
Listing 6-28.
Creating a User-Defined Function
Create Function
fEtlTransformStateToLongName
( @StateAbbreviation nChar(2) )
Returns nVarchar(50)
As
Begin
Return
( Select Case @StateAbbreviation
When 'CA' Then 'California'
When 'OR' Then 'Oregon'
When 'WA' Then 'Washington'
End )
End
The code in Listing 6-28 creates a UDF that returns the full name of the state whenever a two-letter
abbreviation for the state is given. For this example, we have added only three states, but it goes without saying
that you would normally include all the states, regions, or territories that your data warehouse requires. You can
test your code to verify that it works by executing select statements similar to the three test queries in Figure
6-16
.
Figure 6-16.
Working with user-defined functions
