SQL Functions

User-Defined Functions

Using stored procedures from Transact-SQL is not very flexible, because you can use them only with the EXECUTE or INSERT...EXECUTE statements. If you have a stored procedure that returns a single value, you cannot use this procedure inside an expression. If your procedure returns a result set, you cannot use this procedure in the FROM clause of any Transact-SQL statement.

Some user-defined functions are similar to views but they can be defined with more than one statement and they accept parameters. You can call user-defined functions in the same way you execute stored procedures, and you can use scalar user-defined functions as part of any expression anywhere in a Transact-SQL statement where an expression is valid. Furthermore, you can use a user-defined function that returns a table in the FROM clause of any Transact-SQL Data Manipulation Language (DML) statement.

However, user-defined functions have more useful benefits. They enable you to

· Use the result set returned by a stored procedure in the FROM clause of a query

· Join the results of two stored procedures, without using temporary tables to store intermediate results

· Use the result of a stored procedure in the IN operator

· Use a stored procedure as a subquery in the WHERE clause

· Create a view that cannot be solved with a single SELECT statement

· Create a view with parameters similar to the way Microsoft Access creates queries with parameters

· Extend the list of built-in functions with any financial function

· Create new mathematical functions for any special scientific database applications that you might require

You cannot change the definition of these built-in user-defined functions. In some cases, you cannot see their definition using the sp_help or sp_helptext system stored procedures, and you cannot script them. However, their definition is stored in the syscomments system table as any other user-defined function.

fn_CharIsWhiteSpace(@nchar) returns 1 if the variable @nchar only contains a space, a tab character, a newline character, or carriage return character; it returns 0 otherwise.

· fn_MSSharedVersion(@len_minorversion) returns the major and minor version number of SQL Server. @len_minorversion specifies how many digits to show for the minor version.

· fn_MsGenSqeScStr(@pstrin) returns the string @pstring, converting single quotes into two single quotes so that you are able to concatenate this string with other strings to execute a dynamic statement.

· fn_IsReplMergeAgent() returns 1 if the present process is executed by the Replication Merge Agent.

· fn_GetPersistedServerNameCaseVariation(@servername) returns the server name of the server specified in @servername with exactly the same case it uses in the sysservers system table, regardless of the case used to call this function.

· fn_ReplGetBinary8LoDWord(@binary8_value) takes the lower four bytes from the

@binary8_value binary variable and converts them into an integer value.

· fn_ReplPrepadBinary8(@varbinary8_value) converts the varbinary(8) value stored in @varbinary8_value into a fixed-length binary(8) value with leading zeros.

· fn_ReplMakeStringLiteral(@string) converts the value stored in the @string value into a UNICODE string, including quotes, such as N'Hello', to be used in dynamically constructed statements.

· fn_ReplQuoteName(@string) returns the value stored in @string en closed in square brackets. You can use this function in dynamic execution to select object names that contain spaces or keywords, such as [Order Details].

· fn_GenerateParameterPattern(@parameter) returns a pattern string you can use with the LIKE operator to test for strings containing any case variation of the value stored in @parameter, such as converting 'Hello' into '%[hH][eE][lL][lL][oO]%'. This is useful in case-sensitive servers, databases, or columns.

Types of User-Defined Functions According to Their Return Value

You can define a user-defined function with a single statement or with multiple statements, as you will see later in this chapter in the "Creating and Dropping User-Defined Functions" section.

According to their return value, user-defined functions can be divided into three groups:

· Scalar functions that return a single scalar value.

· Table-valued functions that return a full result set, similar to a table.

· Inline user-defined functions are a special case of table-valued user-defined functions, but they are limited to a single SELECT statement.

Scalar Functions

Scalar user-defined functions return a single value, and they can be used wherever an expression is accepted, such as

· In the SELECT clause of a SELECT statement, as a part of an expression or as an individual column

· In the SET clause of an UPDATE statement, as a value to insert into a field of the table being updated

· In the FROM clause of any DML statement (SELECT, UPDATE, INSERT, DELETE), as a single column , single-row result set–derived table

