Creating Functions, Stored Procedures, and Triggers in VS .NET

Generation PDF417 in VS .NET Creating Functions, Stored Procedures, and Triggers

9

SQL Server provides a set of built-in functions that you can plug into your applications to provide common functionality. For example, you can use the GETDATE() function to return the current system date and time in the SQL Server 2005 standard internal format for datetime values. Although SQL Server provides a nice variety of built-in functions, you can also build your own functions to encapsulate pieces of commonly used code, letting you develop the code once and reuse it across applications. Typically, you create UDFs to encapsulate complex pieces of code so that the implementation is seamless to applications. In this lesson, you see how to implement two types of UDFs: scalar functions, which return a scalar value result, and table-valued functions, which return a result in the form of a table. You also learn how to identify deterministic and nondeterministic functions, which affect whether you can define indexes on the results the functions return.

After this lesson, you will be able to:

Scalar functions accept 0 or more input parameters and return a single scalar value. You use the CREATE FUNCTION Transact-SQL statement to create a function. The general syntax of the statement is as follows:

CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS return_data_type [ WITH <function_option> [ ,...n ] ] [ AS ] BEGIN function_body RETURN scalar_expression END [ ; ] <function_option>::= { [ ENCRYPTION ] | [ SCHEMABINDING ] | [ EXECUTE_AS_Clause ] }

Each function must have a unique name that conforms to the rules for object identifiers. Although you do not have to define input parameters for functions, it is rare for UDFs to not have input parameters. So you will usually specify one or more input parameters along with the data type of each parameter. You use the statement s RETURNS clause to specify the data type of the scalar value that the function will return. There are several options that you can specify for this clause. When you specify ENCRYPTION, SQL Server encrypts the definition of the function when it is stored. The SCHEMABINDING option prevents any objects that the function depends on from being dropped. The EXECUTE AS option specifies the security context of the function. The body of the function is delimited by a BEGIN END construct, which must include a RETURN clause that is used to output the value that the function calculates. The body of the function is obviously where all the interesting work happens. Although you can execute virtually any valid batch of code within a function, functions do have some restrictions. The most significant restriction is that you cannot use a function to change the state of any object in a database or the database itself. Therefore, you cannot insert, update, or delete data in tables, nor can you create, alter, or drop objects in the database. However, you can create one or more table variables and issue INSERT, UPDATE, and DELETE statements against the table variable.

A table variable is a special type of variable used to temporarily store a set of rows that will be returned as the result of a table-valued function. For information about table variables, see the SQL Server 2005 Books Online topic Table (Transact-SQL).

Because scalar functions return a single value, you normally use them in the column list of a SELECT statement and can use them in the WHERE clause as well. The following example shows you how to define a scalar-valued function that returns the stock level for a given product ID:

CREATE FUNCTION [dbo].[ufnGetStock](@ProductID [int]) RETURNS [int] AS -- Returns the stock level for the product. This function is used -- internally only. BEGIN DECLARE @ret int;