This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Scalar User Defined Functions in SQL Server - UDFs Part four 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. Scalar User-Defined Functions in SQL Server Writing Custom Functions in SQL Server Creating and Using a Custom Function Altering and Deleting Custom Functions (this blog) Writing Complex User-Defined Functions This blog is part of our long and detailed online SQL tutorial. Another way to learn SQL is on one of our SQL training courses.

Scalar User Defined Functions in SQL Server - UDFs

Part four 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.

Scalar User-Defined Functions in SQL Server
Writing Custom Functions in SQL Server
Creating and Using a Custom Function
Altering and Deleting Custom Functions (this blog)
Writing Complex User-Defined Functions

This blog is part of our long and detailed online SQL tutorial. Another way to learn SQL is on one of our SQL training courses.

Posted by Andrew Gould on 01 February 2013

Altering and Deleting Custom Functions

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.

Choosing to Modify a Function

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.

Writing Code to Alter a Function

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)

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

Executing the Code to Alter a Function

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.

Using a Modified Function

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.

Deleting a Custom Function

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.

What's Next?

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.

Parts of this blog
Scalar User-Defined Functions in SQL Server Writing Custom Functions in SQL Server Creating and Using a Custom Function Altering and Deleting Custom Functions (this blog) Writing Complex User-Defined Functions

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR