Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
546 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers 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
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andrew Gould
In this tutorial
This part of the series explains what to do if you've created a function and decided that you need to change the way that it works.
The easiest way to alter a function that you've created is to right-click on its name in the Object Explorer and choose to modify it.
Choose this option to start altering a function.
When you do this you'll be presented with a page of system-generated code that will look very much like the original code you wrote to create your function. The most important difference is that rather than the word CREATE, the word ALTER is used.
The line starting with ALTER FUNCTION is the important one. You can happily delete the four system-generated lines above it.
If you're curious about the extra instructions in the code shown above here is some further reading on QUOTED_IDENTIFIER and ANSI_NULLS.
You can now modify the code to change the way your function works. For our date format example we could add a section which puts a suffix at the end of the number of the day, like so:
ALTER FUNCTION [dbo].[fnLongDate]
(
@MyDate AS DATETIME
)
RETURNS VARCHAR(MAX)
AS
BEGIN
RETURN DATENAME(DW,@MyDate) + ' ' +
DATENAME(D,@MyDate) +
CASE
WHEN DAY(@MyDate) IN (1, 21, 31) THEN 'st'
WHEN DAY(@MyDate) IN (2, 22) THEN 'nd'
WHEN DAY(@MyDate) IN (3, 23) THEN 'rd'
ELSE 'th'
END + ' ' +
DATENAME(M,@MyDate) + ' ' +
DATENAME(YY,@MyDate)
END
Finally, in order to make sure the function is actually modified, you need to execute the code that you have written.
This is one of those easy things to forget - on several occasions I've written the code to modify a function, then gone away to test it and wondered why my modifications didn't work. I eventually realised that I hadn't executed the code to perform the modification!
You can execute this code in the same way as any other code: either click the Execute tool at the top of the screen; or press F5 on the keyboard.
To test if your modifications have worked you can simply execute a query which uses it.
Now our function adds the appropriate suffix to the day of the month.
The most drastic modification you can make to a custom function is to delete it entirely! You can do this using the Object Explorer, as shown in the diagram below:
Right-click on the name of your function and choose Delete.
You can also write code to delete your function. This is much easier than the code you need to write in order to create it!
DROP FUNCTION fnLongDate
When you execute this code your function will be removed from the database, although you may have to refresh the Scalar-valued Functions folder in order to prove that this has happened.
The next part of this blog series will show you how to create longer, more complex functions using programming techniques such as using variables and writing conditional statements.
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.