1 Kursoret

1.1 Perse sherbejne kursoret

Operacionet ne nje database relacionale veprojne mbi nje te gjithe rreshtat qe plotesojne kriteret.PSH: Rreshtat qe kthehen nga nje komande SELECT konsistojne ne te gjithe rreshtat qe plotesojne kushtet ne klauzolen WHERE. I gjithe ky grup rreshtash qe kthehet nga kjo komande njihet si “Result set”. Aplikacionet , sidomos aplikacionet online nuk mund te jene efektive nqs e perpunojne te gjithe Result set njeheresh. Keto aplikacione kane nevoje per mekanizema qe t’I mundesojne perpunimin e nje blloku te vogel rreshtash. Kursoret jane elemte shtese ten je result set qe mundesojne pikerisht perpunimin e tij ne blloqe me te vogla.

Me ane te kursoreve ne jemi ne gjendje qe:

· Te lejojme pozicionimin ne nje rresht specific te Result set.

· Te aksesojme nje rresht ose nje bllok rreshtash duke filluar nga pozicioni aktual ne Result set.

· Ofrohet mundesia per te modifikuar te dhenat e rreshtave ne pozicionin korrent ne result set.

· Eshte e mundur te ofrohen nivele te ndryshme te aksesimit te dhenave qe jane duke u ndryshuar nga perdorues te tjere.

1.2 Krijimi I nje kursori

Microsoft SQL Server 2008 mundeson krijimin e nje kursori me ane te dy menyrave:

· Transact-SQL

· Nepermjet gjuheve te programimit(API).

Nje aplikacion nuk duhet qe t’I perdori te dyja menyrat per krijimin e nje kursori. Nqs nuk eshte krijuar asnje lloj kursori SQL Server automatikisht do te ktheje te gjithe result set, te njhur si default result set.

1.3 Proceset e Kursoreve

Kursoret qe krijohen me ane te Transact-SQL dhe kursoret qe krijohen me ane te gjuheve te programimit(API) kane sintakse te ndryshme, por hapat e pergjithshem jane te njejte dhe jane si me poshte:

1. Bashkengjitet kursorit shprehja Transact-SQL e result set dhe percaktohen karakteristikat e kursorit, psh nqs rreshtat e kursorit mund te modifikohen(update).

2. Ekzekutohet shperhja Transact-SQL per te populluar kursorin.

3. Merren ne kursor rreshtat qe nevojiten. Operacioni per marrjen e nje rreshti apo nje blloku rreshtash ne kursor quhet “fetch”.Ekzekutimi I nje serie fetch per terheqjen e rreshtave ne te dy drejtimet(para, mbrapa) ne nje kursor quhet “scrolling”

4. Sipas nevojes mund te behen modifikime ne rreshtat e nje kursori ne pozicionin korrent(update ose delete).

Mbyll kursorin

When a request is submitted for execution, Microsoft SQL Server sends result sets back to clients in the following way:

SQL Server receives a network packet from the client containing the Transact-SQL statement or batch of Transact-SQL statements to be executed.

SQL Server compiles and executes the statement or batch.

SQL Server begins putting the rows of the result set, or multiple result sets from a batch or stored procedure, in network packets and sending them to the client. SQL Server puts as many result set rows as possible in each packet.

The packets containing the result set rows are cached in the network buffers of the client. As the client application fetches the rows, the ODBC driver or the OLE DB provider pulls the rows from the network buffers and transfers the data to the client application. The client retrieves the results one row at a time in a forward direction.

This mode of returning results is known as a default result set.

SQL Server provides the ability to have multiple active result sets under a single connection, which is commonly referred as MARS. This capability must be enabled explicitly per connection. It is only available in the SQL Server Native Client ODBC driver, the SQL Server Native Client OLEDB provider, and the .NET Framework 2.0 SqlClient Managed Provider.

A connection string attribute or session property is used to enable MARS for a particular session:

For ODBC, SQL_COPT_SS_MARS_ENABLED should be set to SQL_MARS_ENABLED_YES

For OLEDB, SSPROP_INIT_MARSCONNECTION should be set to VARIANT_TRUE

For SqlClient managed provider, the connection string attribute MultipleActiveResultSets must be set to true.

