Create Function SQL | How to Create Function in SQL?

In this article create function SQL describes a way to Create a UDF function (user-defined function) in SQL by using Transact-SQL, A user-defined function could be Transact-SQL or common language runtime which accepts the parameters, and these perform an action, like as advanced complex calculations, which return the result of the action as a value. therefore, the return value can be a scalar or table. to create a reusable routine that can be utilized in these ways in which.

Note:
Transact-structured query language (SQL) statements as SELECT
In the application for calling the function
the definition of other user-define function
For describe a view and improve the functionality of an indexed view
To define or describe a column in the table
Define or describe the CHECK constraint on a column

Replace a stored or hold on the procedure
Used an inline function as a filter predicate for a security policy

Syntax:

CREATE [ OR REPLACE ] FUNCTION
… [[database.]schema.]function( [ argname argtype[,…] ] )
… RETURN return‑type
… AS
… BEGIN
…… RETURN expression;
… END;

Parameters Description

OR REPLACE	it’s specified to replace an existing function with a new definition, therefore, if you change only an argument name or argument type or if you modify the body of a SQL function, the system maintains both versions under the same function name.
[database.]schema	specifies a schema, by default public. if the schema is any schema other than the public, you must supply the schema name.
function	the Specified a name for SQL (structured query language) function to create, where the function conforms to the conventions delineate or described in Identifiers. therefore, when we using more than one schema, then specify the schema which contains a function.
argname	Specifies the name of the argument.
argtype	it’s the specified data type for an argument which will be passed to the function and therefore the argument types must be matched with the type names
return‑type	Specifies the data type to be returned or come back by the function.
RETURN expression	A semicolon at the end or tip of the expression is required (needed). Specifies the SQL (structured query language)function body (function), wherever expression will contain built-in functions, operators, and argument names, specified in the CREATE FUNCTION statement.

Important Note:
The CREATE FUNCTION definition permits or allows just one RETURN expression. FROM, GROUP BY, ORDER BY, WHERE, LIMIT, analytics, aggregation, and meta-function aren’t allowed.

Privileges (Non-superuser:)

CREATE privilege on the function’s schema
USAGE privilege on the function’s library

Create Function SQL | Limitations and Restrictions

The user-defined functions can’t be used for the performance actions because it modifies the database state.
The user-defined functions can’t contain an OUTPUT INTO clause that encompasses a table as its target.
The FOR XML the clause isn’t allowed.
The user-defined functions will not call a stored procedure however can call an extended stored procedure.
The user-defined functions cannot create the use of temp tables or dynamic SQL. Table variables are allowed.
The user-defined functions can not return multiple result sets(can’t come to multiple results). Then use a stored procedure if you want to return multiple result sets.
Error handling is restricted in a user-defined function. A UDF doesn’t support attempt or TRY...CATCH, RAISERROR or @ERROR
therefore, SET statements aren’t allowed in a user-defined function.
There are the following Service Broker statements, which cannot be included in the definition of a Transact-SQL user-defined function:

SEND
RECEIVE
BEGIN DIALOG CONVERSATION
MOVE CONVERSATION
END CONVERSATION
GET CONVERSATION GROUP

Create Function SQL | Procedure

you will define the CREATE FUNCTION (scalar) statement:
after that, you will specify a name for the function.
Specify the name and data type for each or every input parameter.
you will need to specify the RETURNS keyword and the data type of the scalar return (come back) value.
Specify the BEGIN keyword to introduce the function-body. Notice: The utilization of the BEGIN ATOMIC keyword isn’t recommended or counseled for new functions.
Specify the function body and also specify the RETURN clause and a scalar return variable or value.
Specify the END keyword after that you will need to execute the CREATE FUNCTION (scalar) statement from a supported interface.

Results

Therefore, after execution, you will get the result that the CREATE FUNCTION (scalar) statement should execute with success and scalar function should be created.

Program Example

1. There is the following example of a compiled SQL function:

