Function in SQL

Function:

Functions are compact pieces of Transact SQL code, which can accept parameters, and return either a value, or a table. They are saved as individual work units, and are created using standard SQL commands. There are two types of function in SQL server first one is Built-in function, Built-in functions are those function which is offered by SQL Server that can be used either in your SELECT clause. For the most part these functions provide complex coding that would be very difficult to get this same functionality without these functions. And second one is User Define Function, User Define Function are those SQL function which is develop by you for particular purpose.

There are three types of user define scalar functions which are namely given as follows:

Scalar Function:

A Scalar user-defined function returns one of the scalar data types. Text, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value.

Syntax:Creating Scalar function:

-----SYNTAX DEMONSTRATION OF CREATING SCALAR FUNCTION 
CREATE FUNCTION <FUNCTION_NAME>
(
    -----ACCEPT PARAMETER OF FUNCTION
   @PARAMETER_NAME1 DATA_TYPES,
   @PARAMETER_NAME2 DATA_TYPES,
    ..........           ..........
    ..........           ..........
)
RETURNS DATA_TYPES OF VARRIABLE
AS
BEGIN
 ----------FUNCTION BODY--------
----------WRITE SELECT, INSERT, UPDATE, DELETE ETC COMMAND AND RETURN VALUE
RETURN QUERY_STATEMENT
END

Example: Creating scalar function:

 ----------CREATING SCALAR FUNCTION IN SQL SERVER-------
