BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
- Scalar User-Defined Functions in SQL Server
- Writing Custom Functions in SQL Server
- Creating and Using a Custom Function (this blog)
- Altering and Deleting Custom Functions
- Writing Complex User-Defined Functions
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.
Creating and Using a Custom Function
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.
--The GO command begins a new batch
--CREATE FUNCTION must be the first statement in a batch
CREATE FUNCTION fnLongDate
@MyDate AS DATETIME
--Our function code goes here!
RETURN DATENAME(DW,@MyDate) + ' ' +
DATENAME(D,@MyDate) + ' ' +
DATENAME(M,@MyDate) + ' ' +
Assuming that everything works, you should see this reassuring message at the bottom of the screen:
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:
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.
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.