MARS is only available for client providers that are connected to SQL Server. MARS is not available in any combination of earlier releases of the client providers or SQL Server.

In earlier releases of SQL Server or in sessions without MARS enabled, the client cannot send any other requests on a particular connection until the application has either processed all the rows returned by SQL Server or sent SQL Server a request to cancel the rest of the results. No updates can be done on the connection until all the results have been processed.

Default Result Set is the type of result set processing SQL Server uses when no cursors have been requested. This happens when the following conditions are met:

The application does not use the DECLARE CURSOR statement to request a Transact-SQL server cursor. The application instead executes the Transact-SQL statements, such as SELECT, directly.

If the application uses ADO, OLE DB, and ODBC, it leaves all API cursor attributes at their default settings so that no API cursors are requested. This default set of attributes is to request a forward-only, read-only cursor with a rowset size of 1.

Because this type of processing is used when all cursor attributes are set to their defaults, and when no cursor processing is actually involved from SQL Server or the database API, this is called a default result set.

A default result set is not given to an application in one large block. The result set is cached in the network buffers on the client. The application fetches through the result set one row at a time. On each fetch, the OLE DB provider or the ODBC driver moves the data from the next row in the network buffer into variables in the application. OLE DB, ODBC, and ADO applications use the same API functions to retrieve the rows that they would use to fetch the rows from a cursor. The SqlClient managed provider uses the SqlDataReader class to expose a default result set. When MultipleActiveResultSets is set to true, more than one SqlDataReader is allowed to be open at a given time.

Default result sets are the most efficient way to transmit results to the client. The only packet sent from the client computer to the server is the original packet with the statement to execute. When the results are sent back to the client, SQL Server puts as many result set rows as it can into each packet, minimizing the number of packets sent to the client.

All Transact-SQL statements are supported when using default result sets. You can also execute batches or stored procedures containing multiple statements that return result sets when using default result sets.

Default result sets can only be used to send result sets back to a client application. The data in a default result set is not available to any other Transact-SQL statement or variable in a batch, stored procedure, or trigger. For example, consider the following statement in a stored procedure or trigger:

SELECT ProductID FROM AdventureWorks.Production.Product;

The statement generates a default result set containing the IDs of all the products in the AdventureWorks database, which is sent directly to the client by SQL Server. None of the other Transact-SQL statements or variables in the stored procedure or trigger can reference this list of product IDs. For the data in this result set to be used by other Transact-SQL statements, it would have to be in a Transact-SQL server cursor:

DECLARE abc CURSOR FOR

SELECT ProductID FROM AdventureWorks.Production.Product;

Implementimi i kursoreve

Microsoft SQL Server supports three cursor implementations:

Transact-SQL cursors

Are based on the DECLARE CURSOR syntax and are used mainly in Transact-SQL scripts, stored procedures, and triggers. Transact-SQL cursors are implemented on the server and are managed by Transact-SQL statements sent from the client to the server. They may also be contained in batches, stored procedures, or triggers.

Application programming interface (API) server cursors

Support the API cursor functions in OLE DB and ODBC. API server cursors are implemented on the server. Each time a client application calls an API cursor function, the SQL Server Native Client OLE DB provider or ODBC driver transmits the request to the server for action against the API server cursor.

Client cursors

Are implemented internally by the SQL Server Native Client ODBC driver and by the DLL that implements the ADO API. Client cursors are implemented by caching all the result set rows on the client. Each time a client application calls an API cursor function, the SQL Server Native Client ODBC driver or the ADO DLL performs the cursor operation on the result set rows cached on the client.

Because Transact-SQL cursors and API server cursors are implemented on the server, they are referred to collectively as server cursors.

Do not mix the use of these various types of cursors. If you execute a DECLARE CURSOR and OPEN statement from an application, first set the API cursor attributes to their defaults. If you set API cursor attributes to something other than their defaults and then execute a DECLARE CURSOR and OPEN statement, you are asking SQL Server to map an API cursor over a Transact-SQL cursor. For example, do not set the ODBC attributes that call for mapping a keyset-driven cursor over a result set, and then use that statement handle to execute a DECLARE CURSOR and OPEN calling for an INSENSITIVE cursor.

