--- title: "SQL Function" description: "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 wo" author: "Anonymous User" published: 2011-07-05 updated: 2014-09-18 canonical: https://www.mindstick.com/blog/199/sql-function category: "database" tags: ["database"] reading_time: 4 minutes --- # 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. Data transformation and reference value retrieval are common uses for functions. There are two types of [function in SQL](https://www.mindstick.com/articles/12998/what-is-scalar-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](https://www.mindstick.com/interview/2294/what-is-the-difference-between-a-user-defined-function-and-a-stored-procedure), which is a saved Transact-SQL routine that returns a value. User-[defined functions](https://www.mindstick.com/interview/23510/what-are-all-types-of-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](https://www.mindstick.com/forum/540/using-stored-procedures-with-entity-framework-in-an-asp-dot-net-application). There are three types of user define [scalar functions](https://www.mindstick.com/interview/23537/what-are-aggregate-and-scalar-functions) which are namely given as follows: 1. [Scalar Function](https://www.mindstick.com/forum/34598/how-to-create-scalar-function-in-sql): 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](https://www.mindstick.com/articles/12386/5-up-and-coming-programming-languages-to-know-about). 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 valueWithAsBegin// function body// return scalar expressionEnd ``` 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 tableAsBegin// function bodyEnd ``` ##### Example: Creating Inline valued function: ``` CREATE FUNCTION TestTableValuedFunction(-- Add the parameters for the function here@name varchar(50),@password varchar(50))RETURNS TABLEASRETURN(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](https://www.mindstick.com/forum/159471/how-does-c-plus-plus-support-multiple-inheritance-and-what-are-potential-issues-associated-with-it) T-SQL statements to build the final result where the view is limited to a single [SELECT statement](https://www.mindstick.com/forum/160076/explain-the-sql-select-statement-and-how-it-s-used-to-search-for-data-in-a-database). 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](https://www.mindstick.com/articles/803/using-stored-procedure-in-asp-dot-net) 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))ASBEGIN-- Fill the table variable with the rows for your result setinsert into @clientinfo(uid,username,emailid) select [Uid],UserName,EmailId  from userlogininfo where Password = @passwordRETURNENDGO ``` // 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. --- Original Source: https://www.mindstick.com/blog/199/sql-function Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.