10) do not support the use of scrollable cursors. Example 19.10 gives you an ideaof how to use a <TT>WHILE</TT> loop to implement a rough cursor-type functionalitywhen that functionality is not automatically supplied.<H4><FONT COLOR="#000077">Example 19.10</FONT></H4><P>You can use the <TT>WHILE</TT> loop to scroll through tables one record at a time.Transact-SQL stores the <TT>rowcount</TT> variable that can be set to tell SQL Serverto return only one row at a time during a query. If you are using another databaseproduct, determine whether your product has a similar setting. By setting <TT>rowcount</TT>to <TT>1</TT> (its default is <TT>0</TT>, which means unlimited), SQL Serverreturns only one record at a time from a <TT>SELECT</TT> query. You can use thisone record to perform whatever operations you need to perform. By selecting the contentsof a table into a temporary table that is deleted at the end of the operation, youcan select out one row at a time, deleting that row when you are finished. When allthe rows have been selected out of the table, you have gone through every row inthe table! (As we said, this is a very rough cursor functionality!) Let's run theexample now.</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">1&gt; <B>set rowcount 1</B>2&gt;<B> declare @PLAYER char(30)</B>3&gt; <B>create table temp_BATTERS (</B>4&gt; <B>NAME char(30),</B>5&gt; <B>TEAM int,</B>6&gt; <B>AVERAGE float,</B>7&gt; <B>HOMERUNS int,</B>8&gt; <B>RBIS int)</B>9&gt;<B> insert temp_BATTERS</B>10&gt; <B>select * from BATTERS</B>11&gt; <B>while exists (select * from temp_BATTERS)</B>12&gt; <B>begin</B>13&gt;      <B>select @PLAYER = NAME from temp_BATTERS</B>14&gt;   <B>   print @PLAYER</B>15&gt;     <B> delete from temp_BATTERS where NAME = @PLAYER</B>16&gt; <B>end</B>17&gt; <B>print &quot;LOOP IS DONE!&quot;</B></FONT></PRE><H5>ANALYSIS:</H5><P>Note that by setting the <TT>rowcount</TT> variable, you are simply modifyingthe number of rows returned from a <TT>SELECT</TT>. If the <TT>WHERE</TT> clauseof the <TT>DELETE</TT> command returned five rows, five rows would be deleted! Alsonote that the <TT>rowcount</TT> variable can be reset repeatedly. Therefore, fromwithin the loop, you can query the database for some additional information by simplyresetting <TT>rowcount</TT> to <TT>1</TT> before continuing with the loop.<H2><FONT COLOR="#000077">Transact-SQL Wildcard Operators</FONT></H2><P>The concept of using wildcard conditions in SQL was introduced on Day 3, &quot;Expressions,Conditions, and Operators.&quot; The <TT>LIKE</TT> operator enables you to use wildcardconditions in your SQL statements. Transact-SQL extends the flexibility of wildcardconditions. A summary of Transact-SQL's wildcard operators follows.<UL>	<LI>The underscore character (<TT>_</TT>)represents any one individual character.	For example, <TT>_MITH</TT> tells the query to look for a five-character string ending	with <TT>MITH</TT>.	<P>	<LI>The percent sign (<TT>%</TT>) represents any one or multiple characters. For	example, <TT>WILL%</TT> returns the value <TT>WILLIAMS</TT> if it exists. <TT>WILL%</TT>	returns the value <TT>WILL</TT>.	<P>	<LI>Brackets (<TT>[ ]</TT>) allow a query to search for characters that are contained	within the brackets. For example, <TT>[ABC]</TT> tells the query to search for strings	containing the letters <TT>A</TT>, <TT>B</TT>, or <TT>C</TT>.	<P>	<LI>The <TT>^</TT> character used within the brackets tells a query to look for any	characters that are not listed within the brackets. For example, <TT>[^ABC]</TT>	tells the query to search for strings that do not contain the letters <TT>A</TT>,	<TT>B</TT>, or <TT>C</TT>.</UL><H2><FONT COLOR="#000077">Creating Summarized Reports Using COMPUTE</FONT></H2><P>Transact-SQL also has a mechanism for creating summarized database reports. Thecommand, <TT>COMPUTE</TT>, has very similar syntax to its counterpart in SQL*Plus.(See Day 20, &quot;SQL*Plus.&quot;)</P><P>The following query produces a report showing all batters, the number of homeruns hit by each batter, and the total number of home runs hit by all batters:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF"><B>select name, homerunsfrom batterscompute sum(homeruns)</B></FONT></PRE><H5>ANALYSIS:</H5><P>In the previous example, <TT>COMPUTE</TT> alone performs computations on the reportas a whole, whereas <TT>COMPUTE BY</TT> performs computations on specified groupsand the entire report, as the following example shows:</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">COMPUTE FUNCTION(expression) [BY expression]  where the FUNCTION might include SUM, MAX, MIN, etc. and  EXPRESSION is usually a column name or alias.</FONT></PRE><H2><FONT COLOR="#000077">Date Conversions</FONT></H2><P>Sybase and Microsoft's SQL Server can insert dates into a table in various formats;they can also extract dates in several different types of formats. This section showsyou how to use SQL Server's <TT>CONVERT</TT> command to manipulate the way a dateis displayed.</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">CONVERT (datatype [(length)], expression, format)</FONT></PRE><P>The following date formats are available with SQL Server when using the <TT>CONVERT</TT>function: <BR></P><P><TABLE BORDER="1">	<TR ALIGN="LEFT" rowspan="1">		<TD ALIGN="LEFT" VALIGN="TOP">Format code</TD>		<TD ALIGN="LEFT" VALIGN="TOP">Format picture</TD>	</TR>	<TR>		<TD ALIGN="LEFT" VALIGN="TOP"><TT>100</TT></TD>		<TD ALIGN="LEFT" VALIGN="TOP"><TT>mon dd yyyy hh:miAM/PM</TT></TD>	</TR>	<TR ALIGN="LEFT" rowspan="1">		<TD ALIGN="LEFT" VALIGN="TOP"><TT>101</TT></TD>		<TD ALIGN="LEFT" VALIGN="TOP"><TT>mm/dd/yy</TT></TD>	</TR>	<TR ALIGN="LEFT" rowspan="1">		<TD ALIGN="LEFT" VALIGN="TOP"><TT>102</TT></TD>		<TD ALIGN="LEFT" VALIGN="TOP"><TT>yy.mm.dd</TT></TD>	</TR>	<TR ALIGN="LEFT" rowspan="1">		<TD ALIGN="LEFT" VALIGN="TOP"><TT>103</TT></TD>		<TD ALIGN="LEFT" VALIGN="TOP"><TT>dd/mm/yy</TT></TD>	</TR>	<TR ALIGN="LEFT" rowspan="1">		<TD ALIGN="LEFT" VALIGN="TOP"><TT>104</TT></TD>		<TD ALIGN="LEFT" VALIGN="TOP"><TT>dd.mm.yy</TT></TD>	</TR>	<TR ALIGN="LEFT" rowspan="1">		<TD ALIGN="LEFT" VALIGN="TOP"><TT>105</TT></TD>		<TD ALIGN="LEFT" VALIGN="TOP"><TT>dd-mm-yy</TT></TD>	</TR>	<TR ALIGN="LEFT" rowspan="1">		<TD ALIGN="LEFT" VALIGN="TOP"><TT>106</TT></TD>		<TD ALIGN="LEFT" VALIGN="TOP"><TT>dd mon yy</TT></TD>	</TR>	<TR ALIGN="LEFT" rowspan="1">		<TD ALIGN="LEFT" VALIGN="TOP"><TT>107</TT></TD>		<TD ALIGN="LEFT" VALIGN="TOP"><TT>mon dd, yy</TT></TD>	</TR>	<TR ALIGN="LEFT" rowspan="1">		<TD ALIGN="LEFT" VALIGN="TOP"><TT>108</TT></TD>		<TD ALIGN="LEFT" VALIGN="TOP"><TT>hh:mi:ss</TT></TD>	</TR>	<TR ALIGN="LEFT" rowspan="1">		<TD ALIGN="LEFT" VALIGN="TOP"><TT>109</TT></TD>		<TD ALIGN="LEFT" VALIGN="TOP"><TT>mon dd, yyyy hh:mi:ss:mmmAM/PM</TT></TD>	</TR>	<TR ALIGN="LEFT" rowspan="1">		<TD ALIGN="LEFT" VALIGN="TOP"><TT>110</TT></TD>		<TD ALIGN="LEFT" VALIGN="TOP"><TT>mm-dd-yy</TT></TD>	</TR>	<TR ALIGN="LEFT" rowspan="1">		<TD ALIGN="LEFT" VALIGN="TOP"><TT>111</TT></TD>		<TD ALIGN="LEFT" VALIGN="TOP"><TT>yy/mm/dd</TT></TD>	</TR>	<TR ALIGN="LEFT" rowspan="1">		<TD ALIGN="LEFT" VALIGN="TOP"><TT>112</TT></TD>		<TD VALIGN="TOP"><TT>yymmdd</TT></TD>	</TR></TABLE></P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">select &quot;PayDate&quot; = convert(char(15), paydate, 107)from payment_tablewhere customer_id = 012845</FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT><FONT COLOR="#0066FF"></FONT></H5><PRE><FONT COLOR="#0066FF">PayDate---------------May 1, 1997</FONT></PRE><H5>ANALYSIS:</H5><P>The preceding example uses the format code <TT>107</TT> with the <TT>CONVERT</TT>function. According to the date format table, code <TT>107</TT> will display thedate in the format <TT>mon dd, yy</TT>.<H2><FONT COLOR="#000077">SQL Server Diagnostic Tools--SET Commands</FONT></H2><P>Transact-SQL provides a list of <TT>SET</TT> commands that enable you to turnon various options that help you analyze Transact-SQL statements. Here are some ofthe popular <TT>SET</TT> commands:<UL>	<LI><TT>SET STATISTICS IO ON</TT> tells the server to return the number of logical	and physical page requests.	<P>	<LI><TT>SET STATISTICS TIME ON</TT> tells the server to display the execution time	of an SQL statement.	<P>	<LI><TT>SET SHOWPLAN ON</TT> tells the server to show the execution plan for the	designated query.	<P>	<LI><TT>SET NOEXEC ON</TT> tells the server to parse the designated query, but not	to execute it.	<P>	<LI><TT>SET PARSONLY ON</TT> tells the server to check for syntax for the designated	query, but not to execute it.</UL><P>Transact-SQL also has the following commands that help to control what is displayedas part of the output from your queries:<UL>	<LI><TT>SET ROWCOUNT </TT>n tells the server to display only the first n records	retrieved from a query.	<P>	<LI><TT>SET NOCOUNT ON</TT> tells the server not to report the number of rows returned	by a query.</UL><BLOCKQUOTE>	<P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>If you are concerned with tuning	your SQL statements, refer to Day 15, &quot;Streamlining SQL Statements for Improved	Performance.&quot; <HR></BLOCKQUOTE><H2><FONT COLOR="#000077">Summary</FONT></H2><P>Day 19 introduces a number of topics that add some teeth to your SQL programmingexpertise. The basic SQL topics that you learned earlier in this book are extremelyimportant and provide the foundation for all database programming work you undertake.However, these topics are just a foundation. The SQL procedural language conceptsexplained yesterday and today build on your foundation of SQL. They give you, thedatabase programmer, a great deal of power when accessing data in your relationaldatabase.</P><P>The Transact-SQL language included with the Microsoft and Sybase SQL Server databaseproducts provide many of the programming constructs found in popular third- and fourth-generationlanguages. Its features include the <TT>IF</TT> statement, the <TT>WHILE</TT> loop,and the capability to declare and use local and global variables.</P><P>Keep in mind that Day 19 is a brief introduction to the features and techniquesof Transact-SQL code. Feel free to dive head first into your documentation and experimentwith all the tools that are available to you. For more detailed coverage of Transact-SQL,refer to the Microsoft SQL Server Transact-SQL documentation.<H2><FONT COLOR="#000077">Q&amp;A</FONT></H2><DL>	<DD><B>Q Does SQL provide a <TT>FOR</TT> loop?</B>	<P><B>A</B> Programming constructs such as the <TT>FOR</TT> loop, the <TT>WHILE</TT>	loop, and the <TT>CASE</TT> statement are extensions to ANSI SQL. Therefore, the	use of these items varies widely among database systems. For instance, Oracle provides	the <TT>FOR</TT> loop, whereas Transact-SQL (SQL Server) does not. Of course, a <TT>WHILE</TT>	loop can increment a variable within the loop, which can simulate the <TT>FOR</TT>	loop.</P>	<P><B>Q I am developing a Windows (or Macintosh) application in which the user interface	consists of Windows GUI elements, such as windows and dialog boxes. Can I use the	</B>PRINT<B> statement to issue messages to the user?</B></P>	<P><B>A</B> SQL is entirely platform independent. Therefore, issuing the <TT>PRINT</TT>	statement will not pop up a message box. To output messages to the user, your SQL	procedures can return predetermined values that indicate success or failure. Then	the user can be notified of the status of the queries. (The <TT>PRINT</TT> command	is most useful for debugging because a <TT>PRINT</TT> statement executed within a	stored procedure will not be output to the screen anyway.)</DL><H2><FONT COLOR="#000077">Workshop</FONT></H2><P>The Workshop provides quiz questions to help solidify your understanding of thematerial covered, as well as exercises to provide you with experience in using whatyou have learned. Try to answer the quiz and exercise questions before checking theanswers in Appendix F, &quot;Answers to Quizzes and Exercises.&quot;<H3><FONT COLOR="#000077">Quiz</FONT></H3><DL>	<DD><B>1.</B> True or False: The use of the word SQL in Oracle's PL/SQL and Microsoft/Sybase's	Transact-SQL implies that these products are fully compliant with the ANSI standard.	<P><B>2. </B>True or False: Static SQL is less flexible than Dynamic SQL, although	the performance of static SQL can be better.</DL><H3><FONT COLOR="#000077">Exercises</FONT></H3><DL>	<DD><B>1. </B>If you are not using Sybase/Microsoft SQL Server, compare your product's	extensions to ANSI SQL to the extensions mentioned today.	<P><B>2. </B>Write a brief set of statements that will check for the existence of	some condition. If this condition is true, perform some operation. Otherwise, perform	another operation.</DL><H1></H1><CENTER><P><HR><A HREF="../ch18/ch18.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch20/ch20.htm"><IMGSRC="../buttonart/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"BORDER="0"></A><A HREF="../index.htm"><IMG SRC="../buttonart/contents.gif" WIDTH="128"HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A><BR><BR><BR><IMG SRC="../buttonart/corp.gif" WIDTH="284" HEIGHT="45" ALIGN="BOTTOM" ALT="Macmillan Computer Publishing USA"BORDER="0"></P><P>&#169; <A HREF="../copy.htm">Copyright</A>, Macmillan Computer Publishing. Allrights reserved.</CENTER></BODY></HTML>