A potential drawback of server cursors is that they currently do not support all Transact-SQL statements. Server cursors do not support Transact-SQL statements that generate multiple result sets; therefore, they cannot be used when the application executes a stored procedure or a batch that contain more than one SELECT statement. Server cursors also do not support SQL statements containing the keywords COMPUTE, COMPUTE BY, FOR BROWSE, or INTO.

Server Cursors vs. Default Result Sets

Using a cursor is less efficient than using a default result set. In a default result set the only packet sent from the client to the server is the packet containing the statement to execute. When using a server cursor, each FETCH statement must be sent from the client to the server, where it must be parsed and compiled into an execution plan.

If a Transact-SQL statement will return a relatively small result set that can be cached in the memory available to the client application, and you know before executing the statement that you must retrieve the entire result set, use a default result set. Use server cursors only when cursor operations are required to support the functionality of the application, or when only part of the result set is likely to be retrieved.

Server Cursors vs. Client Cursors

There are several advantages to using server cursors instead of client cursors:

Performance

If you are going to access a fraction of the data in the cursor (typical of many browsing applications), using server cursors provides optimal performance because only fetched data is sent over the network. Client cursors cache the entire result set on the client.

Additional cursor types

If the SQL Server Native Client ODBC driver used only client cursors, it could support only forward-only and static cursors. By using API server cursors the driver can also support keyset-driven and dynamic cursors. SQL Server also supports the full range of cursor concurrency attributes only through server cursors. Client cursors are limited in the functionality they support.

More accurate positioned updates

Server cursors directly support positioned operations, such as the ODBC SQLSetPos function or UPDATE and DELETE statements with the WHERE CURRENT OF clause. Client cursors, on the other hand, simulate positioned cursor updates by generating a Transact-SQL searched UPDATE statement, which leads to unintended updates if more than one row matches the WHERE clause conditions of the UPDATE statement.

Memory usage

When using server cursors, the client does not need to cache large amounts of data or maintain information about the cursor position because the server does that.

Multiple active statements

When using server cursors, no results are left outstanding on the connection between cursor operations. This allows you to have multiple cursor-based statements active at the same time.

The operation of all server cursors, except static or insensitive cursors, depends on the schema of the underlying tables. Any schema changes to those tables after a cursor has been declared results in an error on any subsequent operation on that cursor.

Specifying Cursors

In the OLE DB, ODBC, and ADO specifications, a cursor is implicitly opened over any result set returned by a Transact-SQL statement. Before executing a Transact-SQL statement, you should first set attributes or properties to define the characteristics of the cursor. You can then call API functions to fetch one row or batch of rows at a time. The default settings for the API cursor attributes or properties have the same characteristics as a Microsoft SQL Server default result set. The SQL Server Native Client ODBC driver and SQL Server Native Client OLE DB Provider both implement default result sets when the cursor attributes or properties are set to their defaults. No cursor is used in this case. The driver and provider only implement a cursor if any of the cursor attributes or properties are changed from their defaults.

OLE DB, ODBC, and ADO each use different terms in referring to this combination of a result set and cursor:

OLE DB uses the term rowset to refer to the combination of a result set and its associated cursor behaviors.

ODBC uses the terms result set and cursor somewhat interchangeably because it considers each result set to have a cursor automatically mapped over it. A rowset in ODBC is specifically the number of rows returned on a fetch.

ADO uses the term recordset in the same way that OLE DB uses the term rowset.

The default of the Microsoft SQL Server Native Client OLE DB Provider and SQL Server Native Client ODBC driver is to use an API server cursor to implement any requested cursor functions. ODBC client applications can use client cursors instead of server cursors by loading the ODBC Cursor Library. OLE DB does not have its own implementation of client cursors, but the ADO API does.

The following table summarizes what type of cursor or result set is implemented based on the current API cursor settings and the Transact-SQL statement being executed. The cells with "Do not combine cursor types" indicate that both a Transact-SQL server cursor and an API cursor are being requested at the same time. This can result in undefined behavior and should not be done.

Transact SQL CURSOR

Transact-SQL cursors are used mainly in stored procedures, triggers, and Transact-SQL scripts in which they make the contents of a result set available to other Transact-SQL statements.

The typical process for using a Transact-SQL cursor in a stored procedure or trigger is:

Declare Transact-SQL variables to contain the data returned by the cursor. Declare one variable for each result set column. Declare the variables to be large enough to hold the values returned by the column and with a data type that can be implicitly converted from the data type of the column.

