FUNCTION in SQL Server

What is a function in SQL Server? How to use SQL Server functions? Let's find out with Quantrimang.com!

SQL Server is one of the programming knowledge that you must know if you want to manage databases well when developing products or projects. In particular, the SQL Server function is very important.

Functions in SQL Server are database objects that contain a group of SQL commands to perform a specific task. A function accepts input parameters, executes the action, then returns the result.

You need to remember that the function always returns a single value or a table. The main purpose of this function is to reproduce common tasks easily. You can build functions once and use them in multiple locations based on your needs. SQL Server does not allow the use of functions to insert, delete, or update records in a database table.

The following are the rules for creating functions in SQL Server:

• A function must have a name. The name cannot begin with a special character such as @, $, # or other similar character.
• The SELECT command is the only command that works with functions.
• You can use a function in any position such as AVG, COUNT, SUM, MIN, DATE and other functions using SELECT query in SQL.
• Whenever a function is called, it is compiled.
• The function must return a value or result.
• The function only uses input parameters.
• You cannot use TRY and CATCH commands in functions.

This article will provide you with syntax and examples on how to create and delete functions in SQL Server.

CREATE FUNCTION (Create Function)

Syntax

To create a function in SQL Server, we use the syntax below:

CREATE FUNCTION [schema_name.]function_name
( [ @parameter [ AS ] [type_schema_name.] datatype
[ = default ] [ READONLY ]
, @parameter [ AS ] [type_schema_name.] datatype
[ = default ] [ READONLY ] ]
)

RETURNS return_datatype

[ WITH { ENCRYPTION
| SCHEMABINDING
| RETURNS NULL ON NULL INPUT
| CALLED ON NULL INPUT
| EXECUTE AS Clause ]

[ AS ]

BEGIN

[declaration_section]

executable_section

RETURN return_value

END;

Parameter:

• schema_name: The schema name owns the function.
• function_name: The name assigned to the function.
• @parameters: One or more parameters are passed to the function.
• type_schema_name: Schema data type (if any).
• Datatype: Data type for @parameter.
• Default: Default value assigned to @parameter.
• READONLY: @parameter cannot be overridden by functions.
• return_datatype: The data type of the return value.
• ENCRYPTION: The function's source code will not be stored as text in the system.
• SCHEMABINDING: Make sure objects are not edited to affect the function.
• RETURNS NULL ON NULL INPUT: The function will return NULL if any parameter is NULL.
• CALL ON NULL INPUT: The function will execute even if a NULL parameter is included.
• EXECUTE AS clause: Defines the security context for function execution.
• return_value: Value is returned.

For example

CREATE FUNCTION fuNhanvien
( @nhanvien_id INT )

RETURNS VARCHAR(50)

AS

BEGIN

DECLARE @nhanvien_name VARCHAR(50);

IF @nhanvien_id 

The above function is assigned the name fuNhanvien, has one parameter @nhanvien_id with INT data type. The function will return a value of type VARCHAR(50) when executing the RETURNS statement.

Then you can make the reference fuNhanvien as follows:

USE [test]
GO

SELECT dbo.fuNhanvien(8);

GO

Drop Function (Delete Function)

Once you have successfully created functions, there will also be cases where you want to remove the function from the database for some reasons.

Syntax

To delete a function, we have the following syntax:

DROP FUNCTION function_name;

Parameter:

function_name: The name of the function you want to delete..

For example

DROP FUNCTION fuNhanvien;

Executing this command means you have just deleted the function fuNhanvien from the database.

SQL Server has many built-in functions including strings, numbers, dates, conversions and some advanced functions in SQL Server. Some prominent string functions in SQL Server:

Functions in SQL Server

Advanced functions in SQL Server

Previous article: SEQUENCE in SQL Server

Next article: PROCEDURE (Procedure) in SQL Server