Scalar User Defined Functions in SQL Server - UDFs
Part two of a five-part series of blogs

If you have a calculation that you frequently use in SQL queries you're probably bored of writing out the same code time after time. Why not try creating a user-defined function to save you the hassle? This blog teaches you how to define your own custom functions, including how to use input parameters, how to alter a function after you've created it, and even how to use fancy programming techniques such as variables and IF statements to help structure complex sequences of calculations.

  1. Scalar User-Defined Functions in SQL Server
  2. Writing Custom Functions in SQL Server (this blog)
  3. Creating and Using a Custom Function
  4. Altering and Deleting Custom Functions
  5. Writing Complex User-Defined Functions

This blog is part of our long and detailed online SQL tutorial.  Another way to learn SQL is on one of our SQL training courses.

Posted by Andrew Gould on 01 February 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Writing Custom Functions in SQL Server

There are two main ways to create a user-defined function in SQL Server Management Studio: you can write one from scratch; or you can use a template.

Creating a Function Using a Template

If you want to use a template to create a function you first need to find the folder in which your function will be stored.  The diagram below shows where this folder is for a database called Movies:

The Scalar-valued functions folder

Every database will have a Scalar-valued Functions folder. To add a new function to it, right-click the folder and select the option shown.

 

A scalar-valued function is simply one which returns a single value each time it is called.

Once you've clicked the New Scalar-valued Function... option shown in the diagram above, you'll be presented with a wonderfully intuitive (ahem) page of system-generated code.

System-generated code

This is just a small portion of the full template that is generated.

In theory it's possible to modify the code on this page to define your own function, but I'll admit that I've not once done this!  Instead, I prefer to write my functions from scratch, as described in the next section.

Writing a Function from Scratch

To start writing a function from scratch you'll need a new query window.  You can create one by pressing CTRL + N on the keyboard, or by clicking the button shown below:

New query

Look for the New Query button in the top left of the screen.

 

Giving Your Function a Name

The first line of code you need to write tells SQL Server that you're creating a new function and what its name will be:

CREATE FUNCTION fnLongDate

Beginning your function names with the letters fn is a useful way to distinguish your custom functions from the built-in functions in SQL Server.

Listing the Input Parameters

Once you've given your function a name you can then list the input parameters, or arguments, that your function will have.  It's up to you whether you do this on one line or across several, but you must make sure to enclose the parameter list in a set of parentheses and separate individual parameters with a comma.

CREATE FUNCTION fnLongDate

(

@MyDate AS DATETIME

)

All parameter names must begin with an @ symbol.  Our example function has just a single input; the date that we want to format.  The data type of this parameter is DATETIME.

Specifying the Return Type of the Function

After listing the parameters you can then say what kind of data your function will return.  You do this using the RETURNS keyword:

CREATE FUNCTION fnLongDate

(

@MyDate AS DATETIME

)

RETURNS VARCHAR(MAX)

Our example function will return a string of text and, as we don't know how long the text will be, we've used VARCHAR(MAX) as the return type.

The final step in setting up the structure of a custom function is to add the AS keyword and a BEGIN and END block:

CREATE FUNCTION fnLongDate

(

@MyDate AS DATETIME

)

RETURNS VARCHAR(MAX)

AS

BEGIN

--Our function code goes here!

END

Now we're ready to start writing the logic of the function within the BEGIN and END block.

Telling the Function What to Calculate

For our simple function we want to replicate the logic of the expression we saw in the query in part 1 of this blog.  Here's a reminder of what that looks like:

SELECT

FilmName

,DATENAME(DW,FilmReleaseDate) + ' ' +

DATENAME(D,FilmReleaseDate) + ' ' +

DATENAME(M,FilmReleaseDate) + ' ' +

DATENAME(YY,FilmReleaseDate)

FROM

tblFilm

Of course, we only need the four lines which use the DATENAME function, and we'll need to replace the reference to the FilmReleaseDate field with the name of the input parameter for our function:

CREATE FUNCTION fnLongDate

(

@MyDate AS DATETIME

)

RETURNS VARCHAR(MAX)

AS

BEGIN

--Our function code goes here!

DATENAME(DW,@MyDate) + ' ' +

DATENAME(D,@MyDate) + ' ' +

DATENAME(M,@MyDate) + ' ' +

DATENAME(YY,@MyDate)

END

Telling the Function to Return an Answer

The final step in defining our function is to say what answer the function should return.  This instruction must be included in every custom function you create and it must be the last thing that the function does.  Because our function is so simple, it effectively only has a single line, so we need to add the word RETURN to the start of it:

CREATE FUNCTION fnLongDate

(

@MyDate AS DATETIME

)

RETURNS VARCHAR(MAX)

AS

BEGIN

--Our function code goes here!

RETURN DATENAME(DW,@MyDate) + ' ' +

DATENAME(D,@MyDate) + ' ' +

DATENAME(M,@MyDate) + ' ' +

DATENAME(YY,@MyDate)

END

And that's it!  All we have to do now is execute the code to create the function and then use it in a query.  All of that is explained in the next part of this blog series.

This blog has 0 threads Add post