Pages

Men

rh

10/18/2013

Functions in Sql Server

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 textntextimagecursor, 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