Associate a Transact-SQL cursor with a SELECT statement using the DECLARE CURSOR statement. The DECLARE CURSOR statement also defines the characteristics of the cursor, such as the cursor name and whether the cursor is read-only or forward-only.

Use the OPEN statement to execute the SELECT statement and populate the cursor.

Use the FETCH INTO statement to fetch individual rows and have the data for each column moved into a specified variable. Other Transact-SQL statements can then reference those variables to access the fetched data values. Transact-SQL cursors do not support fetching blocks of rows.

When you are finished with the cursor, use the CLOSE statement. Closing a cursor frees some resources, such as the cursor's result set and its locks on the current row, but the cursor structure is still available for processing if you reissue an OPEN statement. Because the cursor is still present, you cannot reuse the cursor name at this point. The DEALLOCATE statement completely frees all resources allocated to the cursor, including the cursor name. After a cursor is deallocated, you must issue a DECLARE statement to rebuild the cursor.

Monitoring Transact-SQL Cursor Activity

You can use the sp_cursor_list system stored procedure to get a list of cursors visible to the current connection, and sp_describe_cursor, sp_describe_cursor_columns, and sp_describe_cursor_tables to determine the characteristics of a cursor.

After the cursor is opened, the @@CURSOR_ROWS function or the cursor_rows column returned by sp_cursor_list or sp_describe_cursor indicates the number of rows in the cursor.

After each FETCH statement, @@FETCH_STATUS is updated to reflect the status of the last fetch. You can also get this status information from the fetch_status column returned by sp_describe_cursor. @@FETCH_STATUS reports conditions such as fetching beyond the first or last row in the cursor. @@FETCH_STATUS is global to your connection and is reset by each fetch on any cursor open for the connection. If you must know the status later, save @@FETCH_STATUS into a user variable before executing another statement on the connection. Even though the next statement may not be a FETCH, it could be an INSERT, UPDATE or DELETE that fires a trigger containing FETCH statements that reset @@FETCH_STATUS. The fetch_status column returned by sp_describe_cursor is specific to the cursor specified and is not affected by FETCH statements that reference other cursors. sp_describe_cursor is, however, affected by FETCH statements that reference the same cursor, so care is still needed in its use.

After a FETCH is completed, the cursor is positioned on the fetched row. The fetched row is known as the current row. If the cursor was not declared as a read-only cursor, you can execute an UPDATE or DELETE statement with a WHERE CURRENT OF cursor_name clause to modify the current row.

The name given to a Transact-SQL cursor by the DECLARE CURSOR statement can be either global or local. Global cursor names are referenced by any batch, stored procedure, or trigger executing on the same connection. Local cursor names cannot be referenced outside the batch, stored procedure, or trigger in which the cursor is declared. Local cursors in triggers and stored procedures are therefore protected from unintended references outside the stored procedure or trigger.

Using the cursor Variable

Microsoft SQL Server also supports variables with a cursor data type. A cursor can be associated with a cursor variable by either of two methods:

/* Use DECLARE @local_variable, DECLARE CURSOR and SET. */

DECLARE @MyVariable CURSOR

DECLARE MyCursor CURSOR FOR

SELECT LastName FROM AdventureWorks.Person.Contact

SET @MyVariable = MyCursor

GO

/* Use DECLARE @local_variable and SET */

DECLARE @MyVariable CURSOR

SET @MyVariable = CURSOR SCROLL KEYSET FOR

SELECT LastName FROM AdventureWorks.Person.Contact;

DEALLOCATE MyCursor;

After a cursor has been associated with a cursor variable, the cursor variable can be used instead of the cursor name in Transact-SQL cursor statements. Stored procedure output parameters can also be assigned a cursor data type and associated with a cursor. This allows stored procedures to expose local cursors in a controlled manner.

Referencing Transact-SQL Cursors

Transact-SQL cursor names and variables are referenced only by Transact-SQL statements; they cannot be referenced by the API functions of OLE DB, ODBC, and ADO. For example, if you use DECLARE CURSOR and OPEN a Transact-SQL cursor, there is no way to use the ODBC SQLFetch or SQLFetchScroll functions to fetch a row from the Transact-SQL cursor. Applications that need cursor processing and are using these APIs should use the cursor support built into the database API instead of Transact-SQL cursors.

