Microsoft® SQL Server® 2012 Bible

456

Part IV: Programming with T-SQL

Creating a Scalar Function

User-defi ned functions are created, altered, or dropped with the same DDL commands used

for other objects; although the syntax is slightly different to allow for the return value:

CREATE FUNCTION FunctionName (InputParameters)

RETURNS DataType

AS

BEGIN;

Code;

RETURN Expression;

END;

The input parameters include a data-type defi nition and may optionally include a default

value similar to stored procedure parameters (parameter = default). Function param-

eters differ from stored procedure parameters in that even if the default is wanted, the

parameter is still required to call the function. Parameters with defaults don’t become

optional parameters. To request the default when calling the function, pass the keyword

DEFAULT to the function.

The following user-defi ned scalar function performs a simple mathematical function. The

second parameter includes a default value:

CREATE FUNCTION dbo.ufnCalculateQuotient

(@Numerator numeric(5,2),

@Denominator numeric(5,2)= 1.0)

RETURNS numeric(5,2)

AS

BEGIN;

RETURN @Numerator/@Denominator;

END;

GO

SELECT dbo.ufnCalculateQuotient(12.1,7.45),

dbo.ufnCalculateQuotient (7.0,DEFAULT);

Result:

----------- -----------

1.62 7.00

For a more complex scalar user-defi ned function, the ufnGetOrderTotalByProduct

function created in the AdventureWorks2012 sample database returns the total number

of orders for each product. Because the task returns a single value, calculating the total

number of orders is a prime candidate for a scalar user-defi ned function. As a function,

it can be plugged into any query, whereas a stored procedure is more diffi cult to use as a

building block in other code.

USE AdventureWorks2012;

c18.indd 456c18.indd 456 7/30/2012 5:41:46 PM7/30/2012 5:41:46 PM

http://www.it-ebooks.info