USE [ArunSingh]
GO
/****** Object:  UserDefinedFunction [dbo].[TestScalarFunction]    Script Date: 07/11/2011 14:48:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:         <Arun Singh>
-- Create date: <11 July, 2011>
-- Description:   <TestScalar Function description>
-- =============================================
CREATE FUNCTION [dbo].[TestScalarFunction] 
(
      @NAME VARCHAR(50)
)
RETURNS varchar(50)
AS
BEGIN
       DECLARE @SELECTNAME VARCHAR(50)
       SET @SELECTNAME =  @NAME
       RETURN @SELECTNAME 
       
  
END

Syntax: Alter Scalar function

-----SYNTAX DEMONSTRATION OF CREATING SCALAR FUNCTION 
ALTER FUNCTION <FUNCTION_NAME>
(
    -----ACCEPT PARAMETER OF FUNCTION
   @PARAMETER_NAME1 DATA_TYPES,
   @PARAMETER_NAME2 DATA_TYPES,
    ..........           ..........
    ..........           ..........
)
RETURNS DATA_TYPES OF VARRIABLE
AS
BEGIN
 ----------FUNCTION BODY--------
----------WRITE SELECT, INSERT, UPDATE, DELETE ETC COMMAND AND RETURN VALUE
RETURN QUERY_STATEMENT
END

Example: Alter Scalar Function

USE [ArunSingh]
GO
/****** Object:  UserDefinedFunction [dbo].[TestScalarFunction]    Script Date: 07/11/2011 14:48:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:         <Arun Singh>
-- Create date: <11 July, 2011>
-- Description:   <TestScalar Function description>
-- =============================================
ALTER FUNCTION [dbo].[TestScalarFunction] 
(
      @NAME VARCHAR(50)
)
RETURNS varchar(50)
AS
BEGIN
       DECLARE @SELECTNAME VARCHAR(50)
       SET @SELECTNAME =  @NAME
       RETURN @SELECTNAME 
       
  
END

Inline Table Valued Function:

An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables.

Syntax:Creating Inline Table valued function:

---------SYNTAX CREATING INLINE FUNCTION-----
CREATE FUNCTION <FUNCTION_NAME>
(
 ------ACCEPT PARAMETER FUNCTION----
 @PARAMETER_NAME1 DATA_TYPES,
 @PARAMETER_NAME1 DATA_TYPES,
 ..........             .......
 .........              .......
 ........               .......
)
RETURNS TABLE 
AS
RETURN
(
  
   --------FUNCTION BODY-----
  ----- WRITE SQL QURY ------
  ------ RETURN STATEMENT-----
)

Example: Creating Inline Table valued function

USE [ArunSingh]
GO
/****** Object:  UserDefinedFunction [dbo].[TestTableValuedFunction]    Script Date: 07/11/2011 15:07:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:         <Arun Singh>
-- Create date: <11 JULY, 2011>
-- Description:   <Test Inline table valued function>
-- =============================================
CREATE FUNCTION [dbo].[TestTableValuedFunction] 
(     
      -- Add the parameters for the function here
      @name varchar(50),
      @password varchar(50)
)
RETURNS TABLE 
AS
RETURN
(
       SELECT * FROM userlogininfo WHERE UserName =@name AND Password = @password
)

Syntax: Altering Inline valued function

---------SYNTAX ALTERING INLINE FUNCTION-----
ALTER FUNCTION <FUNCTION_NAME>
(
 ------ACCEPT PARAMETER FUNCTION----
 @PARAMETER_NAME1 DATA_TYPES,
 @PARAMETER_NAME1 DATA_TYPES,
 ..........             .......
 .........              .......
 ........               .......
)
RETURNS TABLE 
AS
RETURN
(
  
   --------FUNCTION BODY-----
  ----- WRITE SQL QURY ------
  ------ RETURN STATEMENT-----
)

Example: Altering Inline valued function

USE [ArunSingh]
GO
/****** Object:  UserDefinedFunction [dbo].[TestTableValuedFunction]    Script Date: 07/11/2011 15:07:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:         <Arun singh>
-- Create date: <11 JULY, 2011>
-- Description:   <TEST Inline table valued function>
-- =============================================
ALTER FUNCTION [dbo].[TestTableValuedFunction] 
(     
      -- Add the parameters for the function here
      @name varchar(50),
      @password varchar(50)
)
RETURNS TABLE 
AS
RETURN
(
       SELECT * FROM userlogininfo WHERE UserName =@name AND Password = @password
)

Multi –Statement Valued Function:

A Multi-Statement Table-Value user-defined function returns a table and is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement.

Syntax: Creating Multi-Statement Valued Function

--------- DEMONSTRATION OF CREATING MULTI-VALUED FUNCTION----
CREATE FUNCTION  <FUNCTION_NAME>
(   
 -------PARAMETER OF FUNCTION------
@PARAMETER_NAME1  DATATYPES,
@PARAMETER_NAME2  DATATYPES,
..........              .......
..........              .......
)
RETURNS @VARRIABLE TABL
AS
  BEGIN
      --------FUNCTION BODY--
      ------SQL QUERY ----
      RETURN
  END

Example: Creating multi-Statement valued function

USE [ArunSingh]
GO
/****** Object:  UserDefinedFunction [dbo].[TestMultiValuedFunction]    Script Date: 07/11/2011 15:06:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:         <Arun Singh>
-- Create date: <11 JULY, 2011>
-- Description:   <TEST MULTI VALUED FUNCTION>
-- =============================================
CREATE FUNCTION [dbo].[TestMultiValuedFunction] 
(
      -- Add the parameters for the function here
      @password varchar(50)
)
RETURNS
@clientinfo TABLE
(
      -- Add the column definitions for the TABLE variable here
      [uid] int ,
      username varchar(50),
      emailid varchar(50)
      
)
AS
BEGIN
      -- Fill the table variable with the rows for your result set
      insert into @clientinfo(uid,username,emailid) select [Uid],UserName,EmailId  from userlogininfo where Password = @password
      RETURN
END

Syntax: Altering Multi-Valued Function

--------- DEMONSTRATION OF CREATING MULTI-VALUED FUNCTION----
ALTER FUNCTION  <FUNCTION_NAME>
(   
 -------PARAMETER OF FUNCTION------
@PARAMETER_NAME1  DATATYPES,
@PARAMETER_NAME2  DATATYPES,
..........              .......
..........              .......
)
RETURNS @VARRIABLE TABL
AS
  BEGIN
      --------FUNCTION BODY--
      ------SQL QUERY ----
      RETURN
  END

Example: Altering Multi-Valued Function

USE [ArunSingh]
GO
/****** Object:  UserDefinedFunction [dbo].[TestMultiValuedFunction]    Script Date: 07/11/2011 15:06:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:         <Arun Singh>
-- Create date: <11 JULY, 2011>
-- Description:   <TEST MULTI VALUED FUNCTION>
-- =============================================
ALTER FUNCTION [dbo].[TestMultiValuedFunction] 
(
      -- Add the parameters for the function here
      @password varchar(50)
)
RETURNS
@clientinfo TABLE
(
      -- Add the column definitions for the TABLE variable here
      [uid] int ,
      username varchar(50),
      emailid varchar(50)
      
)
AS
BEGIN
      -- Fill the table variable with the rows for your result set
      insert into @clientinfo(uid,username,emailid) select [Uid],UserName,EmailId  from userlogininfo where Password = @password
      RETURN
END

Drop SQL Function:

To drop SQL function uses the following syntax:

Syntax:

---- SYNTAX DEMONSTRATION TO DROP FUNCTION
DROPFUNCTION<FUNCTION_NAME>

Example: Dropping SQL function

DROPFUNCTION [DBO].TestMultiValuedFunction

Advantage of User Define Function:

User Define Function allows modular programming.
User Define Function allows faster execution.
User Define Function reduces network traffic.

articles