You can use Transact-SQL cursors in applications by using FETCH and binding each column returned by the FETCH to a program variable. The Transact-SQL FETCH does not support batches, however, so this is the least efficient way to return data to an application. Fetching each row requires a roundtrip to the server. It is more efficient to use the cursor functionality built into the database APIs that support fetching batches of rows.

Transact-SQL cursors are extremely efficient when contained in stored procedures and triggers. This is because everything is compiled into one execution plan on the server and there is no network traffic associated with fetching rows.

Transact-SQL Cursors and SET Options

In SQL Server, an error is raised when a FETCH statement is issued in which there is a change in values as of the time the cursor was opened. This error occurs for any of the following plan-affecting options, or the options required for indexed views and computed columns. To avoid the error, do not change SET options while a cursor is open

Plan Effect Options

ARITHABORT

NUMERIC_ROUNDABORT

FORCEPLAN

QUOTED_IDENTIFIER

ANSI_NULL_DFLT_ON

ANSI_NULL_DFLT_OFF

ANSI_WARNINGS

ANSI_PADDING

ANSI_NULLS

CONCAT_NULL_YIELDS_NULL

DATEFIRST

DATEFORMAT

LANGUAGE

TEXTSIZE

Indexed views and computed columns

ANSI_NULLS

ANSI_PADDING

ANSI_WARNINGS

ARITHABORT (under compatibility level of 80 or lower)

CONCAT_NULL_YIELDS_NULL

QUOTED_IDENTIFIER

NUMERIC_ROUNDABORT

In SQL Server 2000, changes to ANSI_NULLS and QUOTED_IDENTIFIER did not raise an error, although the others did.

API server cursors

The OLE DB, ODBC, and ADO APIs support mapping cursors over the result sets of executed SQL statements. The Microsoft SQL Server Native Client OLE DB provider and SQL Server Native Client ODBC driver implement these operations through the use of API server cursors. API server cursors are cursors implemented on the server and managed by API cursor functions. As the application calls the API cursor functions, the cursor operation is transmitted to the server by the OLE DB provider or ODBC driver.

When using an API server cursor in OLE DB, ODBC, and ADO, use the functions or methods of the API to:

• • Open a connection.

  • Set attributes or properties defining the characteristics of the cursor the API automatically maps over each result set.

  • Execute one or more Transact-SQL statements.

  • Use API functions or methods to fetch the rows in the result sets.

When the API cursor attributes or properties are set to their default settings, the SQL Server Native Client OLE DB provider and the SQL Server Native Client ODBC driver use default result sets. Although the API is technically asking for a cursor, the default cursor characteristics match the behavior of a default result set. The OLE DB provider and ODBC driver, therefore, implement the default cursor options using a default result set because it is the most efficient way to retrieve rows from the server. When using default result sets, an application can execute any Transact-SQL statement or batch, but it can only have one outstanding statement on a connection. This means the application must process or cancel all the result sets returned by one statement before it can execute another statement on the connection.

When the API cursor attributes or properties are set to anything other than their defaults, the SQL Server Native Client OLE DB provider and the SQL Server Native Client ODBC driver use API server cursors instead of default result sets. Each call to an API function that fetches rows generates a roundtrip to the server to fetch the rows from the API server cursor.

API Server Cursor Restrictions

An application cannot execute the following statements when using API server cursors:

Transact-SQL statements that SQL Server does not support in server cursors.

Batches or stored procedures that return multiple result sets.

SELECT statements that contain COMPUTE, COMPUTE BY, FOR BROWSE, or INTO clauses.

An EXECUTE statement referencing a remote stored procedure.

API Server Cursor Implementation

The SQL Server Native Client OLE DB provider and SQL Server Native Client ODBC driver use these special system stored procedures to signal cursor operations to the server:

sp_cursoropen defines the SQL statement to be associated with the cursor and the cursor options, then populates the cursor.

sp_cursorfetch fetches a row or block of rows from the cursor.

sp_cursorclose closes and deallocates the cursor.

sp_cursoroption is used to set various cursor options.

sp_cursor is used to request positioned updates.

sp_cursorprepare compiles the Transact-SQL statement or batch associated with a cursor into an execution plan but does not create the cursor.

