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 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
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
It is also possible to internally declare a table structure with its colums, that we could fill in with the data of a
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
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:
|Mavin||Petitt||2336 cottonwood Lane||123456789|
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');
|Janice||Haynes||4822 Radio Park Drive||111222333|
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.