<TR ALIGN="LEFT" rowspan="1">		<TD ALIGN="LEFT">7</TD>		<TD ALIGN="LEFT">5</TD>		<TD ALIGN="LEFT">Come On, Come On</TD>		<TD ALIGN="LEFT">1992</TD>	</TR>	<TR ALIGN="LEFT" rowspan="1">		<TD ALIGN="LEFT">7</TD>		<TD ALIGN="LEFT">3</TD>		<TD ALIGN="LEFT">Stones in the Road</TD>		<TD ALIGN="LEFT">1994</TD>	</TR>	<TR ALIGN="LEFT" rowspan="1">		<TD ALIGN="LEFT">8</TD>		<TD ALIGN="LEFT">5</TD>		<TD ALIGN="LEFT">Second Piano Concerto</TD>		<TD ALIGN="LEFT">1985</TD>	</TR></TABLE><H4><FONT COLOR="#000077">Example 13.1</FONT></H4><P>You can create a temporary table in the <TT>tempdb</TT> database. After insertinga dummy record into this table, log out. After logging back into SQL Server, tryto select the dummy record out of the temporary table. Note the results:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">1&gt; <B>create table #albums (</B>2&gt; <B>artist char(30),</B>3&gt; <B>album_name char(50),</B>4&gt;<B> media_type int)</B>5&gt; <B>go</B>1&gt; <B>insert #albums values (&quot;The Replacements&quot;, &quot;Pleased To Meet Me&quot;, 1)</B>2&gt; <B>go</B></FONT></PRE><P>Now log out of the SQL Server connection using the <TT>EXIT</TT> (or <TT>QUIT</TT>)command. After logging back in and switching to the database you last used, try thefollowing command:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">1&gt; select * from #albums2&gt; go</FONT></PRE><H5>ANALYSIS:</H5><P>This table does not exist in the current database.<H4><FONT COLOR="#000077">Example 13.2</FONT></H4><P>Now create the table with syntax 2:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">1&gt; <B>create table tempdb..albums (</B>2&gt; <B>artist char(30)</B>,3&gt; <B>album_name char(50),</B>4&gt; <B>media_type int)</B>5&gt;<B> go</B>1&gt; <B>insert #albums values (&quot;The Replacements&quot;, &quot;Pleased To Meet Me&quot;, 1)</B>2&gt; <B>go</B></FONT></PRE><P>After logging out and logging back in, switch to the database you were using when<TT>create table tempdb..albums()</TT> was issued; then issue the following command:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">1&gt; <B>select * from #albums</B>2&gt;<B> go</B></FONT></PRE><P>This time, you get the following results:</P><H5>OUTPUT:</H5><PRE><FONT COLOR="#0066FF">artist                         album_name              media_type_______________________________________________________________________________________</FONT></PRE><PRE><FONT COLOR="#0066FF">The Replacements               Pleased To Meet Me      1</FONT></PRE><H4><FONT COLOR="#000077">Example 13.3</FONT></H4><P>This example shows a common usage of temporary tables: to store the results ofcomplex queries for use in later queries.</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">1&gt; <B>create table #temp_info (</B>2&gt; <B>name char(30),</B>3&gt; <B>homebase char(40),</B>4&gt; <B>style char(20),</B>5&gt; <B>artist_id int)</B>6&gt; <B>insert #temp_info</B>7&gt; <B>select * from ARTISTS where homebase = &quot;Nashville&quot;</B>8&gt; <B>select RECORDINGS.* from RECORDINGS, ARTISTS</B>9&gt; <B>where RECORDINGS.artist_id = #temp_info.artist_id</B>10&gt;<B> go</B></FONT></PRE><P>The preceding batch of commands selects out the recording information for allthe artists whose home base is Nashville.</P><P>The following command is another way to write the set of SQL statements used inExample 13.3:</P><PRE><FONT COLOR="#0066FF">1&gt;<B> select ARTISTS.* from ARTISTS, RECORDINGS where ARTISTS.homebase = &quot;Nashville&quot;</B>2&gt; <B>go</B></FONT></PRE><H2><FONT COLOR="#000077">Cursors</FONT></H2><P>A database cursor is similar to the cursor on a word processor screen. As youpress the Down Arrow key, the cursor scrolls down through the text one line at atime. Pressing the Up Arrow key scrolls your cursor up one line at a time. Hittingother keys such as Page Up and Page Down results in a leap of several lines in eitherdirection. Database cursors operate in the same way.</P><P>Database cursors enable you to select a group of data, scroll through the groupof records (often called a recordset), and examine each individual line of data asthe cursor points to it. You can use a combination of local variables and a cursorto individually examine each record and perform any external operation needed beforemoving on to the next record.</P><P>One other common use of cursors is to save a query's results for later use. Acursor's result set is created from the result set of a <TT>SELECT</TT> query. Ifyour application or procedure requires the repeated use of a set of records, it isfaster to create a cursor once and reuse it several times than to repeatedly querythe database. (And you have the added advantage of being able to scroll through thequery's result set with a cursor.)</P><P>Follow these steps to create, use, and close a database cursor:<DL>	<DD><B>1.</B> Create the cursor.	<P><B>2. </B>Open the cursor for use within the procedure or application.</P>	<P><B>3.</B> Fetch a record's data one row at a time until you have reached the end	of the cursor's records.</P>	<P><B>4.</B> Close the cursor when you are finished with it.</P>	<P><B>5. </B>Deallocate the cursor to completely discard it.</DL><H3><FONT COLOR="#000077">Creating a Cursor</FONT></H3><P>To create a cursor using Transact-SQL, issue the following syntax:</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">declare cursor_name cursor     for select_statement     [for {read only | update [of column_name_list]}]</FONT></PRE><P>The Oracle7 SQL syntax used to create a cursor looks like this:</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">DECLARE cursor_name CURSOR     FOR {SELECT command | statement_name | block_name}</FONT></PRE><P>By executing the <TT>DECLARE cursor_name CURSOR</TT> statement, you have definedthe cursor result set that will be used for all your cursor operations. A cursorhas two important parts: the cursor result set and the cursor position.</P><P>The following statement creates a cursor based on the <TT>ARTISTS</TT> table:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">1&gt; <B>create Artists_Cursor cursor</B>2&gt; <B>for select * from ARTISTS</B>3&gt; <B>go</B></FONT></PRE><H5>ANALYSIS:</H5><P>You now have a simple cursor object named <TT>Artists_Cursor</TT> that containsall the records in the <TT>ARTISTS</TT> table. But first you must open the cursor.<H3><FONT COLOR="#000077">Opening a Cursor</FONT></H3><P>The simple command to open a cursor for use is</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">open cursor_name</FONT></PRE><P>Executing the following statement opens <TT>Artists_Cursor</TT> for use:</P><PRE><FONT COLOR="#0066FF">1&gt; <B>open Artists_Cursor</B>2&gt;<B> go</B></FONT></PRE><P>Now you can use the cursor to scroll through the result set.<H3><FONT COLOR="#000077">Scrolling a Cursor</FONT></H3><P>To scroll through the cursor's result set, Transact-SQL provides the following<TT>FETCH</TT> command.</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">fetch cursor_name [into fetch_target_list]</FONT></PRE><P>Oracle SQL provides the following syntax:</P><PRE><FONT COLOR="#0066FF">FETCH cursor_name {INTO : host_variable     [[INDICATOR] : indicator_variable]        [,   : host_variable        [[INDICATOR] : indicator_variable] ]...     | USING DESCRIPTOR descriptor }</FONT></PRE><P>Each time the <TT>FETCH</TT> command is executed, the cursor pointer advancesthrough the result set one row at a time. If desired, data from each row can be fetchedinto the<TT> fetch_target_list</TT> variables.<BLOCKQUOTE>	<P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Transact-SQL enables the programmer	to advance more than one row at a time by using the following command: <TT>set cursor	rows </TT>number<TT> for cursor_name</TT>. This command cannot be used with the <TT>INTO</TT>	clause, however. It is useful only to jump forward a known number of rows instead	of repeatedly executing the <TT>FETCH</TT> statement. <HR></BLOCKQUOTE><P>The following statements fetch the data from the<TT> Artists_Cursor</TT> resultset and return the data to the program variables:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">1&gt; <B>declare @name char(30)</B>2&gt; <B>declare @homebase char(40)</B>3&gt; <B>declare @style char(20)</B>4&gt; <B>declare @artist_id int</B>5&gt; <B>fetch Artists_Cursor into @name, @homebase, @style, @artist_id</B>6&gt; <B>print @name</B>7&gt; <B>print @homebase</B>8&gt; <B>print @style</B>9&gt; <B>print char(@artist_id)</B>10&gt; <B>go</B></FONT></PRE><P>You can use the <TT>WHILE</TT> loop (see Day 12, &quot;Database Security&quot;)to loop through the entire result set. But how do you know when you have reachedthe end of the records?<H3><FONT COLOR="#000077">Testing a Cursor's Status</FONT></H3><P>Transact-SQL enables you to check the status of the cursor at any time throughthe maintenance of two global variables: <TT>@@sqlstatus</TT> and <TT>@@rowcount</TT>.</P><P>The <TT>@@sqlstatus</TT> variable returns status information concerning the lastexecuted <TT>FETCH</TT> statement. (The Transact-SQL documentation states that nocommand other than the <TT>FETCH</TT> statement can modify the <TT>@@sqlstatus</TT>variable.) This variable contains one of three values. The following table appearsin the Transact-SQL reference manuals: <BR><BR><TABLE BORDER="1">	<TR ALIGN="LEFT" rowspan="1">		<TD ALIGN="LEFT" VALIGN="TOP">Status</TD>		<TD ALIGN="LEFT" VALIGN="TOP">Meaning</TD>	</TR>	<TR>		<TD ALIGN="LEFT" VALIGN="TOP"><TT>0</TT></TD>		<TD ALIGN="LEFT" VALIGN="TOP">Successful completion of the <TT>FETCH</TT> statement.</TD>	</TR>	<TR ALIGN="LEFT" rowspan="1">		<TD ALIGN="LEFT" VALIGN="TOP"><TT>1</TT></TD>		<TD ALIGN="LEFT" VALIGN="TOP">The <TT>FETCH</TT> statement resulted in an error.</TD>	</TR>	<TR ALIGN="LEFT" rowspan="1">		<TD ALIGN="LEFT" VALIGN="TOP"><TT>2</TT></TD>		<TD VALIGN="TOP">There is no more data in the result set.</TD>	</TR></TABLE><DL>	<DD></DL><P>The <TT>@@rowcount</TT> variable contains the number of rows returned from thecursor's result set up to the previous fetch. You can use this number to determinethe number of records in a cursor's result set.</P><P>The following code extends the statements executed during the discussion of the<TT>FETCH</TT> statement. You now use the <TT>WHILE</TT> loop with the <TT>@@sqlstatus</TT>variable to scroll the cursor:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">1&gt; <B>declare @name char(30)</B>2&gt; <B>declare @homebase char(40)</B>3&gt;<B> declare @style char(20)</B>4&gt; <B>declare @artist_id int</B>5&gt; <B>fetch Artists_Cursor into @name, @homebase, @style, @artist_id</B>6&gt; <B>while (@@sqlstatus = 0)</B>7&gt; <B>begin</B>8&gt;    <B>  print @name</B>9&gt;      <B>print @homebase</B>10&gt;     <B>print @style</B>11&gt;    <B> print char(@artist_id)</B>12&gt;     <B>fetch Artists_Cursor into @name, @homebase, @style, @artist_id</B>13&gt; <B>end</B>14&gt; <B>go</B></FONT></PRE><H5>ANALYSIS:</H5><P>Now you have a fully functioning cursor! The only step left is to close the cursor.<H3><FONT COLOR="#000077">Closing a Cursor</FONT></H3><P>Closing a cursor is a very simple matter. The statement to close a cursor is asfollows:</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">close cursor_name</FONT></PRE><P>This cursor still exists; however, it must be reopened. Closing a cursor essentiallycloses out its result set, not its entire existence. When you are completely finishedwith a cursor, the <TT>DEALLOCATE</TT> command frees the memory associated with acursor and frees the cursor name for reuse. The <TT>DEALLOCATE</TT> statement syntaxis as follows:</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">deallocate cursor cursor_name</FONT></PRE><P>Example 13.4 illustrates the complete process of creating a cursor, using it,and then closing it, using Transact-SQL.<H4><FONT COLOR="#000077">Example 13.4</FONT></H4><H5><FONT COLOR="#000000">INPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">1&gt; <B>declare @name char(30)</B>2&gt; <B>declare @homebase char(40)</B>3&gt; <B>declare @style char(20)</B>4&gt; <B>declare @artist_id int</B>5&gt; <B>create Artists_Cursor cursor</B>6&gt; <B>for select * from ARTISTS</B>7&gt; <B>open Artists_Cursor</B>8&gt; <B>fetch Artists_Cursor into @name, @homebase, @style, @artist_id</B>9&gt; <B>while (@@sqlstatus = 0)</B>10&gt; <B>begin</B>11&gt;      <B>print @name</B>12&gt;     <B> print @homebase</B>13&gt;    <B>  print @style</B>14&gt;     <B> print char(@artist_id)</B>15&gt;    <B>  fetch Artists_Cursor into @name, @homebase, @style, @artist_id</B>16&gt; <B>end</B>17&gt; <B>close Artists_Cursor</B>18&gt; <B>deallocate cursor Artists_Cursor</B>19&gt; <B>go</B></FONT></PRE><BLOCKQUOTE>	<P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The following is sample data only.	<HR></P></BLOCKQUOTE><H5>OUTPUT:</H5><PRE><FONT COLOR="#0066FF">Soul Asylum             Minneapolis        Rock        1Maurice Ravel           France             Classical   2Dave Matthews Band      Charlottesville    Rock        3Vince Gill              Nashville          Country     4Oingo Boingo            Los Angeles        Pop         5Crowded House           New Zealand        Pop         6Mary Chapin-Carpenter   Nashville          Country     7Edward MacDowell        U.S.A.             Classical   8</FONT></PRE><H3><FONT COLOR="#000077">The Scope of Cursors</FONT></H3><P>Unlike tables, indexes, and other objects such as triggers and stored procedures,cursors do not exist as database objects after they are created. Instead, cursorshave a limited scope of use.<BLOCKQUOTE>	<P><HR><FONT COLOR="#000077"><B>WARNING:</B></FONT><B> </B>Remember, however, that memory	remains allocated for the cursor, even though its name may no longer exist. Before	going outside the cursor's scope, the cursor should always be closed and deallocated.	<HR></BLOCKQUOTE><P>A cursor can be created within three regions:<UL>	<LI>In a session--A session begins when a user logs on. If the user logged on to	an SQL Server and then created a cursor, then cursor_name would exist until the user	logged off. The user would not be able to reuse cursor_name during the current session.	<P>	<LI>Stored procedure--A cursor created inside a stored procedure is good only during	the execution of the stored procedure. As soon as the stored procedure exits, <TT>cursor_name</TT>