Subscribe For Free Updates!

We'll not spam mate! We promise.

Sep 1, 2012

SQL Server 2008:How To Create User Defined Functions in SQL

Views:

SQL User Defined Functions
To day i will Show you how to Create User defined Function SQL Server and how to Use it.
I've already shared  how create Store Procedures and How to Get a Return Value From Store Procedures in SQL Server.
Store Procedure ,User Define Function and User Define or User Created Views are Always Saved in the Data Base (MDF file). They are all Fast and Pre-Compiled which means we call these Functions or Store Procedures  From Our business logic layer or (Simply from Coding ) where they are Directly Executed and thus which Saves time.
Difference of time saving is noticible in enviorments where you have 25-30 tables and Each table has 1,000-50,000 rows, in these Situations we Feel the Difference between In-Line Queries and Pre-Compiled SQL Statements(Store Procedure ,User Define Function and User Define or Created Views  etc).



This Article Covers the basics of User Define Functions. I also will discuss Why  (and how ) to create them and How Use them.

In SQL The primary formula of creating a function is:
CREATE FUNCTION FunctionName()
A function to be useful for us must produce a result. Means A function returns a result or a value. During  creation of a function, you must Define the type of value that the function would return. To provide this information, after the name of the function, type the RETURNS keyword followed by a definition for a data type. simple example is Below:
CREATE FUNCTION Addition()
RETURNS Decimal(6,3)

After Defining the Return Type or Function we can Create body of function . Body of a function starts with the BEGIN and ends with the END keywords. example Below:
CREATE FUNCTION Addition()
RETURNS Decimal(6,3)
AS
BEGIN

END
AS Keyword is Optional.

You can define the assignment of the function between the BEGIN and END keywords,and that represents the body of the function. After performing this assignment, just before the END keyword, you must specify value i.e. function returns. This is done by typing the RETURN keyword . sample Code Below:
CREATE FUNCTION GetFullName()
RETURNS varchar(100)
AS
BEGIN
 RETURN 'www.visualstudiolearn.com'
END

Here is another example:

CREATE FUNCTION CalculateWeeklySalary()
RETURNS Decimal(8, 2)
AS
BEGIN
    RETURN 880.44
END
After Writing Above code in Query Windows Press F5 to Create Function in Data Base.

Function Calling
Now function was created, By Calling you can get the value it returns.  For Calling User Function, type the name of the Data Base 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. Sample is Below:
DatabaseName.dbo.FunctionName()
Because a function returns a value, you can use that value. 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.
To rename a function, in the Object Explorer, right-click it and click Rename. Type the desired new name and press Enter.

Parameterized Function
To create a function that takes a Arguments or Parameters , specify a name and the type of value of the parameter(s) in its parentheses when you are Creating a function. Sample is Below:
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 Parametrized Function
Calling a Function that takes one parameter, you must supply value for that argument. Type the value of the parameter in the parentheses of the function.  example is below:
Print  DataBaseName.dbo.Addition(488);
GO


Function With More  then One Arguments

Instead of only one Argument, you can also make a function that takes more than one or Various parameter. For this,  separate the arguments in the parentheses of the function with a comma. example Below:
CREATE FUNCTION Addition(@Number1 Decimal(6,2), @Number2 Decimal(6,2))
you can use the parameters as if you already knew their value in function body. You can also declare local variables and involve them with parameters as you see fit. example Below:
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, we must provide a value for each parameter, 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


Sample How to Use User Define Function in Select Query  or any Other  SQL Query.
Sample Code Below

SELECT *
FROM tb_Employees AS E,
     dbo.fn_EmployeesInDept('shipping') AS EID
WHERE E.EmployeeID = EID.EmployeeID

Kinds of User-Defined Functions :-
There are three types of User-Defined functions in SQL Server 2000 and they are (1)Scalar,Inline (2)Table-Valued and (3)Multi-statement Table-valued.

Benefits of User-Defined Functions:-

The benefits to SQL Server User-Defined functions are numerous. First, we can use these functions in so many different places when compared to the SQL Server stored procedure. The ability for a function to act like a table (for Inline table and Multi-statement table functions) gives developers the ability to break out complex logic into shorter and shorter code blocks. This will generally give the additional benefit of making the code less complex and easier to write and maintain. In the case of a Scalar User-Defined Function, the ability to use this function anywhere you can use a scalar of the same data type is also a very powerful thing. Combining these advantages with the ability to pass parameters into these database objects makes the SQL Server User-Defined function a very powerful tool.

A Video Tutorial On User Define Function in SQL 2008 , R2



















Hope You Guys Enjoyed The Tutorial. Let Me know If you have any questions??


Plese Feel Free to Socializer This Post
SOCIALIZE IT →
FOLLOW US →
SHARE IT →

1 comments:

  1. If you would like an alternative to casually picking up girls and trying to figure out the right thing to say...

    If you would rather have women chase YOU, instead of spending your nights prowling around in noisy bars and night clubs...

    Then I urge you to view this short video to learn a amazing secret that might get you your very own harem of attractive women:

    FACEBOOK SEDUCTION SYSTEM!!!

    ReplyDelete

Become a Fan

visual studio learn