sp_cursorexecute creates and populates a cursor from the execution plan created by sp_cursorprepare.

sp_cursorunprepare discards the execution plan from sp_cursorprepare.

sp_cursorprepexec compiles a plan for the submitted Transact-SQL statement or batch associated with a cursor, creates the cursor, and populates it. sp_cursorprepexec combines the behavior of sp_cursorprepare and sp_cursorexecute.

These system stored procedures will show up in SQL Server Profiler traces of ADO, OLE DB, and ODBC applications that are using API server cursors. They are intended only for the internal use of the SQL Server Native Client OLE DB provider and the SQL Server Native Client ODBC driver. The full functionality of these procedures is available to the applications through the use of the cursor functionality of the database APIs. Specifying the procedures directly in an application is not supported.

When SQL Server executes a statement for a connection, no other statements can be executed on the connection until all the results from the first statement have been processed or canceled. This rule still holds when using API server cursors, but to the application it looks like SQL Server has started supporting multiple active statements on a connection. This is because the full result set is stored in the server cursor and the only statements being transmitted to SQL Server are the executions of the sp_cursor system stored procedures. SQL Server executes the stored procedure, and as soon as the client retrieves the result set it can execute any other statement. The OLE DB provider and ODBC driver always retrieve all the results from an sp_cursor stored procedure before they return control to the application. This lets applications interleave fetches against multiple active server cursors.

CLIENT CURSORS

ODBC supports client cursors, cursors implemented on the client. In a client cursor, a default result set is used to cache the entire result set on the client and all cursor operations are performed against this client cache. None of the server cursor functionality of Microsoft SQL Server is used. Client cursors support only forward-only and static cursors, not keyset-driven or dynamic cursors.

ODBC implements client cursors that use the ODBC Cursor Library. This is intended for use with ODBC drivers that support only the default settings for cursor characteristics. Because the SQL Server Native Client ODBC driver offers full support for cursor operations through server cursors, limit the use of client cursors.

Client cursors should be used only to alleviate the restriction that server cursors do not support all Transact-SQL statements or batches. If a static scrolling cursor is needed on a Transact-SQL statement or batch that cannot be executed with a server cursor, consider using a client cursor.

FETCHING AND SCROLLING

The operation to retrieve a row from a cursor is called a fetch. These are the fetch options:

FETCH FIRST

Fetches the first row in the cursor.

FETCH NEXT

Fetches the row after the last row fetched.

FETCH PRIOR

Fetches the row before the last row fetched.

FETCH LAST

Fetches the last row in the cursor.

FETCH ABSOLUTE n

Fetches the nth row from the first row in the cursor if n is a positive integer. If n is a negative integer, the row n rows before the end of the cursor is fetched. If n is 0, no rows are fetched.

FETCH RELATIVE n

Fetches the row n rows from the last row fetched. If n is positive, the row n rows after the last row fetched is fetched. If n is negative, the row n rows before the last row fetched is fetched. If n is 0, the same row is fetched again.

When a cursor is opened, the current row position in the cursor is logically before the first row. This causes the different fetch options to have the following behaviors if they are the first fetch performed after the cursor is opened:

FETCH FIRST

Fetches the first row in the cursor.

FETCH NEXT

Fetches the first row in the cursor.

FETCH PRIOR

Does not fetch a row.

FETCH LAST

Fetches the last row in the cursor.

FETCH ABSOLUTE n

Fetches the nth row from the first row in the cursor if n is a positive integer. If n is a negative integer, then the row n rows before the end of the cursor is fetched (for example, n = -1 returns the last row in the cursor). If n is 0, no rows are fetched.

FETCH RELATIVE n

Fetches the nth row in the cursor if n is positive. No rows are fetched if n is negative or 0.

Transact-SQL cursors are limited to fetching one row at a time. API server cursors support fetching blocks of rows with each fetch. A cursor that supports fetching multiple rows at a time is called a block cursor.

The operation to retrieve a row from a cursor is called a fetch. These are the fetch options:

FETCH FIRST

Fetches the first row in the cursor.

FETCH NEXT

Fetches the row after the last row fetched.

FETCH PRIOR

Fetches the row before the last row fetched.

FETCH LAST

Fetches the last row in the cursor.

