T-SQL Function

• Function is a database object in SQL Server. Basically, it is a set of SQL statements that accept only input parameters, perform actions and return the result. Function can return an only single value or a table
• In T-SQL, a function is considered an object
• Functions compile every time
• Functions must return a value or result
• Functions only work with input parameters
• Try and catch statements are not used in functions
• From https://www.janbasktraining.com/blog/different-types-sql-server-sql-database-functions/
  • Supports only SELECT or read data. We cannot use function to Insert/Update/Delete records in the database table
  • We can use variables and cursors
  • It accepts only input parameters, do not have output parameters
  • We can call User Defined Functions as part of Select/Insert/Update/Delete
  • It can be nested up to 32 level
  • UDF can have up to 1023 input parameters
  • It can’t return XML data type
  • It doesn’t support Exception handling
• A function is a set of SQL statements that perform a specific task. Functions foster code reusability. If you have to repeatedly write large SQL scripts to perform the same task, you can create a function that performs that task. Next time instead of rewriting the SQL, you can simply call that function
• Read more: https://www.red-gate.com/simple-talk/sql/t-sql-programming/sql-server-functions-the-basics/

Types of functions

• User Defined function: User defined functions are create by a user
• System Defined Function: System functions are built in database functions
• See: https://www.dotnettricks.com/learn/sqlserver/different-types-of-sql-server-functions

Scalar valued function

• Scalar function takes one or more parameters and returns a single value
• any function that returns a single piece of data is called a Scalar-valued Function
• Scalar- valued Function can be used as part of the SELECT statement, part of the where clause and or part of the order by clause.

Creating function in SSMS

Step-1

Step-2

Step-3

Step-4

Creating function using T-SQL

CREATE FUNCTION getEmail(@PersonName AS varchar(250))
RETURNS varchar(250)
AS
BEGIN
	DECLARE @emailAddress AS varchar(250)
	
	SELECT @emailAddress = EmailAddress
	FROM [Application].[People]
	WHERE [FullName] = @PersonName

	RETURN @emailAddress
END

See: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql

Example - a custom function to convert degrees celsius into degrees fahrenheit

-- create a custom function to convert degrees celsius into degrees fahrenheit
CREATE FUNCTION Warehouse.ToFahrenheit (@Celsius decimal(10,2))
RETURNS decimal(10,2)
AS
BEGIN
	DECLARE @Fahrenheit decimal(10,2);
	SET @Fahrenheit = (@Celsius * 1.8 + 32);
	RETURN @Fahrenheit
END;

Table valued function

• Table-valued Function. As the name implies, the results of this function will be an entire table
• a view would do some similar things. But, remember you can’t pass a parameter to a view. So the strength of Tabled-valued Functions is that we can pass one or more parameters to it and get different results based on those parameters. Other than that they function largely like tables or views

Creating a table valued function

CREATE FUNCTION dbo.AuthorsByStatus(@isActive int)
RETURNS @AuthorsTable TABLE 
(	[FirstName] [varchar](50) NOT NULL,
	[LastName] [varchar](50) NOT NULL,
	[PhoneNumber] [varchar](50) NULL,
	[Address] [varchar](50) NULL,
	[City] [varchar](50) NULL,
	[State] [varchar](50) NULL,
	[Zip] [varchar](50) NULL,
	[Active] [int] NULL
)  
AS 
BEGIN

	INSERT INTO @AuthorsTable
	    SELECT * 
	    FROM AUTHORS
	    WHERE Active = @isActive

	RETURN;
END

Usage

SELECT * FROM [dbo].[AuthorsByStatus](1)