CREATE FUNCTION GetPrice (Retailer CHAR(20), Pid INT)         
    RETURNS  DECIMAL(10,3) 
    LANGUAGE SQL  
    MODIFIES SQL
    BEGIN 
      DECLARE price DECIMAL(10,3); 

      IF Retailer = 'Retailer 1' 
        THEN SET price = (SELECT ProdPrice FROM R1Table WHERE Id = Pid); 
      ELSE IF Retailer = 'Retailer 2' 
        THEN SET price = (SELECT Price 
                          FROM R2Table 
    		 		 	  WHERE Pid = GetPrice.Pid); 
      END IF; 
  
    RETURN price; 
  END

CREATE FUNCTION GetPrice (Retailer CHAR(20), Pid INT)

RETURNS DECIMAL(10,3)

LANGUAGE SQL

MODIFIES SQL

BEGIN

DECLARE price DECIMAL(10,3);

IF Retailer = 'Retailer 1'

THEN SET price = (SELECT ProdPrice FROM R1Table WHERE Id = Pid);

ELSE IF Retailer = 'Retailer 2'

THEN SET price = (SELECT Price

FROM R2Table

WHERE Pid = GetPrice.Pid);

END IF;

RETURN price;

END

Therefore, this function takes in two input parameters and they return a single scalar value, conditionally based on the input parameter values. furthermore, It requires the declaration and also uses a local variable named price to hold the value to be returned until the function returns.

2: There is the following example which demonstrates the compiled SQL function definition containing a cursor, REPEAT statement, and a condition handler statement:

CREATE FUNCTION exit_func(a INTEGER)
    SPECIFIC exit_func
    LANGUAGE SQL
    RETURNS INTEGER
    BEGIN 
      DECLARE val INTEGER DEFAULT 0;

      DECLARE myint INTEGER DEFAULT 0;

      DECLARE cur2 CURSOR FOR
        SELECT c2 FROM udfd1 
          WHERE c1 <= a 
          ORDER BY c1;

      DECLARE EXIT HANDLER FOR NOT FOUND
        BEGIN
          SIGNAL SQLSTATE '70001' 
          SET MESSAGE_TEXT = 
            'Exit handler for not found fired';
        END;

    OPEN cur2;

    REPEAT
      FETCH cur2 INTO val;
      SET myint = myint + val;
    UNTIL (myint >= a) 
    END REPEAT;

    CLOSE cur2;

    RETURN myint;

  END@

CREATE FUNCTION exit_func(a INTEGER)

SPECIFIC exit_func

LANGUAGE SQL

RETURNS INTEGER

BEGIN

DECLARE val INTEGER DEFAULT 0;

DECLARE myint INTEGER DEFAULT 0;

DECLARE cur2 CURSOR FOR

SELECT c2 FROM udfd1

WHERE c1 <= a

ORDER BY c1;

DECLARE EXIT HANDLER FOR NOT FOUND

BEGIN

SIGNAL SQLSTATE '70001'

SET MESSAGE_TEXT =

'Exit handler for not found fired';

END;

OPEN cur2;

REPEAT

FETCH cur2 INTO val;

SET myint = myint + val;

UNTIL (myint >= a)

END REPEAT;

CLOSE cur2;

RETURN myint;

END@

Create Function SQL | Syntax

CREATE [OR REPLACE]
    [DEFINER = {user | CURRENT_USER | role | CURRENT_ROLE }]
    [AGGREGATE] FUNCTION [IF NOT EXISTS] func_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...]
    RETURN func_body

func_parameter:
    param_name type

type:
    Any valid data type

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

func_body:
    Valid SQL procedure statement

CREATE [OR REPLACE]

[DEFINER = {user | CURRENT_USER | role | CURRENT_ROLE }]

[AGGREGATE] FUNCTION [IF NOT EXISTS] func_name ([func_parameter[,...]])

RETURNS type

[characteristic ...]

RETURN func_body

