Home > Automation, Querying, SQL Server 2008R2, SQL Server 2012 > Functions – A handy tool for formatting data (among other things)

Functions – A handy tool for formatting data (among other things)

Last week we started Spatial with Part 1 of a series, and we’ll get back to that on Thursday.  Today, we’ll talk about functions.  Functions are a tool that can be used by developers to perform automated data replacement, concatenation, calculations or other tasks in a much easier way:  by programming a function, then simply calling that function for the field that needs to be calculated.   There are two types, system functions and User-Defined Functions (UDF’s).  This post is going to deal with UDF’s.

According to the MSDN article on Functions, a “user-defined function is a Transact-SQL or common language runtime (CLR) routine that accepts parameters, performs an action, such as a complex calculation, and returns the result of that action as a value.”

Lets start with an example.  Lets say your query to return a mailing list looks like this:

SELECT UserId, Firstname + ' ' + LastName AS 'Name', 
       Address + ', ' + City + ', ' + State + ' ' + PostalCode AS 'Address'
FROM UserData

Wouldn’t it be easier if you could automate the concatenation of those fields?  Well, I’ve got some good news for you.  A person can create a function to do this for them.  Lets take a peek at the basic code of how to create a function:

CREATE FUNCTION fn_ConcatAddress (@UserId int)
RETURNS nvarchar
    AS
    RETURN
    BEGIN 
        SELECT Address + ', ' + City + ', ' + State + ' ' + PostalCode
        FROM UserData
        WHERE UserId = @UserId
    END

This function will automatically concatenate the Address field for the UserId that is passed to it.  So, then one could write the following query and return the same results as the first one:

SELECT UserId, Firstname + ' ' + LastName AS 'Name',
       fn_ConcatAddress(UserId) AS 'Address'
FROM UserData

Isn’t that a handy little trick?  Think of the things you can do with that.  All that happens is your passing variables to a function that takes those variables, runs them through the SQL you have written below the Return block, and returns the value of the statement.

I apologize for the short article, as I’m pressed for time today, but I’d like to hear from you!  What are some of the fancy tricks you’ve been able to do with functions?

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: