blog

Home / DeveloperSection / Blogs / SQL Function

SQL Function

Anonymous User 7044 05-Jul-2011

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. Data transformation and reference value retrieval are common uses for functions. 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.

  Creates a user-defined function, which is a saved Transact-SQL routine that returns a value. User-defined functions cannot be used to perform a set of actions that modify the global database state. User-defined functions, like system functions, can be invoked from a query. They also can be executed through an EXECUTE statement like stored procedures.

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

1.     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 to Create Scalar function:
Create Function (function Name)
(             // parameter of function   )
Returns @scalar data types value
With
As
Begin
// function body
// return scalar expression
End

Example: Creating scalar function:

CREATE FUNCTION TestScalarFunction
(
 @NAME VARCHAR(50)
)
  RETURNS varchar(50)
  AS
 BEGIN
  DECLARE @SELECTNAME VARCHAR(50)
  SET @SELECTNAME =  @NAME
   RETURN @SELECTNAME
 END
 GO

OutPut: //select  dbo.TestScalarFunction('Arun')

 Arun  // this desired output

2.     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 to create Inline Table valued function:
Create Function [Function Name]
(// declare parameter of function)
Returns table
As
Begin
// function body
End
  Example: Creating Inline valued function:
CREATE FUNCTION 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
)
GO

Output: // Select * from dbo. TestTableValuedFunction(‘Arunsingh’,’123456’)

// this desired output:

102         Arunsingh           Vashundhra Ghaziabad arunbaswar@gmail.com               123456  7607185995                Admin   1989-02-28

3.     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. Also, the ability to pass parameters into a T-SQL select command or a group of them gives us the capability to in essence create a parameterized, non-updateable view of the data in the underlying tables. Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, I can use it in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets.

Syntax to create Multi-Statement Valued Function:
Create Function [function name]
(   // parameter of function)
Returns @variable table
 With
   AS
    Begin
// Function Body
  End
 Example: Creating multi-Statement valued function:


CREATE FUNCTION 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
GO

// select * from TestMultiValuedFunction(123456)

// Desired Output : 102 Arunsingh           arunbaswar@gmail.com                                                                                 

Advantage of User Define Function:

·         User Define Function allows modular programming.

·         User Define Function allows faster execution.

·         User Define Function reduces network traffic.


Updated 18-Sep-2014
I am a content writter !

Leave Comment

Comments

Liked By