My shared items

 

Friday, October 5, 2007

"CURSOR" in SQL Server

Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. For example, you can use cursor to include a list of all user databases and make multiple operations against each database by passing each database name as a variable.

Cursors are the SLOWEST way to access data inside SQL Server

Example:

USE Northwind
DECLARE @EmployeeID varchar(10), @Counter int

CREATE TABLE #tempTbl
(
EmployeeID int,
OrderCount int
)

SET @Counter = 0
DECLARE Employees CURSOR
FOR
SELECT DISTINCT
(EmployeeID) FROM Employees

OPEN Employees
BEGIN
WHILE( @Counter < @@CURSOR_ROWS)

BEGIN

FETCH NEXT FROM Employees INTO @EmployeeID

INSERT INTO #tempTbl(EmployeeID, OrderCount)
SELECT @EmployeeID AS EmployeeID, COUNT(O.EmployeeID) AS OrderCount FROM Employees E, Orders O WHERE O.EmployeeID = @EmployeeID AND E.EmployeeID = O.EmployeeID

SET @Counter = @Counter + 1
END

SELECT * FROM #tempTbl

DROP TABLE #tempTbl

CLOSE Employees

DEALLOCATE Employees

END


Output:

EmployeeID OrderCount

3 127
4 156
8 104
1 123
2 96
6 67
7 72
5 42
9 43