Table functions in SQL

Table functions are functions that return a set of results in table. Next we will see, with examples, the statements to create, execute, modify and eliminate user functions.

CREATE FUNCTION

The CREATE FUNCTION statement allow us to create new functions in the database. In the following example we will create a new table funciton called returnCustomer that will return a result in table form with Customers information.

CREATE FUNCTION dbo.returnCustomer (@id INT)
RETURNS TABLE
    AS
    RETURN (
        SELECT [name], 
               lastName, 
               [address], 
               bankAccount
        FROM Customers
        WHERE customerId = @id);

GO

This function receives an id as an input parameter, and filters the Clients table to return a record with the fields specified in the internal SELECT.

It is also possible to internally declare a table structure with its colums, that we could fill in with the data of a SELECT through INSERT.

CREATE FUNCTION dbo.returnAlternativeCustomer (@id INT)
RETURNS @customerResult TABLE
(
	[name]      VARCHAR(50),
	lastName    VARCHAR (255),
	[address]   VARCHAR (255),
	bankAccount INT
)
    AS
	BEGIN
		INSERT @customerResult
		SELECT [name],
               lastName,
               [address],
               bankAccount
		FROM Customers
		WHERE customerId=@id
		RETURN
	END

GO

EXECUTE

We can execute functions directly from the SELECT statement. To do this, we just write the function name after the SELECT statement. If the function receives input parameters, we should introduce the respective value between the function parenthesis, respecting the order in wich they were declare and the datatype they can support.

SELECT * FROM returnCustomer(2);

The result of the execution in the form of a table would be:

ALTER FUNCTION

ALTER FUNCTION defines the statement to modify user functions that have already been declared. The modifications may affect the arguments or input parameters, the type of data returned in the RETURNS or the internal logic.

We proceed to modify the returnCustomer function so that it receives instead of an id, the first and last name as input parameters:

ALTER FUNCTION dbo.returnCustomer (@name VARCHAR (50), @lastName VARCHAR (50))
RETURNS TABLE
    AS
    RETURN (
        SELECT [name], 
        lastName, 
        [address], 
        bankAccount
        FROM Customers
        WHERE [name] = @name
        AND lastName = @lastName);

GO

SELECT * FROM returnCustomer('Janice', 'Haynes');

DROP FUNCTION

The DROP FUNCTION statement deletes the specified function from the system.

To delete the function:

DROP FUNCTION IF EXISTS dbo.returnCustomer;

As with other types of DROP, it is recommended to always use the IF EXISTS clause.