This page has 0 threads | Add post

Writing Custom Functions in SQL Server

Written by Andrew Gould

In this tutorial

Creating a Function Using a Template
Writing a Function from Scratch
Giving Your Function a Name
Listing the Input Parameters
Specifying the Return Type of the Function
Telling the Function What to Calculate
Telling the Function to Return an Answer

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:

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.

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:

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)

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)

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)

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.

Previous
tutorial

Stored procedures

Previous
part

Scalar User-Defined Functions in SQL Server

Top of page

Next
part

Creating and Using a Custom Function

Next
tutorial

Entering and changing data

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

User-defined functions

Writing Custom Functions in SQL Server

Creating a Function Using a Template

Writing a Function from Scratch

Giving Your Function a Name

Listing the Input Parameters

Specifying the Return Type of the Function

Telling the Function What to Calculate

Telling the Function to Return an Answer

Head office

London

Manchester