T-SQL Function

Types of functions

Scalar valued function

Creating function in SSMS

Step-1

Creating function in ssms Step 1

Step-2

Creating function in ssms Step 2

Step-3

Creating function in ssms Step 3

Step-4

Creating function in ssms 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

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)