· In the FROM clause of any DML statement, as part of the joining conditions in the ON clause

· In the WHERE clause or HAVING clause of any DML statement

· In the GROUP BY clause, as part of any grouping condition

· In the ORDER BY clause of any statement, as sorting criteria

· As a DEFAULT value for a column

· Inside a CHECK CONSTRAINT definition

· Inside a CASE expression

· In a PRINT statement, if the user-defined function returns a string

· As part of the condition of IF or WHILE statements

· As part of the definition of a compute column

· As a parameter to call a stored procedure or another user-defined function

· As a return value of a stored procedure, if the user-defined function returns an integer value

· As a return value of another scalar user-defined function

You can identify several parts in the CREATE FUNCTION syntax for a scalar user-defined function:

· CREATE FUNCTION ownername function name, where you can specify the owner of the function and the name of the function.

· () is an empty list of parameters.

· RETURNS datatype, where you define the data type for the returned value as the function's result.

· AS BEGIN...END to mark the function definition body.

· The function definition body.

USE Northwind

GO

-- Returns the maximum ProductID from Products

CREATE FUNCTION dbo.MaxProductID

()

RETURNS int

AS

BEGIN

RETURN (

SELECT MAX(ProductID)

FROM dbo.Products

)

END

GO

CREATE FUNCTION dbo.PRand

()

RETURNS float

AS

BEGIN

DECLARE @dt datetime

DECLARE @dts varchar(3)

DECLARE @t1 float

DECLARE @t2 float

DECLARE @r float

-- Obtain the time of latest executed statement

SET @dt = ( SELECT MAX(last_batch) FROM master.dbo.sysprocesses )

Using Parameters in User-Defined Functions

You can create user-defined functions to expand the collection of system-supplied functions, using parameters. You must define a parameter list in the CREATE FUNCTION statement, after the function name.

USE Northwind

GO

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

-- Generic function to compute the total price of a sale

-- from the quantity, unitprice and discount

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

CREATE FUNCTION dbo.TotalPrice

(@Quantity float, @UnitPrice money, @Discount float = 0.0)

RETURNS money

AS

BEGIN

RETURN (@Quantity * @UnitPrice * (1.0 - @Discount))

END

GO

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

-- Compute the future value of an annuity based on

-- periodic fixed payments with a fixed interest rate

-- Parameters:

-- @rate: interest rate between payments

-- @nper: number of payments

-- @pmt: payment to be made on every period

-- @pv: present value. Default to 0.0

-- @type: 0 if the payment is made at the end of each period (default)

-- 1 if the payment is made at the beginning of each period

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

CREATE FUNCTION dbo.fn_FV

(@rate float, @nper int, @pmt money, @pv money = 0.0, @type bit = 0)

RETURNS money

AS

BEGIN

DECLARE @fv money

IF @rate = 0

SET @fv = @pv + @pmt * @nper

ELSE

SET @fv = @pv * POWER(1 + @rate, @nper) +

@pmt * (((POWER(1 + @rate, @nper + @type) - 1) / @rate) - @type)

RETURN (-@fv)

END

GO

Rules

Rules are a backward-compatibility feature that perform some of the same functions as CHECK constraints. CHECK constraints are the preferred, standard way to restrict the values in a column. CHECK constraints are also more concise than rules; there can only be one rule applied to a column, but multiple CHECK constraints can be applied. CHECK constraints are specified as part of the CREATE TABLE statement, while rules are created as separate objects and then bound to the column.

This example creates a rule that performs the same function as the CHECK constraint example in the preceding topic. The CHECK constraint is the preferred method to use in Microsoft® SQL Server™ 2000.

CREATE RULE id_chk AS @id BETWEEN 0 and 10000

GO

CREATE TABLE cust_sample

   (

   cust_id            int

   PRIMARY KEY,

   cust_name         char(50),

   cust_address         char(50),

   cust_credit_limit   money,

   )

GO

sp_bindrule id_chk, 'cust_sample.cust_id'

GO

PL SQL Examples

****************************************************************************