FETCH ABSOLUTE n

Fetches the nth row from the first row in the cursor if n is a positive integer. If n is a negative integer, the row n rows before the end of the cursor is fetched. If n is 0, no rows are fetched.

FETCH RELATIVE n

Fetches the row n rows from the last row fetched. If n is positive, the row n rows after the last row fetched is fetched. If n is negative, the row n rows before the last row fetched is fetched. If n is 0, the same row is fetched again.

When a cursor is opened, the current row position in the cursor is logically before the first row. This causes the different fetch options to have the following behaviors if they are the first fetch performed after the cursor is opened:

FETCH FIRST

Fetches the first row in the cursor.

FETCH NEXT

Fetches the first row in the cursor.

FETCH PRIOR

Does not fetch a row.

FETCH LAST

Fetches the last row in the cursor.

FETCH ABSOLUTE n

Fetches the nth row from the first row in the cursor if n is a positive integer. If n is a negative integer, then the row n rows before the end of the cursor is fetched (for example, n = -1 returns the last row in the cursor). If n is 0, no rows are fetched.

FETCH RELATIVE n

Fetches the nth row in the cursor if n is positive. No rows are fetched if n is negative or 0.

Transact-SQL cursors are limited to fetching one row at a time. API server cursors support fetching blocks of rows with each fetch. A cursor that supports fetching multiple rows at a time is called a block cursor.

Cursor Classifications

A cursor can be classified by the fetch options it supports:

Forward-only

Rows must be fetched serially from the first row to the last row. FETCH NEXT is the only fetch operation allowed.

Scrollable

Rows can be randomly fetched from anywhere in the cursor. All the fetch operations are allowed (except that dynamic cursors do not support fetch absolute).

Scrollable cursors are especially useful for supporting online applications. A cursor can be mapped to a grid or list box in the application. As the user scrolls up and down and all around the grid, the application uses scroll fetches to retrieve the rows from the cursor the user wants to see.

APIs for Fetching Rows

The APIs for the actual statements, functions, or methods used have different names to fetch rows:

Transact-SQL cursors use the FETCH FIRST, FETCH LAST, FETCH NEXT, FETCH PRIOR, FETCH ABSOLUTE(n), and FETCH RELATIVE(n) statements.

OLE DB uses methods such as IRowset::GetNextRows, IRowsetLocate::GetRowsAt, IRowsetLocate::GetRowsAtBookmark, and IRowsetScroll::GetRowsAtRatio.

ODBC uses the SQLFetch function, which is the same as a FETCH NEXT for one row, or the SQLFetchScroll function. SQLFetchScroll supports block cursors and all the fetch options (first, last, next, prior, absolute, relative).

ADO uses the Move, MoveFirst, MoveLast, MoveNext, and MovePrevious Recordset methods to acquire a position in a cursor. The GetRows recordset method is then used to retrieve one or more rows at that position. GetRows can also be called directly with the Start parameter set to the number of the row to fetch.

SQL Server Cursor Examples

In some circles cursors are never used, in others they are a last resort and in other groups they are used regularly. In each of these camps they have different reasons for their stand on cursor usage. Regardless of your stand on cursors they probably have a place in particular circumstances and not in others. So it boils down to your understanding of the coding technique then your understanding of the problem at hand to make a decision on whether or not cursor based processing is appropriate or not. To get started let's do the following:

• • Look at an example cursor

  • Break down the components of the cursor

  • Provide additional cursor examples

  • Analyze the pros and cons of cursor usage

Example Cursor

Here is an example cursor from tip Simple script to backup all SQL Server databases where backups are issued in a serial manner:

DECLARE @name VARCHAR(50) -- database name

DECLARE @path VARCHAR(256) -- path for backup files

DECLARE @fileName VARCHAR(256) -- filename for backup

DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\Backup\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR

SELECT name

FROM master.dbo.sysdatabases

WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @fileName = @path + @name + '_' + @fileDate + '.BAK'

BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor

Cursor Components

Based on the example above, cursors include these components:

