Home > DeveloperSection > Beginner > Function in SQL

Function in SQL


Database Database 
Ratings:
0 Comment(s)
 4336  View(s)
Rate this:

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

DROP FUNCTION <FUNCTION_NAME> 

Example: Dropping SQL function

DROP FUNCTION [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.

                                                               

 


Don't want to miss updates? Please click the below button!

Follow MindStick