1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

User-defined Functions In SQL Server Part 1

Discussion in 'SQL Server' started by Sagar Jaybhay, Jan 25, 2020.

Tags:
  1. Sagar Jaybhay

    Sagar Jaybhay New Member

    Joined:
    Jan 28, 2019
    Messages:
    23
    Likes Received:
    12
    Trophy Points:
    3
    Gender:
    Male
    Occupation:
    Sr. Software Developer
    Location:
    Pune
    Home Page:
    User-defined functions also called UDF. In SQL server there are 3 different types of User-Defined functions

    a) Scalar function.

    b) Inline table-valued functions.

    c) Multi-statement table-valued functions.

    You can find these functions folder in SQL Server Management studio see below image

    User Defined Function In SQL Server.png


    Scalar Functions

    The scalar function is a function that may or may not have parameters but always return a single value. The return type of scalar function can be any data-type except text, image, cursor, and timestamp. The scalar function may or may not have parameters but it should return a single value.

    Why this function called scalar function because it returns a single value.

    Example:

    In-built function with parameter

    Code:
    select ABS(-1000) // op 1000

    In-built function with 0 parameter
    Code:
    Select GetDate() //
    
    General Syntax for Scalar function:

    Code:
    CREATE FUNCTION [schema_name.]function_name (parameter_list)
    
    RETURNS  return-data-type AS
    
    BEGIN
    
    statements
    
    RETURN value
    
    END
    
    How to create User-defined Scalar function in the SQL server?


    Code:
    Create function GetAge(@bdate nvarchar(100))
    
    returns int
    
    as
    
    begin
    
    declare @age Int
    
    set @age=DATEDIFF(YEAR,cast(@bdate as date),Getdate());
    
    return @age;
    
    end
    
    
    In the above function, we calculate the age.

    To call this function if you only use

    Select function-name(parameter); then it will give an error to overcome this error we need to give a fully qualified name or 2 part name or at-least database owner and name append before the function name. In our case, dbo is a database owner.

    Code:
     select dbo.GetAge('12/01/1988'); 
    Second example: you can use this in select clause as shown below


    Code:
    select id,first_name,last_name,dbo.getage(dateofbirth) from studentTable
    
    
    You can do this in store-procedure also but you can not use stored procedure in select clause and where clause.


    How to alter function in the SQL server?


    Code:
    ALTER function [dbo].[GetAge](@bdate nvarchar(100))
    returns int
    as
    begin
    declare @age Int
    set @age=DATEDIFF(YEAR,cast(@bdate as date),Getdate());
    return @age;
    end
    

    The alter keyword needs to use instead of create statement and whole function you need to write here.

    How to delete or remove a function from the SQL server?


    You need to use a drop keyword and function name to remove function in the SQL server. Also, a fully qualified name or database administrator name needs to use along with function name if you use only function name then it will give an error.

    Code:
    drop function dbo.getage
     
    shabbir likes this.

Share This Page