Overview

• A function lets you encapsulate reusable logic and build software that you can “compose”.
• Functions hide the steps and the complexity from other code. In certain respects, SQL Server’s functions are fundamentally different from functions in other programming environments.
• In procedural programming, the piece of functionality that most programmers call a function should really be called a subroutine, which is more like a miniature program.
• These subroutines can go about changing data, introducing side effects, and generally misbehaving as much as they like.

What are the Microsoft SQL database functions?

CREATE FUNCTION (Transact-SQL) - SQL Server

• In SQL Server, functions adhere much more closely to their mathematical definition of mapping a set of inputs to a set of outputs.
• SQL Server’s functions accept parameters, perform some sort of action, and return a result.
• They do all of this with no side effects.
• Nevertheless, in the same way as subroutines, SQL Server functions can hide complexity from users and turn a complex piece of code into a re-usable commodity.

IF OBJECT_ID (N'dbo.FN_MyFunctionName') IS NOT NULL
   DROP FUNCTION dbo.FN_MyFunctionName
GO

CREATE FUNCTION MyFunctionName 
(
	@p1 INT, 
	@p2 CHAR(1))
RETURNS TABLE
AS
RETURN SELECT @p1 AS 'Number', @p2 AS 'Character'
GO

-- Testing:
SELECT * FROM dbo.MyFunctionName(5, 'a');

IF OBJECT_ID (N'dbo.FN_MyFunctionName') IS NOT NULL
   DROP FUNCTION dbo.FN_MyFunctionName
GO

CREATE FUNCTION FN_FunctionName (@p1 INT)
RETURNS INT
AS
BEGIN
	RETURN (@p1 + 10);
END
GO

-- Testing:
SELECT dbo.FN_FunctionName(5) AS Test;

UDF Definition Rules

• The body of the function must be enclosed in a BEGIN/END block if it has more than one statement.
• Statements with side effects (insert/update/delete) and temporary tables may not be used.
  • You can, however, use table variables.
  • Table variables are allowed in UDFs because they are created as variables, not through DDL. DDL is viewed as producing a side effect and is not allowed.
• TRY/CATCH statements are not allowed since CATCH can have the side effect of masking the error state of a given function.
• You cannot execute a command with exec or sp_executesql nor can execute a stored procedure in a function.
  • Therefore, dynamic SQL is not allowed within functions.

UDF Drawbacks

• SQL Server has no optimization whereby it can compile UDF as inline code.
  • Therefore, it will simply call it once for every row.
• We won’t see the true cost of the function when we’re looking at execution plans.

Scalar Functions