func_parameter:

param_name type

type:

Any valid data type

characteristic:

LANGUAGE SQL

| [NOT] DETERMINISTIC

| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

| SQL SECURITY { DEFINER | INVOKER }

| COMMENT 'string'

func_body:

Valid SQL procedure statement

Explanation

To create a new stored function used the CREATE FUNCTION statement. therefore, you must have the CREATE ROUTINE database privilege for use the CREATE FUNCTION.
A function takes any range of the number of arguments and returns a value from the function (performing) body. furthermore, the body of the function can be any valid SQL expression as you’d use, for example, in any select (choose) expression.
therefore, If you’ve got the appropriate privileges, you can call the function exactly as you’d any inbuilt-function.

Create a Function Statement

You can also use a variant of the CREATE FUNCTION statement to install a user-defined function
(UDF) defined by a plugin. You can use a SELECT statement for the function body by enclosing it in parentheses, exactly as you would use a subselect for any other expression.
therefore, the SELECT statement will be must return a single value. If more than one column is returned when the function is called, error 1241 results.
furthermore, If more than one row is returned when the function is called, error 1242 results. Use a LIMIT clause to ensure only one row is returned.
Therefore, You can also replace the RETURN clause with a BEGIN…END compound statement. The compound statement must contain a RETURN statement. When the function is called, the RETURN statement immediately returns its result, and any statements after RETURN are effectively ignored.

the Built-in function

By default, a function is associated with the current database. To associate the function explicitly with a given database, specify the fully-qualified name as db_name.func_name when you create it.
therefore, If the function name is the same as the name of a built-in function, you must use the fully qualified name when you call it. the parameter list will be enclosed within parentheses must always be present. furthermore, If no parameters, then the empty parameter list of () should be used.
Notice:- The Parameter names are not case sensitive, each parameter can be declared to use any valid data type, except that the COLLATE attribute cannot be used. for valid identifiers to use as function names.

CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS rettype
      | RETURNS TABLE ( column_name column_type [, ...] ) ]
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | WINDOW
    | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | PARALLEL { UNSAFE | RESTRICTED | SAFE }
    | COST execution_cost
    | ROWS result_rows
    | SUPPORT support_function
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } ...

CREATE [ OR REPLACE ] FUNCTION

name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )

[ RETURNS rettype

| RETURNS TABLE ( column_name column_type [, ...] ) ]

{ LANGUAGE lang_name

| TRANSFORM { FOR TYPE type_name } [, ... ]

| WINDOW

| IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF

| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT

| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER

| PARALLEL { UNSAFE | RESTRICTED | SAFE }

| COST execution_cost

| ROWS result_rows

| SUPPORT support_function

| SET configuration_parameter { TO value | = value | FROM CURRENT }

| AS 'definition'

| AS 'obj_file', 'link_symbol'

} ...

Explanation:

In the above Code segment, CREATE FUNCTION defines a new function. CREATE OR REPLACE FUNCTION will either replace an existing definition or create a new function. you are able to define the function, the user must have the USAGE privilege on the language.
Furthermore, if a schema name will be included, then the function will be created in the specified schema. otherwise, it will be created in the current schema. the name of the new function must not match procedure with the same input argument types in the same schema or any existing function.
furthermore, the function and procedures of different argument types can share a name, this is known as the overloading.

Use CREATE or REPLACE Function

therefore, to replace the current definition of an existing function, you can use CREATE OR REPLACE FUNCTION. it is impossible to change the name or argument types of a function this method or way means that if you tried, you would actually be creating a new distinct function.
furthermore, also CREATE OR REPLACE will not let you change the return type of an existing function, you can do that, you want to drop and recreate the function.

Note: When using OUT parameters, which means you can’t modify or change the types of any OUT parameters except by dropping the function.

Therefore, When CREATE OR REPLACE FUNCTION is used to replace an existing function, the permission and ownership of the function do not change.
furthermore, all other function properties are assigned the values specified. you must own the function to replace it, which means that it included being a member of the owning role.

