Functions:
functions in programming languages, SQL Server user-defined
functions are routines that accept parameters, perform an action, such as a
complex calculation, and return the result of that action as a value. The
return value can either be a single scalar value or a result set.
The benefits of
using user-defined functions in SQL Server are:
- They allow modular programming.
You can create the function once, store
it in the database, and call it any number of times in your program.
User-defined functions can be modified independently of the program source
code.
- They allow faster execution.
Similar to stored procedures,
Transact-SQL user-defined functions reduce the compilation cost of Transact-SQL
code by caching the plans and reusing them for repeated executions. This means
the user-defined function does not need to be reparsed and reoptimized with
each use resulting in much faster execution times.
CLR functions offer significant
performance advantage over Transact-SQL functions for computational tasks,
string manipulation, and business logic. Transact-SQL functions are better
suited for data-access intensive logic.
- They can reduce network traffic.
An operation that filters data based on
some complex constraint that cannot be expressed in a single scalar expression
can be expressed as a function. The function can then invoked in the WHERE
clause to reduce the number or rows sent to the client.
Table-Valued Functions
User-defined table-valued functions
return a table data type. For an inline table-valued function,
there is no function body; the table is the result set of a single SELECT
statement.
Scalar Function
User-defined scalar functions return a
single data value of the type defined in the RETURNS clause. For an inline
scalar function, there is no function body; the scalar value is the result of a
single statement. For a multistatement scalar function, the function body,
defined in a BEGIN...END block, contains a series of Transact-SQL statements
that return the single value. The return type can be any data type except text, ntext, image, cursor,
and timestamp.
System Functions
SQL Server provides many system
functions that you can use to perform a variety of operations. They cannot be
modified. For more information
Table Valued functions can be classified into 2
types.
1) Inline Table Valued Functions
2) New Multi Statement table-valued
functions.
Syntax : Inline Table Valued Functions Synatax:
CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName>
(
-- Add the
parameters for the function here
<@param1,
sysname, @p1> <Data_Type_For_Param1, , int>,
<@param2,
sysname, @p2> <Data_Type_For_Param2, , char>
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT
statement with parameter references here
SELECT 0
)
GO
Example :
CREATE FUNCTION Infn_GetOrderMasterDetails
(
@State VARCHAR(250)
)
RETURNS TABLE
AS
RETURN
(
SELECT JOBNO,JobName,State,Amount FROM
dbo.Ordermaster WHERE State = @State
)
GO
Using the Function
The
next step is to use the function. If we want to select all the rows having Id
equal to ‘AP’ or other, the query would look like:
SELECT * FROM Infn_GetOrderMasterDetails(‘AP’)
Result would be below :
New Multi-statement Table-valued
Functions
Syntax
CREATE FUNCTION <Table_Function_Name, sysname, FunctionName>
(
-- Add the
parameters for the function here
<@param1,
sysname, @p1> <data_type_for_param1, , int>,
<@param2,
sysname, @p2> <data_type_for_param2, , char>
)
RETURNS
<@Table_Variable_Name, sysname, @Table_Var> TABLE
(
-- Add the column
definitions for the TABLE variable here
<Column_1,
sysname, c1> <Data_Type_For_Column1, , int>,
<Column_2,
sysname, c2> <Data_Type_For_Column2, , int>
)
AS
BEGIN
-- Fill the table
variable with the rows for your result set
RETURN
END
GO
Example:
CREATE FUNCTION NmlFn_StoreOrderDetails
(
@Jobno BIGINT
)
RETURNS @ServiceMaster TABLE
(
JOBNO INT,
JOBNAME VARCHAR(250),
STATE VARCHAR(250),
TOTALAMOUNT DECIMAL(18,2)
)
AS
BEGIN
--
Fill the table variable with the rows for your result set
INSERT INTO
@ServiceMaster(JOBNO,JOBNAME,STATE,TOTALAMOUNT)
SELECT JobNo,JobName,State,TOTALAMOUT FROM
dbo.Ordermaster WHERE JobNo =@Jobno
RETURN
END
GO
To view the data
SELECT * FROM
NmlFn_StoreOrderDetails(1000)
Result would be:
Scalar Functions Syntax:
CREATE FUNCTION
<Scalar_Function_Name, sysname, FunctionName>
(
-- Add the parameters for the
function here
<@Param1, sysname, @p1>
<Data_Type_For_Param1, , int>
)
RETURNS
<Function_Data_Type, ,int>
AS
BEGIN
-- Declare the return variable
here
DECLARE <@ResultVar, sysname,
@Result> <Function_Data_Type, ,int>
-- Add the T-SQL statements to
compute the return value here
SELECT <@ResultVar, sysname,
@Result> = <@Param1, sysname, @p1>
-- Return the result of the
function
RETURN <@ResultVar, sysname,
@Result>
END
GO
Example
:
CREATE FUNCTION [dbo].[Calculate]
(@LastYearValue decimal(18,2), @ThisYearValue decimal(18,2))
RETURNS decimal(18,2)
AS BEGIN
DECLARE @Hold Decimal(18,2)
SET @Hold = 0
IF @LastYearValue > 0
BEGIN
SET
@Hold = ((@ThisYearValue - @LastYearValue) / @LastYearValue) * 100.00;
END
ELSE
BEGIN
SET
@Hold = (@ThisYearValue - @LastYearValue);
END
RETURN ISNULL(@Hold,0)
END
No comments :
Post a Comment