sql server functions

SQL Server 2000

There are three types of UDF in Microsoft SQL Server 2000:

1. Scalar functions.
2. Inline table-valued functions.
3. Multistatement table-valued functions.

Scalar functions return a single data value (not a table) with RETURNS clause. Scalar functions can use all scalar data types, with exception of timestamp and user-defined data types. Inline table-valued functions return the result set of a single SELECT statement. Multistatement table-valued functions return a table, which was built with many TRANSACT-SQL statements.

User-defined functions can be invoked from a query like built-in functions such as OBJECT_ID, LEN, DATEDIFF, or can be executed through an EXECUTE statement like stored procedures.

The Structure of Stored Procedures

The Structure of Stored Procedures
(from SQL SERVER 2000 Stored Procedure Programming)

We will pause aminute to explain the structure of a stored procedure.
A stored procedure encapsulates a relatively simple Select statement for later use.
It returns a recordset containing values from the Selected column.

The recordset will contain only records that matched with the WHERE statement.

The code of a stored procedure consists of a header and a body.

The header of the stored procedure defines external attributes of the stored procedure—its name and a list of one or more parameters.

A example stored procedure has two parameters.
Parameter names must start with the @ character.
The developer must also define a datatype for each parameter.
The header must begin with the Create Procedure keyword and finish with the As keyword.

The body of the stored procedure contains the Transact-SQL statements to be executed when the stored procedure runs.

In this example, there is just one Select statement using the procedure parameters.