Drop and Recreate Function

suppose that if you want to drop and then recreate a function, the new function is not the same entity as the old, you will have to drop existing rules, triggers, views, etc. which refer to the old function, use CREATE OR REPLACE FUNCTION to change a function definition without breaking objects which refer to the function.
furthermore, also ALTER FUNCTION can be used to change most of the auxiliary properties of an existing function.
The user which creates function becomes the owner of the function.
therefore we are able to create a function, you must have USAGE privilege on the argument types and the return type.

Important Note:
The SQL Macros will be flattened in all cases, including DDL.
You can create a view on the queries that use SQL functions and then query the views. therefore, when you create the view, then a SQL function replaces a call to the user-defined function with the function body in the view definition. Therefore, when the body of the user-defined function is replaced, the view should also be replaced.
If multiple SQL functions with the same name and argument type are in the search path, the first match is used when the function is called.
The strictness and volatility (immutable, volatile, or stable) of an SQL Macro are automatically inferred from the function’s definition. Vertica then determines the correctness of usage, such as where an immutable function is expected but a volatile function is provided.

Question: How to use SQL Server built-in functions and create user-defined scalar functions?

A function is a set of SQL statement which perform the special task if you have to repeat, the write large SQL script, to perform the same or task, you are free to create a function which performs the task.
Let us see and work with a simple example.

Create function SQL | First of all, Preparing the data

CREATE DATABASE collegedb
CREATE TABLE candidate
(
    id INT PRIMARY KEY,
    name VARCHAR(70) NOT NULL,
    gender VARCHAR(50) NOT NULL,
    DOB datetime NOT NULL,
    total_point INT NOT NULL,
)
INSERT INTO candidate
VALUES (1, 'Priya', 'Female', '17-JUN-1990', 400), 
(2, 'Amit', 'Male', '12-FEB-1984', 615), 
(3, 'Sarita', 'Female', '17-MAR-1998', 500), 
(4, 'Kabita', 'Female', '20-DEC-1986', 450), 
(5, 'Vijay', 'Male', '21-JUL-1981', 630), 
(6, 'Pinki', 'Female', '03-May-1984', 400), 
(7, 'Sumit', 'Male', '19-APR-1981', 613), 
(8, 'Kuldeep', 'Male', '29-AUG-1964', 553), 
(9, 'Sachin', 'Male', '25-NOV-1990', 699), 
(10, 'Elis', 'Female', '28-OCT-1990', 400);

CREATE DATABASE collegedb

CREATE TABLE candidate

(

id INT PRIMARY KEY,

name VARCHAR(70) NOT NULL,

gender VARCHAR(50) NOT NULL,

DOB datetime NOT NULL,

total_point INT NOT NULL,

)

INSERT INTO candidate

VALUES (1, 'Priya', 'Female', '17-JUN-1990', 400),

(2, 'Amit', 'Male', '12-FEB-1984', 615),

(3, 'Sarita', 'Female', '17-MAR-1998', 500),

(4, 'Kabita', 'Female', '20-DEC-1986', 450),

(5, 'Vijay', 'Male', '21-JUL-1981', 630),

(6, 'Pinki', 'Female', '03-May-1984', 400),

(7, 'Sumit', 'Male', '19-APR-1981', 613),

(8, 'Kuldeep', 'Male', '29-AUG-1964', 553),

(9, 'Sachin', 'Male', '25-NOV-1990', 699),

(10, 'Elis', 'Female', '28-OCT-1990', 400);

as the following code, this script will create the database “collegedb” on your server. The database will have one table with five(5) columns. such as id, name, gender, DOB, and “total_point”. furthermore, the table will also contain 10 dummy candidate records.

Create function SQL | Built-in functions

