This page has 0 threads | Add post

Creating and Using a Custom Function

Written by Andrew Gould

In this tutorial

Executing Your Code to Create a Function
Finding the Function You Have Created
Using a Custom Function in a Query
Ensuring Your Function is Recognised
What's Next?

So we're at the point where we've written the code to create a user-defined function. What we need to do now is execute the code and then use the function in a query to make sure that it works!

Executing Your Code to Create a Function

You execute the code to create a function in the same ways as for a simple query: either click the Execute button at the top of the screen; or press F5 on the keyboard. Before you do this, it's worth checking that you've selected the correct database from the drop list next to the Execute button:

Here we've made sure to choose the Movies database before clicking Execute.

To avoid having to remember to select the correct database you could add a USE statement to your code, as shown in the example below.

USE Movies

--The GO command begins a new batch

--CREATE FUNCTION must be the first statement in a batch

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

Assuming that everything works, you should see this reassuring message at the bottom of the screen:

Success!

Finding the Function You Have Created

Once your function has been created, you should be able to find it in the Scalar-valued Functions folder of your database. You may need to refresh this folder first in order for your function to appear.

Right-click on the folder and choose Refresh in order to see its most recent additions.

When you expand the folder, you should be able to see your function listed within. You can even expand its Parameters folder to see the list of input parameters that you have defined.

Your custom function is now part of your database.

Using a Custom Function in a Query

You can use a custom function in a query in almost exactly the same way as a built-in function. The one difference is that you must precede your function name with the name of the schema to which it belongs. In the screenshot shown above, the function is listed as dbo.fnLongDate and this is how it must be called when we use it in a query, as shown in the example below:

SELECT

FilmName

,dbo.fnLongDate(FilmReleaseDate)

FROM

tblFilm

The result of this query is the same as if we had written out the full expression used to format each date:

The beauty of a function is that you can reuse it in any query in the database, rather than having to write out the full expression each time.

Ensuring Your Function is Recognised

The first time you attempt to use a custom function in a query you may find that its name is underlined in red. This indicates that your function hasn't been recognised by the IntelliSense feature of SQL Server Management Studio.

If you hover the mouse cursor over the function name you'll be told that the function isn't recognised.

This isn't actually a problem (if you execute the query the function works perfectly), but it is a little disconcerting. To clear the red underlining and ensure that your function name is recognised you need to refresh the IntelliSense cache. To do this, you can choose Edit > IntelliSense > Refresh Local Cache from the menu, or you can press CTRL + SHIFT + R on the keyboard.

Choose this option from the Edit menu.

What's Next?

Now that you've seen how to successfully create and use a function, what if you want to change the way that it works? The next part of this series explains how you can modify an existing function, including how to delete it entirely if you want to.

Previous
tutorial

Stored procedures

Previous
part

Writing Custom Functions in SQL Server

Top of page

Next
part

Altering and Deleting Custom Functions

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

Creating and Using a Custom Function

Executing Your Code to Create a Function

Finding the Function You Have Created

Using a Custom Function in a Query

Ensuring Your Function is Recognised

What's Next?

Head office

London

Manchester