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 FunctionCalling 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.
If you would like an alternative to casually picking up girls and trying to figure out the right thing to say...
ReplyDeleteIf 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!!!