That’s the reason Microsoft has introduced this feature in SQL SERVER 2000

Introduction

SQL Server 2000 supports User-Defined Functions (UDFs) - one or more Transact-SQL statements that can be used to encapsulate code for reuse. User-defined functions cannot make permanent changes to the data or modify database tables. UDF can change only local objects for this UDF, such as local cursors or variables.         

There are three types of UDF in 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.

Difference Between Stored Procedure and User Defined Functions (UDF)

1. Procedure can return 0 or n values whereas functions can return a single value which is mandatory.
2. Procedure can have input and output parameter whereas functions can have only input type of parameters.
3. Procedure can be used for select command as well as for DML commands(Insert, Delete, Update) and DDL Commands (Create, Drop) while functions can be used for only select commands.
4. Functions can be called from Procedure while Procedure can not be called from Functions.
5. Exceptions can be handled by try-catch block in Procedure whereas try-catch block can not be used in functions.           

Cursors: An Overview

• A cursor is a set of rows together with a pointer that identifies a current row.
  
  
• In other word, Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, its like record set in the ASP and visual basic. and it is similar to data reader object.

• For example, you can use a single UPDATE statement to update many rows of data

• There are times when you want to loop through a series of rows a perform processing for each row. In this case you can use a cursor

Please note that

Ø  Cursors are the SLOWEST way to access data inside SQL Server.

Ø  They should only be used when you truly need to access one row at a time.

Ø  The only reason we can think of for that is to call a stored procedure on each row.

Ø  It was discovered that cursors are over thirty times slower than set based alternatives.

Create table person(id int,firstname varchar(20),lastname varchar(20))

insert into person values(10,'Manmomhan','singh')

insert into person values(20,'Abdhul','kalam')

insert into person values(30,'RamaRao','Nandamuri')

insert into person values(40,'Indhra','Gandhi')

insert into person values(50,'Rajashekar','Reddy')

Example for a cursor:

DECLARE @fName varchar(50), @lName varchar(50)—Declare variables

DECLARE cursorName CURSOR -- Declare cursor

--LOCAL SCROLL STATIC

FOR

Select firstName, lastName FROM person

OPEN cursorName -- open the cursor

FETCH NEXT FROM cursorName

INTO @fName, @lName

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT @fName + ' ' + @lName -- print the name

FETCH NEXT FROM cursorName

INTO @fName, @lName

END

CLOSE cursorName -- close the cursor

DEALLOCATE cursorName -- Deallocate the cursor

Example for cursors:

DECLARE @AuthorID char(11)

              

DECLARE c1 CURSOR

FOR

SELECT au_id

FROM authors

OPEN c1

FETCH NEXT FROM c1

INTO @AuthorID

WHILE @@FETCH_STATUS = 0

BEGIN

               PRINT @AuthorID

               FETCH NEXT FROM c1

               INTO @AuthorID

END

CLOSE c1

DEALLOCATE c1