Some operations are difficult or even impossible to obtain with an expression, or the operation could become too complex to achieve. The alternative is to create a function that would take care of performing the operation and supplying the result to the table. Of course, as you may know already, a function is its own object. This means that, after creating it, to use its result in a table, you must call it. For example, you can create a function that returns a value, then call that function and assign its returned value to a column. You can create your own function and use it, or you can use one of the built-in functions of Transact-SQL.
In order to involve a function with your data entry, you must have one. You can create your own function using the techniques we learned already. To make the function easily accessible, you should create it as part of the database that would use it. Here is an example:
-- ============================================= -- Author: FunctionX -- Create date: Saturday 22 December 2007 -- Description: Used to calculate the greatest common divisor -- ============================================= CREATE FUNCTION GCD ( @a int, @b int ) RETURNS int AS BEGIN DECLARE @Remainder int; WHILE @b <> 0 BEGIN SET @Remainder = @a % @b; SET @a = @b; SET @b = @Remainder; } RETURN @a }
When calling the function, follow the normal rules. Here are examples:
INSERT INTO Calculations VALUES(345, 135, dbo.GCD(345, 135)); GO INSERT INTO Calculations VALUES(40, 6, dbo.GCD(40, 6)); GO INSERT INTO Calculations VALUES(16, 28, dbo.GCD(16, 28)); GO
You can use one of the built-in functions of Transact-SQL. Probably the best way to be familiar with the built-in functions is to check the online documentation to find out if the assignment you want to perform is already created. Using a built-in functions would spare you the trouble of creating your own function. For example, imagine you have a database named AutoRepairShop and imagine it has a table used to create repair orders for customers:
CREATE TABLE RepairOrders ( RepairID int Identity(1,1) NOT NULL, CustomerName varchar(50), CustomerPhone varchar(20), RepairDate DateTime ); GO
When performing data entry for this table, you can let the user enter the customer name and phone number. On the other hand, you can assist the user by programmatically entering the current date. To do this, you would call the GETDATE() function. Here are examples:
INSERT INTO RepairOrders(CustomerName, CustomerPhone, RepairDate) VALUES('Annette Berceau', '301-988-4615', GETDATE()); GO INSERT INTO RepairOrders(CustomerPhone, CustomerName, RepairDate) VALUES('(240) 601-3795', 'Paulino Santiago', GETDATE()); GO INSERT INTO RepairOrders(CustomerName, RepairDate, CustomerPhone) VALUES('Alicia Katts', GETDATE(), '(301) 527-3095'); GO INSERT INTO RepairOrders(RepairDate, CustomerPhone, CustomerName) VALUES(GETDATE(), '703-927-4002', 'Bertrand Nguyen'); GO
You can also involve the function in an operation, then use the result as the value to assign to a field. You can also call a function that takes one or more arguments; make sure you respect the rules of passing an argument to a function when calling it.
If none of the Transact-SQL built-in functions satisfies your requirements, you can create your own.