create function

7/28/2019 Create function

1/4

Microsoft SQL Server - Transact-SQL Functions: Creating a Function

Creating a Function

troduction

n Transact-SQL, the primary formula of creating a function is:

EATE FUNCTION FunctionName()

or a function to be useful, it must produce a result. This is also said that the function returns aesult or a value. When creating a function, you must specify the type of value that the function

would return. To provide this information, after the name of the function, type the RETURNSkeyword followed by a definition for a data type. Here is a simple example:

EATE FUNCTION Addition()

TURNS Decimal(6,3)

After specifying the type of value that the function would return, you can create a body for theunction. The body of a function starts with the BEGIN and ends with the END keywords. Here is anxample:


TURNS Decimal(6,3)

GIN

D

Optionally, you can type the AS keyword before the BEGIN keyword:


TURNS Decimal(6,3)

GIN

D

Between the BEGIN and END keywords, which is the section that represents the body of theunction, you can define the assignment the function must perform. After performing thisssignment, just before the END keyword, you must specify the value that the function returns. Thiss done by typing the RETURN keyword followed by an expression. A sample formula is:


TURNS Decimal(6,3)

GIN

RETURNExpression

D

Here is an example

EATE FUNCTION GetFullName()

TURNS varchar(100)

le:///C|/Documents%20and%20Settings/michael.musyoki/Desktop/SS/createF.htm (1 of 4)11/26/2008 5:10:05 PM
http://functionx.com/sqlserver/index.htmhttp://c.casalemedia.com/c?s=56757&f=2&id=4701797605.991578


2/4


GIN

RETURN 'Doe, John'

D

Here is another example:

EATE FUNCTION CalculateWeeklySalary()

TURNS Decimal(8, 2)

GIN

RETURN 880.44

D;

unction Calling

After a function has been created, you can use the value it returns. Using a function is also referredo as calling it. To call a function, you must qualify its name. To do this, type the name of theatabase in which it was created, followed by the period operator, followed by dbo, followed by theeriod operator, followed by the name of the function, and its parentheses. The formula to use is:

tabaseName.dbo.FunctionName()

Because a function returns a value, you can use that value as you see fit. For example, you can use

ither PRINT or SELECT to display the function's value in a query window. Here is an example thatalls the above Addition() function:

INT Exercise.dbo.GetFullName();

As an alternative, to call a function, in the Object Explorer, right-click its name, position the mousen Script Function As, SELECT To, and click New Query Editor Window.

enaming a Function

To rename a function, in the Object Explorer, right-click it and click Rename. Type the desired newame and press Enter.

odifying a Function

As mentioned already, in the body of the function, you define what the function is supposed to takeare of. As a minimum, a function can return a simple number, typed on the right side of the

RETURN keyword. Here is an example:


TURNS int

GIN

RETURN 1

D

You can also declare new variables in the body of the function to help in carrying the assignment. Aariable declared in the body of a function is referred to as a local variable. Once such a variable haseen declared, it can be used like any other variable. Here is an example:


TURNS int

GIN

DECLARE @Number1 int

SET @Number1 = 588

RETURN @Number1 + 1450

D

Parameterized Function

To create a function that takes a parameter, specify a name and the type of value of the parameters) in its parentheses. Here is an example:



3/4


EATE FUNCTION Addition(@Number1 Decimal(6,2))

When a function takes a parameter, in the body of the function, you can use the parameter as if youknew its value, as long as you respect the type of that value. Here is an example:

EATE FUNCTION Addition(@Number1 Decimal(6,2))

TURNS Decimal(6,2)

GIN

RETURN @Number1 + 1450

D

alling a Parameterized Function

When you call a function that takes one parameter, you must supply a value for that argument. Too this, type the value of the parameter in the parentheses of the function. Here is an example:

Function With Various Arguments

nstead of only one parameter, you can also create a function that takes more than one parameter.n this case, separate the arguments in the parentheses of the function with a comma. Here is anxample:

EATE FUNCTION Addition(@Number1 Decimal(6,2), @Number2 Decimal(6,2))

Once again, in the body of the function, you can use the parameters as if you already knew theiralue. You can also declare local variables and involve them with parameters as you see fit. Here isn example:

EATE FUNCTION Addition(@Number1 Decimal(6,2),

@Number2 Decimal(6,2))

TURNS Decimal(6,2)

GIN

DECLARE @Result Decimal(6,2)

SET @Result = @Number1 + @Number2

RETURN @Result

D;

When calling a function that takes more than one parameter, in the parentheses of the function,rovide a value for each parameter, in the exact order they appear in the parentheses of theunction. Here is an example:

INT Variables1.dbo.Addition(1450, 228);

You can also pass the names of already declared and initialized variables. Here is an example thatalls the above function:

CLARE @Nbr1 Decimal(6,2),

@Nbr2 Decimal(6,2)

T @Nbr1 = 4268.55



4/4


T @Nbr2 =26.83

LECT @Nbr1 As First,

@Nbr2 As Second,

Variables1.dbo.Addition(@Nbr1, @Nbr2) AS Result

This would produce:

Home Copyright 2008 FunctionX, Inc.

>

http://functionx.com/sqlserver/index.htmhttp://functionx.com/sqlserver/index.htmhttp://functionx.com/sqlserver/index.htm