To built-in functions for your “collegedb”, go to Object Explorer-> Databases-> collegedb-> Programmability-> Functions-> System Functions. These give you, the list of all build-in functions as given below.
create function sql
Therefore, in the System Function folder, build-in functions are grouped into different folders, these folders depending on the functionality. if you focus on the image, click on the “Date and Time Functions” folder as shown in the above image, then you will see that the date and time-related functions.
furthermore, expend any function then you will see the parameter’s type and also the value returned by the function.

Create function SQL | See the Figure to Understand

Therefore, to expend the “Datename” function, you will find that it accepts two parameters. The first parameter is the “Date part”, this is type varchar, and the second parameter is “Expression” and which is a DateTime type parameter. this function returns a varchar type value.

let’s create a query that selects the names and year of birth of the candidate. when we insert the dummy records, however, using the datename function we will retrieve only the birth-year of a candidate. let’s look at the following query:

USE collegedb
SELECT name, DATENAME(YEAR, DOB) AS BIRTH_YEAR
FROM candidate

USE collegedb

SELECT name, DATENAME(YEAR, DOB) AS BIRTH_YEAR

FROM candidate

as a result, in the above query here, we have used the Dataname function. we passed “YEAR” as the data part and the DOB (Date of Birth) column as the DateTime expression to the function, the above query will return the following.

Name	Year
Priya	1990
Amit	1984
Sarita	1998
Kabita	1986
Vijay	1981
Pinki	1984
Sumit	1981
Kuldeep	1964
Sachin	1990
Elis	1990

User-defined functions

as per the build-in function does not offer always desired function, here we take the “Datename” function, as you see in the above. although it retrieves the data in multiple formats, suppose that if you want to retrieve the data in a different format, one that is not supported by the “Datename” function. Do we want to retrieve the candidate date of birth in formate “Tuesday, 21 Jun 1990”? No built-in function retrieves data of birth in this format. to do this task we need to call the “Datename” function multiple times and on string concatenation in order to retrieve data in our desired or suitable formate.

USE collegedb
SELECT
 name,
  DATENAME(DW, DOB)+ ', '+
  DATENAME(DAY, DOB)+ ' '+
  DATENAME(MONTH, DOB) +', '+
  DATENAME(YEAR, DOB) AS DOB
FROM candidate

USE collegedb

SELECT

name,

DATENAME(DW, DOB)+ ', '+

DATENAME(DAY, DOB)+ ' '+

DATENAME(MONTH, DOB) +', '+

DATENAME(YEAR, DOB) AS DOB

FROM candidate

Explanation

furthermore, as you can see that we called “Datename” function four(4) times. every time we passed it a different data part. On the first call, we passed DW as the parameter which returns the day of the week. after that concatenated the result with another call to “Datename” function this returned day of the month. therefore, we retrieved the year name and month name from the DOB (Date of Birth) column of the candidate table. as per the above query, we get the result the following.

Name	Year
Priya	Sunday, 17 JUN, 1990
Amit	Sunday, 12 FEB, 1984
Sarita	Tuesday 17 MAR, 1998
Kabita	Saturday, 20 DEC, 1986
Vijay	Tuesday 21 JUL, 1981
Pinki	Tuesday 03 May, 1984
Sumit	Sunday 19 APR, 1981
Kuldeep	saturday 29 AUG, 1964
Sachin	Sunday 25 NOV, 1990
Elis	Sunday 28 OCT, 1990

Create Function SQL | How to Create Function in SQL?

Syntax:

Parameters Description

Privileges (Non-superuser:)

Create Function SQL | Limitations and Restrictions

Create Function SQL | Procedure

Results

Program Example

Create Function SQL | Syntax

Explanation

Create a Function Statement

the Built-in function

Explanation:

Use CREATE or REPLACE Function

Drop and Recreate Function

Question: How to use SQL Server built-in functions and create user-defined scalar functions?

Create function SQL | First of all, Preparing the data

Create function SQL | Built-in functions

Create function SQL | See the Figure to Understand

User-defined functions

Explanation

Related Posts

About Author

admin