 | |||
|
|
||
| |||
| |||
| |||
| ||||||||
|
|
|||||||
|
|
|
|
| Tutorial: |
|
||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|
|||||||||||||||||||||||||||||||||||||||||||||||||
Relational databases are designed to work with sets of data. In fact, the purpose of the Select statement, as the most important statement in SQL, is to define a set of records. In contrast, end-user applications display information to the user record by record (or maybe in small batches). To close the gap between these conflicting requirements, RDBMS architects have invented a new class of programming constructs—cursors. Many types of cursors are implemented in various environments using different syntax, but all cursors work in a similar fashion:
1. A cursor first has to be defined and its features have to be set.
Cursors can be used on both server and client sides. SQL Server and the APIs for accessing database information (OLE DB, ODBC, DB-Library) all include sets of functions for processing cursors.
The major difference between Transact-SQL cursors and other types of cursors is their purpose. Transact-SQL cursors are used from stored procedures, batches, functions, or triggers to repeat custom processing for each row of the cursor. Other kinds of cursors are designed to access database information from the client application. We will review only Transact-SQL cursors.
Transact-SQL Cursors
1. Use the Declare Cursor statement to create the cursor based on the Select statement.
NOTE It is best to show this process through an example. We will rewrite the stored procedure that we used to illustrate the use of the While statement. The purpose of this stored procedure is to collect the properties of a specified asset and return them in delimited format (Property = Value Unit;). The final result should look like this:
CPU=Pentium II;RAM=64 MB;HDD=6.4 GB;Resolution=1024x768;Weight=2 kg; Here is the code for the new instance of the stored procedure:
Alter Procedure dbo.ap_InventoryProperties_Get_Cursor
/********************************************************************
Return comma-delimited list of properties that are describing asset.
Property = Value unit;Property = Value unit;Property = Value unit;...
Output: @chvProperties
Return: n/a
Name Date Description
Created by: Dejan Sunderic 2005.04.18
Modified by:
test:
declare @p varchar(max)
exec dbo.ap_InventoryProperties_Get_Cursor 5, @p OUTPUT, 1
select @p
********************************************************************/
(
@intInventoryId int,
@chvProperties varchar(max) OUTPUT,
@debug int = 0
)
As
declare @intCountProperties int,
@intCounter int,
@chvProperty varchar(50),
@chvValue varchar(50),
@chvUnit varchar(50)
Set @chvProperties = ''
Declare @CrsrVar Cursor
Set @CrsrVar = Cursor For
select Property, Value, Unit
from dbo.InventoryProperty InventoryProperty
inner join dbo.Property Property
on InventoryProperty.PropertyId = Property.PropertyId
where InventoryProperty.InventoryId = @intInventoryId
Open @CrsrVar
Fetch Next From @CrsrVar
Into @chvProperty, @chvValue, @chvUnit
While (@@FETCH_STATUS = 0)
Begin
Set @chvUnit = Coalesce(@chvUnit, '')
If @debug <> 0
Select @chvProperty Property,
@chvValue [Value],
@chvUnit [Unit]
-- assemble list
Set @chvProperties = @chvProperties + @chvProperty + '='
+ @chvValue + ' ' + @chvUnit + '; '
If @debug <> 0
Select @chvProperties chvProperties
Fetch Next From @CrsrVar
Into @chvProperty, @chvValue, @chvUnit
End
Close @CrsrVar
Deallocate @CrsrVar
Return 0
The stored procedure will first declare a cursor:
Declare @CrsrVar Cursor The cursor will then be associated with the collection of properties related to the specified asset:
Set @CrsrVar = Cursor For
Select Property, Value, Unit
From dbo.InventoryProperty InventoryProperty
inner join dbo.Property Property
On InventoryProperty.PropertyId = Property.PropertyId
Where InventoryProperty.InventoryId = @intInventoryId
Before it can be used, the cursor needs to be opened:
Open @CrsrVar The content of the first record can then be fetched into local variables:
Fetch Next From @CrsrVar Into @chvProperty, @chvValue, @chvUnit
While (@@FETCH_STATUS = 0) After the values from the first record are processed, we read the next record:
Fetch Next From @CrsrVar
Into @chvProperty, @chvValue, @chvUnit
Once all records have been read, the value of @@fetch_status is set to –1 and we exit the loop. We need to close and deallocate the cursor and finish the stored procedure:
Close @CrsrVar Deallocate @CrsrVar Now, let's save and execute this stored procedure:
Declare @chvRes varchar(max) Exec ap_InventoryProperties_Get_Cursor 5, @chvRes OUTPUT Select @chvRes Properties SQL Server will return the following:
Properties -------------------------------------------------------------------- CPU=Pentium II ; RAM=64 MB; HDD=6.4 GB; Resolution=1024x768 ; Weight =2 kg; Clock=366 MHz;
 The previous tip is from "15 tips in 15 minutes: Basic Transact-SQL Programming Constructs," excerpted from Chapter 3, of the book "Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET" by Dejan Sunderic, courtesy of McGraw-Hill Publishing.
|
|
|
||||||||||||||||||||||||||||||||||||||||||||||||
Home: 
|
|
 |  |  | |  | |  | |  | |  | |  | |  | |  | |
|
|
About Us | Contact Us | For Advertisers | For Business Partners | Site Index | RSS |
|
|
||
|
|||||||
