562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Scalar User Defined Functions in SQL Server - UDFs
Part three 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.
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!
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:
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.
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.
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.
|Parts of this blog|
25 Aytoun Street