2007-12-11

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.

2007-12-10

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.