• • DECLARE statements - Declare variables used in the code block

  • SET\SELECT statements - Initialize the variables to a specific value

  • DECLARE CURSOR statement - Populate the cursor with values that will be evaluated

    • • NOTE - There are an equal number of variables in the DECLARE <cursor_name> CURSOR FOR statement as there are in the SELECT statement. This could be 1 or many variables and associated columns.

  • OPEN statement - Open the cursor to begin data processing

  • FETCH NEXT statements - Assign the specific values from the cursor to the variables

    • • NOTE - This logic is used for the initial population before the WHILE statement and then again during each loop in the process as a portion of the WHILE statement

  • WHILE statement - Condition to begin and continue data processing

  • BEGIN...END statement - Start and end of the code block

    • • NOTE - Based on the data processing multiple BEGIN...END statements can be used

  • Data processing - In this example, this logic is to backup a database to a specific path and file name, but this could be just about any DML or administrative logic

  • CLOSE statement - Releases the current data and associated locks, but permits the cursor to be re-opened

  • DEALLOCATE statement - Destroys the cursor

Additional Cursor Examples

In the example above backups are issued via a cursor, check out these other tips that leverage cursor based logic:

http://www.mssqltips.com/tip.asp?tip=1599

Searching and finding a string value in all columns in a SQL Server table

Problem

Sometimes there is a need to find if a string value exists in any column in your table. Although there are system stored procedures that do a "for each database" or a "for each table", there is not a system stored procedure that does a "for each column". So trying to find a value in any column in your database requires you to build the query to look through each column you want to search using an OR operator between each column. Is there any way this can be dynamically generated?

Solution

Once again this is where T-SQL comes in handy along with the use of system tables or system views. The code below allows you to search for a value in all text data type columns such as (char, nchar, ntext, nvarchar, text and varchar).

The stored procedure gets created in the master database so you can use it in any of your databases and it takes three parameters:

• • stringToFind - this is the string you are looking for. This could be a simple value as 'test' or you can also use the % wildcard such as '%test%', '%test' or 'test%'.

  • schema - this is the schema owner of the object

  • table - this is the table name you want to search, the procedure will search all char, nchar, ntext, nvarchar, text and varchar columns in the table

The first thing you need to do is create this stored procedure by copying the below code and executing it in a query window.

USE master

GO

CREATE PROCEDURE sp_FindStringInTable @stringToFind VARCHAR(100), @schema sysname, @table sysname

AS

DECLARE @sqlCommand VARCHAR(8000)

DECLARE @where VARCHAR(8000)

DECLARE @columnName sysname

DECLARE @cursor VARCHAR(8000)

BEGIN TRY

SET @sqlCommand = 'SELECT * FROM ' + @schema + '.' + @table + ' WHERE'

SET @where = ''

SET @cursor = 'DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME

FROM ' + DB_NAME() + '.INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = ''' + @schema + '''

AND TABLE_NAME = ''' + @table + '''

AND DATA_TYPE IN (''char'',''nchar'',''ntext'',''nvarchar'',''text'',''varchar'')'

EXEC (@cursor)

OPEN col_cursor

FETCH NEXT FROM col_cursor INTO @columnName

WHILE @@FETCH_STATUS = 0

BEGIN

IF @where <> ''

SET @where = @where + ' OR'

SET @where = @where + ' ' + @columnName + ' LIKE ''' + @stringToFind + ''''

FETCH NEXT FROM col_cursor INTO @columnName

END

CLOSE col_cursor

DEALLOCATE col_cursor

SET @sqlCommand = @sqlCommand + @where

--PRINT @sqlCommand

EXEC (@sqlCommand)

END TRY

BEGIN CATCH

PRINT 'There was an error'

IF CURSOR_STATUS('variable', 'col_cursor') <> -3

BEGIN

CLOSE col_cursor

DEALLOCATE col_cursor

END

END CATCH

Once the stored procedure has been created you can run some tests.

Here are some tests that were done against the AdventureWorks database.

Find the value 'Irv%' in the Person.Address table.

USE AdventureWorks

GO

EXEC sp_FindStringInTable 'Irv%', 'Person', 'Address'

Find the value '%land%' in the Person.Address table.

USE AdventureWorks

GO

EXEC sp_FindStringInTable '%land%', 'Person', 'Address'

Find the value '%land%' in the Person.Contact table.

USE AdventureWorks

GO

EXEC sp_FindStringInTable '%land%', 'Person', 'Contact'

That's all there is to it. Once this has been created you can use this against any table and any database on your server.