Functions in SQL Server

Discussion in 'SQL Server' started by MinalS, May 1, 2016.

  1. In SQL, the set of statements are stored permanently in a function. The functions are user defined functions.

    User defined functions



    The user defined functions is the object of database containing statements used for used for performing the process, accepting the parameters, provide the result as the output. The value returned after the function is processed can be scalar for tabled value.

    The functions are created only if the user needs to perform the actions on particular table. The coding is implemented only if the user does not need any fixed changes to the objects outside the functions. The database table cannot be altered from the function.

    The different parts of the functions are as listed below:
    1. The name of the function
    2. The name of the input parameter and the data type
    3. The options applied to the input parameter
    4. The return parameter data type
    5. More than one T – SQL statements
    The CREATE FUNCTION is used for creation of a function in SQL. The syntax is:

    Code:
    CREATE FUNCTION [ schemaname. ] [ functionname  ( [ { parametername [ AS ] 
    [ type_schemaname. ] parameterdatatype [ = default ] } [ ,….n ]
    )
    RETURNS return datatype [ WITH [ SCHEMABINDING ] [ ENCRYPTION ] 
    [ EXECUTEAS ] [ ,…n ] ]
    [ AS ] 
    BEGIN 
        Body of the function
        Return expression
    END
    [ ; ]
    
    Where,
    • schemaname is the name of the schema for the user defined function
    • functionname is the name of the function
    • parametername is the parameter used in the user defined function
    • [type_schema_name. ] parameter_datatype is the data type used in the parameter
    • [ = default ] is the default value of the parameter
    • return_data_type is the return value of the user defined function

    Scalar functions



    A single parameter is accepted by the scalar function and a single data value is returned. The RETURNS clause is used for returning the result. The data type returned by the function cannot be text, image, cursor, ntext, and timestamp. The group of statements are added inside the BEGIN….END block.

    Example for demonstrating the use of scalar function

    The total marks of the student are added and the percentage value for the corresponding student is determined. Create a scalar function for the same.

    Code:
    create function percentage1 ( @totalmarks int )
    returns int
    as
        begin
            return ( @totalmarks * 100 / 100 )
        end
    
    Execute the function using the following statement.

    Code:
    declare @totalmarks int
    set @totalmarks = percentage1 ( 870 )
    print @totalmarks
    

    Table valued functions



    The table values function always provides table as an output which can be accessed from the SELECT statement. The set of rows are stored in the table data. There are two types of table valued functions as:
    1. Inline table valued function
    2. Multistatement table valued function
    Inline table valued function

    The inline table valued function always provides a variable of the data type as table. A SELECT statement is used for calculating the data. There is no body for a function within begin and end blocks.

    Example:

    Consider an inline table valued function as location_name which has @locname as the input parameter and provides the all the corresponding details from the table.

    Code:
    create function location_name ( @locname varchar(40) )
    returns table
    as
    return ( select * from orderdata where location = @locname )
    go
    
    Execute the following query to produce the output from the function.
    Code:
    select * from location_name ( ‘Melbourne’ )
    
    Multistatement table valued function

    The multistatement table valued function contains set of statements used for creating a table which is returned by the calling statement. The BEGIN and END blocks are present in the function declaration. The temporary table is used for returning the result set and is dependent on the function.

    Consider the following example to demonstrate the use of multistatement table valued function.
    Code:
    create function productdetails ( @ProductID int )
    returns @table table
        ( ProductID int not null,
        productname varchar(20) not null,
        quantity int not null,
        location varchar(20) not null,
        price int not null)
        as
        begin
            insert @table
            select * from productdata where ProductID = @ProductID
            return
        end
    
    Execute the following query to display the value.
    Code:
    select * from productdetails ( 411 )
    
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice