Skip to main content

Creating a Function

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

CREATE FUNCTION FunctionName()

For a function to be useful, it must produce a result. This is also said that the function returns a result 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:
CREATE FUNCTION Addition()
RETURNS Decimal(6,3)
After specifying the type of value that the function would return, you can create a body for the function. The body of a function starts with the BEGIN and ends with the END keywords. Here is an example:
CREATE FUNCTION Addition()
RETURNS Decimal(6,3)
BEGIN

END
Optionally, you can type the AS keyword before the BEGIN keyword:
CREATE FUNCTION Addition()
RETURNS Decimal(6,3)
AS
BEGIN

END
Between the BEGIN and END keywords, which is the section that represents the body of the function, you can define the assignment the function must perform. After performing this assignment, just before the END keyword, you must specify the value that the function returns. This is done by typing the RETURN keyword followed by an expression. A sample formula is:
CREATE FUNCTION Addition()
RETURNS Decimal(6,3)
AS
BEGIN
    RETURN Expression
END
Here is an example
CREATE FUNCTION GetFullName()
RETURNS varchar(100)
AS
BEGIN
 RETURN 'Doe, John'
END
Here is another example:
CREATE FUNCTION CalculateWeeklySalary()
RETURNS Decimal(8, 2)
AS
BEGIN
    RETURN 880.44
END;
GO
Function Calling
After a function has been created, you can use the value it returns. Using a function is also referred to as calling it. To call a function, you must qualify its name. To do this, type the name of the database in which it was created, followed by the period operator, followed by dbo, followed by the period operator, followed by the name of the function, and its parentheses. The formula to use is:
DatabaseName.dbo.FunctionName()
Because a function returns a value, you can use that value as you see fit. For example, you can use either PRINT or SELECT to display the function's value in a query window. Here is an example that calls the above Addition() function:
PRINT Exercise.dbo.GetFullName();
As an alternative, to call a function, in the Object Explorer, right-click its name, position the mouse on Script Function As, SELECT To, and click New Query Editor Window.
Renaming a Function
To rename a function, in the Object Explorer, right-click it and click Rename. Type the desired new name and press Enter.
Modifying a Function
As mentioned already, in the body of the function, you define what the function is supposed to take care of. As a minimum, a function can return a simple number, typed on the right side of the RETURN keyword. Here is an example:
CREATE FUNCTION Addition()
RETURNS int
BEGIN
    RETURN 1
END
You can also declare new variables in the body of the function to help in carrying the assignment. A variable declared in the body of a function is referred to as a local variable. Once such a variable has been declared, it can be used like any other variable. Here is an example:
CREATE FUNCTION Addition()
RETURNS int
BEGIN
    DECLARE @Number1 int
    SET @Number1 = 588
    RETURN @Number1 + 1450
END
A Parameterized Function
To create a function that takes a parameter, specify a name and the type of value of the parameter(s) in its parentheses. Here is an example:
CREATE 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 you knew its value, as long as you respect the type of that value. Here is an example:
CREATE FUNCTION Addition(@Number1 Decimal(6,2))
RETURNS Decimal(6,2)
BEGIN
    RETURN @Number1 + 1450
END
Calling a Parameterized Function
When you call a function that takes one parameter, you must supply a value for that argument. To do this, type the value of the parameter in the parentheses of the function. Here is an example:
A Function With Various Arguments
Instead of only one parameter, you can also create a function that takes more than one parameter. In this case, separate the arguments in the parentheses of the function with a comma. Here is an example:
CREATE 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 their value. You can also declare local variables and involve them with parameters as you see fit. Here is an example:
CREATE FUNCTION Addition(@Number1 Decimal(6,2),
    @Number2 Decimal(6,2))
RETURNS Decimal(6,2)
BEGIN
    DECLARE @Result Decimal(6,2)
    SET @Result = @Number1 + @Number2
    RETURN @Result
END;
GO
When calling a function that takes more than one parameter, in the parentheses of the function, provide a value for each parameter, in the exact order they appear in the parentheses of the function. Here is an example:
PRINT Variables1.dbo.Addition(1450, 228);
You can also pass the names of already declared and initialized variables. Here is an example that calls the above function:
DECLARE @Nbr1 Decimal(6,2),
        @Nbr2 Decimal(6,2)
SET @Nbr1 = 4268.55
SET @Nbr2 =26.83
SELECT @Nbr1 As First,
       @Nbr2 As Second,
       Variables1.dbo.Addition(@Nbr1, @Nbr2) AS Result
This would produce:

Comments

Post a Comment

Popular posts from this blog

ASP .NET - XML Files

An XML File Here is an XML file named "countries.xml": <?xml version="1.0" encoding="ISO-8859-1"?> <countries> <country>   <text>Norway</text>   <value>N</value> </country> <country>   <text>Sweden</text>   <value>S</value> </country> <country>   <text>France</text>   <value>F</value> </country> <country>   <text>Italy</text>   <value>I</value> </country> </countries> Bind a DataSet to a List Control First, import the "System.Data" namespace. We need this namespace to work with DataSet objects. Include the following directive at the top of an .aspx page: <%@ Import Namespace="System.Data" %> Next, create a DataSet for the XML file and load the XML file into the DataSet when the page is first loaded: <script runat="server"> sub Page_Load if Not Page.IsPos...
Post a Comment
Read more

Net FrameWork (Basic Concepts)

Net FrameWork (Basic Concepts) See more presentations by aSGuest51943  |  Upload your own PowerPoint presentations .Net Framework (Basic Concepts) : .Net Framework (Basic Concepts) 1 Amrita Academy For Career Excellence Overview of the .NET Framework : Overview of the .NET Framework Software development environment which helps developers to develop applications quickly and gives optimum, efficient, scalable, performance oriented applications in different languages like Visual Basic .NET, C#, ASP.NET, and Jscript .NET etc... 2 Amrita Academy For Career Excellence Slide 3: Services NET Framework provides the following services: Tools for developing software applications , run-time environments for software application to execute, server infrastructure, value added intelligent software which helps developers to do less coding and work efficiently. The .Net Framework will enable developers to develop applications for various devices and platforms like window...
Post a Comment
Read more

Crystal and Reporting Services Interview Questions - Part 2

Introduction   This FAQ will give you a quick start for two report giant's crystal and reporting services. We have two IIS application 'Reports' and 'Reportserver' what do they do ?   When you install reporting services there are two virtual directories created as shown in the figure below. Figure : - IIS Applications for reports The 'Reports' virtual directory is like an admin. If you browse to http://yourpcname/reports/home.aspx   page you can view, edit properties and display reports. We have numbered the below figure. 1 à We can browse reports and see the output of reports. 2 à We can upload a RDL file directly in the report's database. 3 à Using the report builder link we build new reports. 4 à Using the properties we can do role assignment. 5 à Using subscription link we can add new outputs (FTP, Folder etc) to subscribe to the reports. 6 à Site settings help us to decide how many numbers of snapshots we want in the history, report time out...
Post a Comment
Read more