File: apiCursors.html

package info (click to toggle)
libjtds-java 1.2.5%2Bdfsg-3
links: PTS, VCS
area: main
in suites: jessie, jessie-kfreebsd, stretch
size: 12,180 kB
ctags: 12,775
sloc: java: 39,611; xml: 702; sh: 34; makefile: 12
file content (837 lines) | stat: -rw-r--r-- 38,526 bytes
parent folder | download | duplicates (5)
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=windows-1252">
<TITLE>SQL Server API Server Cursors</TITLE>
<LINK REL="stylesheet" TYPE="text/css" HREF="sqldoc.css">
</HEAD>
<BODY bgcolor="#FFFFFF">
<h2>System stored procedures</h2>
<p>This document contains information on undocumented stored procedures in Microsoft SQL Server.</p>
<BL>
<table>
<tr><td>&nbsp;</td><th align="left">Name</th><th align="left">Function</th></tr>
<tr><td>&nbsp;</td><td><a href="#_sp_cursor">sp_cursor</a></td><td>Update a cursor</td></tr>
<tr><td>&nbsp;</td><td><a href="#_sp_cursorclose">sp_cursorclose</a></td><td>close a cursor</td></tr>
<tr><td>&nbsp;</td><td><a href="#_sp_cursorexecute">sp_cursorexecute</a></td><td>Open a prepared cursor</td></tr>
<tr><td>&nbsp;</td><td><a href="#_sp_cursorfetch">sp_cursorfetch</a></td><td>Fetch rows</td></tr>
<tr><td>&nbsp;</td><td><a href="#_sp_cursoropen">sp_cursoropen</a></td><td>Open a cursor</td></tr>
<tr><td>&nbsp;</td><td><a href="#_sp_cursoroption">sp_cursoroption</a></td><td>Set cursor options</td></tr>
<tr><td>&nbsp;</td><td><a href="#_sp_cursorprepare">sp_cursorprepare</a></td><td>Prepare a cursor statement</td></tr>
<tr><td>&nbsp;</td><td><a href="#_sp_cursorprepexec">sp_cursorprepexec</a></td><td>Prepare a cursor statement and open</td></tr>
<tr><td>&nbsp;</td><td><a href="#_sp_cursorunprepare">sp_cursorunprepare&nbsp;</a></td><td>Free a prepared cursor statement</td></tr>
<tr><td>&nbsp;</td><td><a href="#_sp_execute">sp_execute</a></td><td>Execute a prepared statement</td></tr>
<tr><td>&nbsp;</td><td><a href="#_sp_prepare">sp_prepare</a></td><td>Prepare an SQL statement</td></tr>
<tr><td>&nbsp;</td><td><a href="#_sp_prepexec">sp_prepexec</a></td><td>Prepare and execute an SQL statement</td></tr>
<tr><td>&nbsp;</td><td><a href="#_sp_unprepare">sp_unprepare</a></td><td>Free a prepared statement</td></tr>
</table>
</BL>

<h2><A NAME="_sp_cursoropen"></A>sp_cursoropen</h2>
<P>Defines the attributes of an API server cursor, such as its scrolling behavior
  and the statement used to build the result set on which the cursor operates,
  then populates the cursor. The statement can contain embedded parameters.</P>
<h5>Syntax</h5>
<P><B>sp_cursoropen</b> [<B>@cursor =</B>] <I>cursor_handle</I> OUTPUT,<BR>
  &nbsp;&nbsp;&nbsp;&nbsp;[<B>@stmt =</B>] '<I>stmt</I>'<BR>
  &nbsp;&nbsp;&nbsp;&nbsp;[, [<B>@scrollopt =</B>] <I>scroll_options</I> OUTPUT]<BR>
  &nbsp;&nbsp;&nbsp;&nbsp;[, [<B>@ccopt =</B>] <I>concurrency_options</I> OUTPUT]<BR>
  &nbsp;&nbsp;&nbsp;&nbsp;[, [<B>@rowcount =</B>] <I>rowcount</I> OUTPUT]</P>
<P>[<BR>
  &nbsp;&nbsp;&nbsp;&nbsp;{, [<B>@paramdef =</B>] N'<I>parameter_name data_type</I>
  [,<i>...n</i>]' }<BR>
  &nbsp;&nbsp;&nbsp;&nbsp;{, [<B>@</B><i>param1</i><B> =</B>] <I>value1</I> [,<i>...n</i>]
  }</P>
<P>]</P>
<h5>Arguments</h5>
<DL>
  <DT>[<B>@cursor =</B>] <I>cursor_handle</I> OUTPUT</DT>
  <DD>Is the name of a declared integer variable to receive the cursor handle.
    <I>cursor_handle</I> is <B>int</B>, with no default.</DD>
  <DT>[<B>@stmt =</B>] '<I>stmt</I>'</DT>
  <DD>
    <P>Is a string containing a single SELECT statement or a single stored procedure
      call. The size of the string is limited only by available database server
      memory. <I>stmt</i> can contain parameters having the same form as a variable
      name, for example:</P>
    <P class="ex"><CODE>'SELECT * FROM Employees WHERE EmployeeID = @IDParameter'</CODE></P>
    <P>Each parameter included in <i>stmt</i> must have a corresponding entry
      in both the <b>@paramdef</b> parameter definition list and the parameter
      values list.</P>
  </DD>
  <DT>[<B>@scrollopt =</B>] <I>scroll_options</I> OUTPUT</DT>
  <DD> Is the cursor scroll type. <I>scroll_options</I> is <B>int</B> with a default
    of 1 (keyset-driven), and can be a combination of these values (exactly one
    of the first 5 must be specified).<BR>
    <BR>
    <table cellspacing="1" cols="2" width="446">
      <TR VALIGN="top">
        <TH>Value</TH>
        <TH>Description</TH>
      </TR>
      <TR VALIGN="top">
        <TD>0x0001</TD>
        <TD>Keyset-driven cursor.</TD>
      </TR>
      <TR VALIGN="top">
        <TD>0x0002</TD>
        <TD>Dynamic cursor.</TD>
      </TR>
      <TR VALIGN="top">
        <TD>0x0004</TD>
        <TD>Forward-only cursor.</TD>
      </TR>
      <TR VALIGN="top">
        <TD>0x0008</TD>
        <TD>Static cursor.</TD>
      </TR>
      <TR VALIGN="top">
        <TD>0x0010</TD>
        <TD>Fast forward-only cursor.</TD>
      </TR>
      <TR VALIGN="top">
        <TD>0x1000</TD>
        <TD>Parameterized query.</TD>
      </TR>
      <TR VALIGN="top">
        <TD>0x2000</TD>
        <TD>Auto fetch.</TD>
      </TR>
      <TR VALIGN="top">
        <TD>0x4000</TD>
        <TD>Auto close.</TD>
      </TR>
      <TR VALIGN="top">
        <TD>0x8000</TD>
        <TD>Check acceptable types.</TD>
      </TR>
      <TR VALIGN="top">
        <TD>0x10000</TD>
        <TD>Keyset-driven acceptable.</TD>
      </TR>
      <TR VALIGN="top">
        <TD>0x20000</TD>
        <TD>Dynamic acceptable.</TD>
      </TR>
      <TR VALIGN="top">
        <TD>0x40000</TD>
        <TD>Forward-only acceptable.</TD>
      </TR>
      <TR VALIGN="top">
        <TD>0x80000</TD>
        <TD>Static acceptable.</TD>
      </TR>
      <TR VALIGN="top">
        <TD>0x100000</TD>
        <TD>Fast forward-only acceptable.</TD>
      </TR>
    </table>
    <BR>
    On return, <b>@scrollopt</b> contains the type of cursor actually created,
    which may not match what was requested.</DD>
  <DT>[<B>@ccopt =</B>] <I>concurrency_options</i> OUTPUT</DT>
  <DD>Is the cursor concurrency. <I>concurrency_options</I> is <B>int</B>, with
    a default of 4 (optimistic) and can be a combination of these values (exactly
    one of the first 4 must be specified).<BR>
    <BR>
    <table cellspacing="1" cols="2" width="446">
      <TR VALIGN="top">
        <TH>Value</TH>
        <TH>Description</TH>
      </TR>
      <TR VALIGN="top">
        <TD>0x0001</TD>
        <TD>Read-only.</TD>
      </TR>
      <TR VALIGN="top">
        <TD>0x0002</TD>
        <TD>Scroll locks.</TD>
      </TR>
      <TR VALIGN="top">
        <TD>0x0004</TD>
        <TD>Optimistic. Checks timestamps and, when not available, values.</TD>
      </TR>
      <TR VALIGN="top">
        <TD>0x0008</TD>
        <TD>Optimistic. Checks values (non-text, non-image).</TD>
      </TR>
      <TR VALIGN="top">
        <TD>0x2000</TD>
        <TD>Open on any SQL.</TD>
      </TR>
      <TR VALIGN="top">
        <TD>0x4000</TD>
        <TD>Update keyset in place.</TD>
      </TR>
      <TR VALIGN="top">
        <TD>0x10000</TD>
        <TD>Read-only acceptable.</TD>
      </TR>
      <TR VALIGN="top">
        <TD>0x20000</TD>
        <TD>Locks acceptable.</TD>
      </TR>
      <TR VALIGN="top">
        <TD>0x40000</TD>
        <TD>Optimistic acceptable.</TD>
      </TR>
    </table>
    <BR>
    On return, <b>@ccopt</b> contains the type of cursor actually created, which
    may not match what was requested.</DD>
  <DT>[<B>@rowcount =</B>] <I>rowcount</I> OUTPUT</DT>
  <DD> Is the name of a declared integer variable to receive the number of affected
    rows. <I>rowcount</I> is <B>int</B> with no default value.</DD>
  <DT>[<B>@paramdef =</B>] N'<i>parameter_name data_type</i> [,<i>...n</i>]'</DT>
  <DD>Is one string that contains the definitions of all parameters that have
    been embedded in <i>stmt</i>. Each parameter definition consists of a parameter
    name and a data type. <i>n</i> is a placeholder indicating additional parameter
    definitions. Every parameter specified in <i>stmt</i> must be defined in <b>@paramdef</b>.
    If the Transact-SQL statement in <i>stmt</i> does not contain parameters,
    <b>@paramdef</b> is not needed. The default value for this parameter is NULL.</DD>
  <DT>[<b>@</b><i>param1</i><b> =</b>] <i>value1</i></DT>
  <DD>Is a value for the first parameter defined in the parameter string. The
    value can be a constant or a variable. There must be a parameter value supplied
    for every parameter included in <i>stmt</i>. The values are not needed if
    the Transact-SQL statement in <i>stmt</i> has no parameters.</DD>
  <DT><i>n</i></DT>
  <DD>Is a placeholder for the values of additional parameters. Values can be
    only constants or variables. Values cannot be more complex expressions such
    as functions, or expressions built using operators.</DD>
</DL>
<h5>Return Code Values</h5>
<P>0 (success) or 1 (failure).</P>
<h5>Result Sets</h5>
<P>Returns the result set generated by <i>stmt</i>, but containing no rows.</P>
<h5>Remarks</h5>
<P><b>sp_cursoropen</b> is a more powerful (and programmatic) way of creating
  server-side cursors on SQL Server.</P>
<h5>Permissions</h5>
<P>Execute permissions default to the <b>public</b> role.</P>
<h5>Examples</h5>
<h5>A. Create a cursor for a simple SELECT statement</h5>
<P>This simple example creates a dynamic read-only cursor for a SELECT statement
  with no parameters.</P>
<P class="ex"><CODE>USE pubs</CODE></P>
<P class="ex"><CODE>&nbsp;</CODE></P>
<P class="ex"><CODE>-- Create a dynamc read-only cursor</CODE></P>
<P class="ex"><CODE>DECLARE @cursor INT</CODE></P>
<P class="ex"><CODE>EXEC sp_cursoropen @cursor OUTPUT, N'SELECT * FROM myTable',
  2, 8193</CODE></P>
<P class="ex"><CODE>&nbsp;</CODE></P>
<P class="ex"><CODE>-- Close the cursor</CODE></P>
<P class="ex"><CODE>EXEC sp_cursorclose @cursor</CODE></P>
<P class="ex"><CODE>&nbsp;</CODE></P>
<h5>B. Create a cursor for a parameterized SELECT statement</h5>
<p>This example creates a dynamic read-only cursor for a SELECT statement with
  2 parameters.</p>
<p class="ex"><code>USE pubs</code></p>
<p class="ex"><code>&nbsp;</code></p>
<p class="ex"><code>-- Create a dynamc read-only cursor</code></p>
<p class="ex"><code>DECLARE @cursor INT</code></p>
<p class="ex"><code>EXEC sp_cursoropen @cursor OUTPUT, N'SELECT * FROM myTable
  WHERE col1=@P1 AND col2 LIKE @P2', 2, 8193, N'@P1 INT, @P2 VARCHAR(255)', 10,
  '%x%' </code></p>
<p class="ex"><code>&nbsp;</code></p>
<p class="ex"><code>-- Close the cursor</code></p>
<p class="ex"><code>EXEC sp_cursorclose @cursor</code></p>
<p class="ex"><code>&nbsp;</code></p>
<h5>C. Create a cursor for a stored procedure call</h5>
<p>This example creates a dynamic read-only cursor for a stored procedure with
  2 parameters (the procedure must return only one result set or the cursor creation
  will fail). Note that output parameters can also be used and return values retrieved
  via output parameters.</p>
<p class="ex"><code>USE pubs</code></p>
<p class="ex"><code>&nbsp;</code></p>
<p class="ex"><code>-- Create a dynamc read-only cursor</code></p>
<p class="ex"><code>DECLARE @cursor INT</code></p>
<p class="ex"><code>DECLARE @retval INT</code></p>
<p class="ex"><code>EXEC sp_cursoropen @cursor OUTPUT, 'EXEC @P1=myProc @P2',
  2, 8193, N'@P1 INT OUTPUT, @P2 INT', @retval, 1</code></p>
<p class="ex"><code>&nbsp;</code></p>
<p class="ex"><code>-- Close the cursor</code></p>
<p class="ex"><code>EXEC sp_cursorclose @cursor</code></p>
<p class="ex"><code>&nbsp;</code></p>
<h2><A NAME="_sp_cursorfetch"></A>sp_cursorfetch</h2>
<P>Fetches a row or block of rows from an API server cursor.</P>
<h5>Syntax</h5>
<P><B>sp_cursorfetch</b> [<B>@cursor =</B>] <I>cursor_handle</i><BR>
  &nbsp;&nbsp;&nbsp;&nbsp;[, [<B>@fetchtype =</B>] <I>fetchtype</I>]<BR>
  &nbsp;&nbsp;&nbsp;&nbsp;[, [<B>@rownum =</B>] <I>rownum</I> OUTPUT]<BR>
  &nbsp;&nbsp;&nbsp;&nbsp;[, [<B>@nrows =</B>] <I>nrows</I> OUTPUT]</P>
<h5>Arguments</h5>
<DL>
  <DT>[<B>@cursor =</B>] <I>cursor_handle</i></DT>
  <DD>Is the cursor handle. <I>cursor_handle</I> is <B>int</B>, with no default.</DD>
  <dt>[<B>@fetchtype =</B>] <I>fetchtype</i></dt>
  <dd>Is the fetch type. <I>fetchtype</I> is <B>int</B>, with a default of 2 and
    can have one of these values.<br>
    <br>
    <table cellspacing="1" cols="2" width="446">
      <TR VALIGN="top">
        <TH>Value</TH>
        <TH>Description</TH>
      </TR>
      <TR VALIGN="top">
        <TD>0x0001</TD>
        <TD>First row.</TD>
      </TR>
      <TR VALIGN="top">
        <TD>0x0002</TD>
        <TD>Next row.</TD>
      </TR>
      <TR VALIGN="top">
        <TD>0x0004</TD>
        <TD>Previous row.</TD>
      </TR>
      <TR VALIGN="top">
        <TD>0x0008</TD>
        <TD>Last row.</TD>
      </TR>
      <TR VALIGN="top">
        <TD>0x0010</TD>
        <TD>Absolute row index.</TD>
      </TR>
      <TR VALIGN="top">
        <TD>0x0020</TD>
        <TD>Relative row index.</TD>
      </TR>
      <TR VALIGN="top">
        <TD>0x0040</TD>
        <TD>By value (???).</TD>
      </TR>
      <TR VALIGN="top">
        <TD>0x0080</TD>
        <TD>Refresh.</TD>
      </TR>
      <TR VALIGN="top">
        <TD>0x0100</TD>
        <TD>Result set info.</TD>
      </TR>
      <TR VALIGN="top">
        <TD>0x0200</TD>
        <TD>Previous noadjust (?).</TD>
      </TR>
      <TR VALIGN="top">
        <TD>0x0400</TD>
        <TD>Skip update concurrency (???).</TD>
      </TR>
    </table>
  </dd>
  <dt>[<B>@rownum =</B>] <I>rownum</I> OUTPUT</dt>
  <dd>Is the row number. <I>rownum</I> is <B>int</B>, with a default of NULL.</dd>
  <dt>[<B>@nrows =</B>] <I>nrows</I> OUTPUT</dt>
  <dd>Is the number of rows to fetch. <I>nrows</I> is <B>int</B>, with a default
    of NULL (fetch all rows).</dd>
</DL>
<h5>Return Code Values</h5>
<P>0 (success) or 1 (failure).</P>
<h5>Result Sets</h5>
<P>Returns the requested row or group of rows from the cursor.</P>
<h5>Remarks</h5>
<P>In addition to fetching rows, the 'result set info' fetch type can be used
  to retrieve information about the cursor (current row in <b>@rownum</b> and
  total number of rows in <b>@nrows</b>).</P>
<h5>Permissions</h5>
<P>Execute permissions default to the <b>public</b> role.</P>
<h5>Example</h5>
<P class="ex"><CODE>USE pubs</CODE></P>
<P class="ex"><CODE>&nbsp;</CODE></P>
<P class="ex"><CODE>-- Create a dynamc read-only cursor </CODE></P>
<P class="ex"><CODE>DECLARE @cursor INT</CODE></P>
<P class="ex"><CODE>EXEC sp_cursoropen @cursor OUTPUT, 'SELECT * FROM myTable',
  2, 8193</CODE></P>
<P class="ex"><CODE>&nbsp;</CODE></P>
<P class="ex"><CODE>-- Fetch the next 3 lines</CODE></P>
<P class="ex"><CODE>EXEC sp_cursorfetch @cursor, 2, 0, 3</CODE></P>
<P class="ex"><CODE>&nbsp;</CODE></P>
<P class="ex"><CODE>-- Close the cursor</CODE></P>
<P class="ex"><CODE>EXEC sp_cursorclose @cursor</CODE></P>
<P class="ex"><CODE>&nbsp;</CODE></P>

<h2><A NAME="_sp_cursorclose"></A>sp_cursorclose</h2>
<P>Closes ande deallocates an API server cursor.</P>
<h5>Syntax</h5>
<P><B>sp_cursorclose</b> [<B>@cursor =</B>] <I>cursor_handle</i></P>
<h5>Arguments</h5>
<dl>
  <dt>[<B>@cursor =</B>] <I>cursor_handle</i></dt>
  <dd>Is a cursor handle obtained by calling <B>sp_cursorcreate</B>. <I>cursor_handle</I>
    is <B>int</B>, with no default.</dd>
</dl>
<h5>Return Code Values</h5>
<P>0 (success) or 1 (failure).</P>
<h5>Result Sets</h5>
<P>None.</P>
<h5>Permissions</h5>
<P>Execute permissions default to the <b>public</b> role.</P>
<h5>Example</h5>
<P class="ex"><CODE>USE pubs</CODE></P>
<P class="ex"><CODE>&nbsp;</CODE></P>
<P class="ex"><CODE>-- Create a dynamic read-only cursor </CODE></P>
<P class="ex"><CODE>DECLARE @cursor INT</CODE></P>
<P class="ex"><CODE>EXEC sp_cursoropen @cursor OUTPUT, 'SELECT * FROM myTable',
  2, 8193</CODE></P>
<P class="ex"><CODE>&nbsp;</CODE></P>
<P class="ex"><CODE>-- Close the cursor</CODE></P>
<P class="ex"><CODE>EXEC sp_cursorclose @cursor</CODE></P>
<P class="ex"><CODE>&nbsp;</CODE></P>

<h2><A NAME="_sp_cursoroption"></A>sp_cursoroption</h2>
<P>Sets various options for API server cursors.</P>
<h5>Syntax</h5>
<P><B>sp_cursoroption</b> [<B>@cursor =</B>] <I>cursor_handle</i>,<BR>
  &nbsp;&nbsp;&nbsp;&nbsp;[<B>@code =</B>] <I>code</i>,<BR>
  &nbsp;&nbsp;&nbsp;&nbsp;{ [<B>@value =</B>] <I>value</i><BR>
  &nbsp;&nbsp;&nbsp;&nbsp;| [<B>@cursorname =</B>] <I>cursorname</I> }</P>
<h5>Arguments</h5>
<dl>
  <dt>[<B>@cursor =</B>] <I>cursor_handle</i></dt>
  <dd>Is a cursor handle obtained by calling <B>sp_cursorcreate</B>. <I>cursor_handle</I>
    is <B>int</B>, with no default.</dd>
  <dt>[<B>@code =</B>] <I>code</i></dt>
  <dd>Is the option code. <I>code</I> is <B>int</B>, with no default and can be
    one of these values.<br>
	<br>
    <table cellspacing="1" cols="2" width="446">
      <TR VALIGN="top">
        <TH>Value</TH>
        <TH>Description</TH>
      </TR>
      <TR VALIGN="top">
        <TD>1</TD>
        <TD>Only return the TEXTPTR of the LOB column specified by <i>value</i></TD>
      </TR>
      <TR VALIGN="top">
        <TD>2</TD>
        <TD>Set cursor name.</TD>
      </TR>
    </table>
  </dd>
  <dt>[<B>@value =</B>] <I>value</i></dt>
  <dd>Is the value of the selected option (for option 1 it's the index of the
    LOB column). <I>value</I> is <B>int</B>, with no default.</dd>
  <dt>[<B>@cursorname =</B>] <I>cursorname</i></dt>
  <dd>Is the name for the cursor. <I>cursorname</I> is <B>sysname</B>,
    with no default.</dd>
</dl>
<h5>Return Code Values</h5>
<P>0 (success) or 1 (failure).</P>
<h5>Result Sets</h5>
<P>None.</P>
<h5>Permissions</h5>
<P>Execute permissions default to the <b>public</b> role.</P>
<h5>Example</h5>
<P class="ex"><CODE>USE pubs</CODE></P>
<P class="ex"><CODE>&nbsp;</CODE></P>
<P class="ex"><CODE>-- Create a dynamc cursor </CODE></P>
<P class="ex"><CODE>DECLARE @cursor INT</CODE></P>
<P class="ex"><CODE>EXEC sp_cursoropen @cursor OUTPUT, 'SELECT * FROM myTable',
  2, 8193</CODE></P>
<P class="ex"><CODE>&nbsp;</CODE></P>
<P class="ex"><CODE>-- Name the cursor</CODE></P>
<P class="ex"><CODE>EXEC sp_cursoroption @cursor, 2, 'myCursor'</CODE></P>
<P class="ex"><CODE>&nbsp;</CODE></P>
<P class="ex"><code>-- Use a cursor variable to access the cursor</code></P>
<P class="ex"><CODE>DECLARE @x CURSOR</CODE></P>
<P class="ex"><CODE>EXEC sp_describe_cursor @x out, N'global', 'myCursor'</CODE></P>
<P class="ex"><CODE>FETCH NEXT FROM @x</CODE></P>
<P class="ex"><CODE>&nbsp;</CODE></P>
<P class="ex"><code>-- Use the cursor directly by name</code></P>
<P class="ex"><code>FETCH NEXT FROM myCursor</code></P>
<P class="ex"><CODE>&nbsp;</CODE></P>
<P class="ex"><CODE>-- Close the cursor</CODE></P>
<P class="ex"><CODE>EXEC sp_cursorclose @cursor</CODE></P>
<P class="ex"><CODE>&nbsp;</CODE></P>

<h2><A NAME="_sp_cursor"></A>sp_cursor</h2>
<P>Can be used to request inserts and positioned updates or deletes on API server
  cursors.</P>
<h5>Syntax</h5>
<P><B>sp_cursor</b> [<B>@cursor =</B>] <I>cursor_handle</i>,<BR>
&nbsp;&nbsp;&nbsp;&nbsp;[<B>@optype =</B>] <I>optype</i>,<BR>
&nbsp;&nbsp;&nbsp;&nbsp;[<B>@rownum =</B>] <I>rownum</i>,<BR>
  &nbsp;&nbsp;&nbsp;&nbsp;[<B>@table =</B>] '<I>table</i>'<BR>
  &nbsp;&nbsp;&nbsp;&nbsp;{, [<B>@</B><i>param1</i><B> =</B>] <I>value1</I> [,<i>...n</i>]
  } </P>
<h5>Arguments</h5>
<dl>
  <dt>[<B>@cursor =</B>] <I>cursor_handle</i></dt>
  <dd>Is a cursor handle obtained by calling <B>sp_cursorcreate</B>. <I>cursor_handle</I>
    is <B>int</B>, with no default.</dd>
  <dt>[<B>@optype =</B>] <I>optype</i></dt>
  <dd> Is a the operation to perform. <I>optype</I> is <B>int</B>, with no default
    and can be one of these values.<BR>
    <BR>
    <table cellspacing="1" cols="2" width="446">
      <TR VALIGN="top">
        <TH>Value</TH>
        <TH>Description</TH>
      </TR>
      <TR VALIGN="top">
        <TD>1</TD>
        <TD>Update row (?).</TD>
      </TR>
      <TR VALIGN="top">
        <TD>4</TD>
        <TD>Insert row.</TD>
      </TR>
      <TR VALIGN="top">
        <TD>33</TD>
        <TD>Update row.</TD>
      </TR>
      <TR VALIGN="top">
        <TD>34</TD>
        <TD>Delete row.</TD>
      </TR>
    </table>
  </dd>
  <dt>[<B>@rownum =</B>] <I>rownum</i></dt>
  <dd>Is the number of the row to update in the fetch cache. <i>rownum</i> is
    <b>int</b>, with no default.</dd>
  <dt>[<B>@table =</B>] '<I>table</i>'</dt>
  <dd> Is the name of the table to update (an empty character string seems to
    be ok). <I>table</I> is <B>sysname</B>, with no default.</dd>
</dl>
<h5>Return Code Values</h5>
<P>0 (success) or 1 (failure).</P>
<h5>Result Sets</h5>
<P>None.</P>
<h5>Permissions</h5>
<P>Execute permissions default to the <b>public</b> role.</P>
<h5>Example</h5>
<P class="ex"><CODE>USE pubs</CODE></P>
<P class="ex"><CODE>&nbsp;</CODE></P>
<P class="ex"><CODE>-- Create a dynamc cursor </CODE></P>
<P class="ex"><CODE>DECLARE @cursor INT</CODE></P>
<P class="ex"><CODE>EXEC sp_cursoropen @cursor OUTPUT, 'SELECT * FROM myTable',
  2, 8193</CODE></P>
<P class="ex"><CODE>&nbsp;</CODE></P>
<P class="ex"><CODE>-- Fetch the next 2 lines; this puts lines 1 and 2 in the
  fetch buffer</CODE></P>
<P class="ex"><CODE>EXEC sp_cursorfetch @cursor, 2, 0, 2</CODE></P>
<P class="ex"><CODE>&nbsp;</CODE></P>
<p class="ex"><code>-- Update the second line in the fetch buffer</code></p>
<p class="ex"><code>EXEC sp_cursor @cursor, 33, 2, '', @intCol=5, @charCol='x'</code></p>
<P class="ex"><CODE>&nbsp;</CODE></P>
<P class="ex"><CODE>-- Close the cursor</CODE></P>
<P class="ex"><CODE>EXEC sp_cursorclose @cursor</CODE></P>
<P class="ex"><CODE>&nbsp;</CODE></P>

<h2><A NAME="_sp_cursorprepare"></A>sp_cursorprepare</h2>
<P>Used to prepare a parameterized cursor statement.</P>
<h5>Syntax</h5>
<P><B>sp_cursorprepare</b> [<B>@cursor =</B>] <I>statement_handle</I> OUTPUT,<BR>
  &nbsp;&nbsp;&nbsp;&nbsp; [<B>@paramdef =</B>] N'<I>parameter_name data_type</I>
  [,<i>...n</i>]', <BR>
  &nbsp;&nbsp;&nbsp;&nbsp; [<B>@stmt =</B>] N'<I>stmt</I>',<BR>
  &nbsp;&nbsp;&nbsp;&nbsp; [<B>@options =</B>] <I>options</I>, <BR>
  &nbsp;&nbsp;&nbsp;&nbsp;[, [<B>@scrollopt =</B>] <I>scroll_options</I> OUTPUT]<BR>
  &nbsp;&nbsp;&nbsp;&nbsp;[, [<B>@ccopt =</B>] <I>concurrency_options</I> OUTPUT]<BR>
<h5>Arguments</h5>
<dl>
  <dt>[<B>@cursor =</B>] <I>statement_handle</i></dt>
  <DD>Is the name of a declared integer variable to receive the statement handle.
    <I>statement_handle</I> is <B>int</B>, with no default.</DD>
  <DT>[<B>@paramdef =</B>] N'<i>parameter_name data_type</i> [,<i>...n</i>]'</DT>
  <DD>Is one string that contains the definitions of all parameters that have
    been embedded in <i>stmt</i>. Each parameter definition consists of a parameter
    name and a data type. <i>n</i> is a placeholder indicating additional parameter
    definitions. Every parameter specified in <i>stmt</i> must be defined in <b>@paramdef</b>. </DD>
  <DT>[<B>@stmt =</B>] '<I>stmt</I>'</DT>
  <DD>
    <P>Is a string containing a single SELECT statement or a single stored procedure
      call. The size of the string is limited only by available database server
      memory. <I>stmt</i> can contain parameters having the same form as a variable
      name, for example:</P>
    <P class="ex"><CODE>'SELECT * FROM Employees WHERE EmployeeID = @IDParameter'</CODE></P>
    <P>Each parameter included in <i>stmt</i> must have a corresponding entry
      in both the <b>@paramdef</b> parameter definition list.</P>
  </DD>
  <dt>[<B>@options =</B>] <I>options</I></dt>
  <DD>An integer value. The exact function of this parameter is unknown.
    <I>options</I> is <B>int</B>, with a value of 1.</DD>
  <DT>[<B>@scrollopt =</B>] <I>scroll_options</I> OUTPUT</DT>
  <DD> Is the cursor scroll type. <I>scroll_options</I> is <B>int</B> with a default
    of 1 (keyset-driven). See sp_cursoropen for more information.
   <BR>
    On return, <b>@scrollopt</b> contains the type of cursor actually created,
    which may not match what was requested.</DD>
  <DT>[<B>@ccopt =</B>] <I>concurrency_options</i> OUTPUT</DT>
  <DD>Is the cursor concurrency. <I>concurrency_options</I> is <B>int</B>, with
    a default of 4 (optimistic). See sp_cursoropen for more information.
  <BR>
    On return, <b>@ccopt</b> contains the type of cursor actually created, which
    may not match what was requested.</DD>
</dl>
<h5>Return Code Values</h5>
<P>0 (success) or 1 (failure).</P>
<h5>Result Sets</h5>
<P>None.</P>
<h5>Permissions</h5>
<P>Execute permissions default to the <b>public</b> role.</P>
<h5>Example</h5>

<h2><A NAME="_sp_cursorprepexec"></A>sp_cursorprepexec</h2>
<P>Used to prepare and open a parameterized cursor statement. This command combines the
functions of the sp_cursorprepare and sp_cursorexecute procedures and is available from SQL2000 onwards.</P>
<h5>Syntax</h5>
<P><B>sp_cursorprepexec</b> [<B>@handle =</B>] <I>statement_handle</I> OUTPUT,<BR>
  &nbsp;&nbsp;&nbsp;&nbsp;  [<B>@cursor =</B>] <I>cursor_handle</I> OUTPUT,<BR>
  &nbsp;&nbsp;&nbsp;&nbsp;  [<B>@paramdef =</B>] N'<I>parameter_name data_type,</I>
  [,<i>...n</i>]' <BR>
  &nbsp;&nbsp;&nbsp;&nbsp;  [<B>@stmt =</B>] N'<I>stmt</I>',<BR>
  &nbsp;&nbsp;&nbsp;&nbsp;[, [<B>@scrollopt =</B>] <I>scroll_options</I> OUTPUT]<BR>
  &nbsp;&nbsp;&nbsp;&nbsp;[, [<B>@ccopt =</B>] <I>concurrency_options</I> OUTPUT]<BR>
  &nbsp;&nbsp;&nbsp;&nbsp;[, [<B>@rowcount =</B>] <I>rowcount</I> OUTPUT]</P>
<h5>Arguments</h5>
<dl>
  <dt>[<B>@handle =</B>] <I>statement_handle</i></dt>
  <DD>Is the name of a declared integer variable to receive the statement handle.
    <I>statement_handle</I> is <B>int</B>, with no default.</DD>
  <DT>[<B>@cursor =</B>] <I>cursor_handle</I> OUTPUT</DT>
  <DD>Is the name of a declared integer variable to receive the cursor handle.
    <I>cursor_handle</I> is <B>int</B>, with no default.</DD>
  <DT>[<B>@paramdef =</B>] N'<i>parameter_name data_type</i> [,<i>...n</i>]'</DT>
  <DD>Is one string that contains the definitions of all parameters that have
    been embedded in <i>stmt</i>. Each parameter definition consists of a parameter
    name and a data type. <i>n</i> is a placeholder indicating additional parameter
    definitions. Every parameter specified in <i>stmt</i> must be defined in <b>@paramdef</b>. </DD>
  <DT>[<B>@stmt =</B>] '<I>stmt</I>'</DT>
  <DD>
    <P>Is a string containing a single SELECT statement or a single stored procedure
      call. The size of the string is limited only by available database server
      memory. <I>stmt</i> can contain parameters having the same form as a variable
      name, for example:</P>
    <P class="ex"><CODE>'SELECT * FROM Employees WHERE EmployeeID = @IDParameter'</CODE></P>
    <P>Each parameter included in <i>stmt</i> must have a corresponding entry
      in both the <b>@paramdef</b> parameter definition list.</P>
  </DD>
  <DT>[<B>@scrollopt =</B>] <I>scroll_options</I> OUTPUT</DT>
  <DD> Is the cursor scroll type. <I>scroll_options</I> is <B>int</B> with a default
    of 1 (keyset-driven). See sp_cursoropen for more information.
   <BR>
    On return, <b>@scrollopt</b> contains the type of cursor actually created,
    which may not match what was requested.</DD>
  <DT>[<B>@ccopt =</B>] <I>concurrency_options</i> OUTPUT</DT>
  <DD>Is the cursor concurrency. <I>concurrency_options</I> is <B>int</B>, with
    a default of 4 (optimistic). See sp_cursoropen for more information.
  <BR>
    On return, <b>@ccopt</b> contains the type of cursor actually created, which
    may not match what was requested.</DD>
  <DT>[<B>@rowcount =</B>] <I>rowcount</I> OUTPUT</DT>
  <DD> Is the name of a declared integer variable to receive the number of affected
    rows. <I>rowcount</I> is <B>int</B> with no default value.</DD>
</dl>
<h5>Return Code Values</h5>
<P>0 (success) or 1 (failure).</P>
<h5>Result Sets</h5>
<P>Returns the result set generated by <i>stmt</i>, but containing no rows.</P>
<h5>Permissions</h5>
<P>Execute permissions default to the <b>public</b> role.</P>
<h5>Example</h5>

<h2><A NAME="_sp_cursorexecute"></A>sp_cursorexecute</h2>
<P>Used to execute (open) a prepared cursor statement.</P>
<h5>Syntax</h5>
<P><B>sp_cursorexecute</b> [<B>@handle =</B>] <I>statement_handle</I>,<BR>
  &nbsp;&nbsp;&nbsp;&nbsp;  [<B>@cursor =</B>] <I>cursor_handle</I> OUTPUT,<BR>
  &nbsp;&nbsp;&nbsp;&nbsp;[, [<B>@scrollopt =</B>] <I>scroll_options</I> OUTPUT]<BR>
  &nbsp;&nbsp;&nbsp;&nbsp;[, [<B>@ccopt =</B>] <I>concurrency_options</I> OUTPUT]<BR>
  &nbsp;&nbsp;&nbsp;&nbsp;[, [<B>@rowcount =</B>] <I>rowcount</I> OUTPUT]<BR>
  &nbsp;&nbsp;&nbsp;&nbsp;{, [<B>@</B><i>param1</i><B> =</B>] <I>value1</I> [,<i>...n</i>]
  }</P>
<h5>Arguments</h5>
<dl>
  <dt>[<B>@handle =</B>] <I>statement_handle</i></dt>
  <DD>Is the integer value of the statement handle.
    <I>statement_handle</I> is <B>int</B>, with no default.</DD>
  <DT>[<B>@cursor =</B>] <I>cursor_handle</I> OUTPUT</DT>
  <DD>Is the name of a declared integer variable to receive the cursor handle.
    <I>cursor_handle</I> is <B>int</B>, with no default.</DD>
  <DT>[<B>@scrollopt =</B>] <I>scroll_options</I> OUTPUT</DT>
  <DD> Is the cursor scroll type. <I>scroll_options</I> is <B>int</B> with a default
    of 1 (keyset-driven). See sp_cursoropen for more information.
   <BR>
    On return, <b>@scrollopt</b> contains the type of cursor actually created,
    which may not match what was requested.</DD>
  <DT>[<B>@ccopt =</B>] <I>concurrency_options</i> OUTPUT</DT>
  <DD>Is the cursor concurrency. <I>concurrency_options</I> is <B>int</B>, with
    a default of 4 (optimistic). See sp_cursoropen for more information.
  <BR>
    On return, <b>@ccopt</b> contains the type of cursor actually created, which
    may not match what was requested.</DD>
  <DT>[<B>@rowcount =</B>] <I>rowcount</I> OUTPUT</DT>
  <DD> Is the name of a declared integer variable to receive the number of affected
    rows. <I>rowcount</I> is <B>int</B> with no default value.</DD>
  <DT>[<b>@</b><i>param1</i><b> =</b>] <i>value1</i></DT>
  <DD>Is a value for the first parameter defined in the parameter string. The
    value can be a constant or a variable. There must be a parameter value supplied
    for every parameter included in <i>stmt</i>. The values are not needed if
    the Transact-SQL statement in <i>stmt</i> has no parameters.</DD>
  <DT><i>n</i></DT>
  <DD>Is a placeholder for the values of additional parameters. Values can be
    only constants or variables. Values cannot be more complex expressions such
    as functions, or expressions built using operators.</DD>
</dl>
<h5>Return Code Values</h5>
<P>0 (success) or 1 (failure).</P>
<h5>Result Sets</h5>
<P>Returns the result set generated by the prepared statement <i>handle</i>, but containing no rows.</P>
<h5>Permissions</h5>
<P>Execute permissions default to the <b>public</b> role.</P>
<h5>Example</h5>

<h2><A NAME="_sp_cursorunprepare"></A>sp_cursorunprepare</h2>
<P>Used to free a prepared cursor statement.</P>
<h5>Syntax</h5>
<P><B>sp_cursorunprepare</b> [<B>@handle =</B>] <I>statement_handle</I></P>
<h5>Arguments</h5>
<dl>
  <dt>[<B>@handle =</B>] <I>statement_handle</i></dt>
  <DD>Is the integer value of the statement handle.
    <I>statement_handle</I> is <B>int</B>, with no default.</DD>
</dl>
<h5>Return Code Values</h5>
<P>0 (success) or 1 (failure).</P>
<h5>Result Sets</h5>
<P>None.</P>
<h5>Permissions</h5>
<P>Execute permissions default to the <b>public</b> role.</P>
<h5>Example</h5>

<h2><A NAME="_sp_prepare"></A>sp_prepare</h2>
<P>Used to prepare a parameterized SQL statement.</P>
<h5>Syntax</h5>
<P><B>sp_prepare</b> [<B>@handle =</B>] <I>statement_handle</I> OUTPUT,<BR>
  &nbsp;&nbsp;&nbsp;&nbsp; [<B>@paramdef =</B>] N'<I>parameter_name data_type</I>
  [,<i>...n</i>]', <BR>
  &nbsp;&nbsp;&nbsp;&nbsp; [<B>@stmt =</B>] N'<I>stmt</I>',<BR>
  &nbsp;&nbsp;&nbsp;&nbsp; [<B>@flag =</B>] <I>flag</I>, <BR>
<h5>Arguments</h5>
<dl>
  <dt>[<B>@handle =</B>] <I>statement_handle</i></dt>
  <DD>Is the name of a declared integer variable to receive the statement handle.
    <I>statement_handle</I> is <B>int</B>, with no default.</DD>
  <DT>[<B>@paramdef =</B>] N'<i>parameter_name data_type</i> [,<i>...n</i>]'</DT>
  <DD>Is one string that contains the definitions of all parameters that have
    been embedded in <i>stmt</i>. Each parameter definition consists of a parameter
    name and a data type. <i>n</i> is a placeholder indicating additional parameter
    definitions. Every parameter specified in <i>stmt</i> must be defined in <b>@paramdef</b>. </DD>
  <DT>[<B>@stmt =</B>] '<I>stmt</I>'</DT>
  <DD>
    <P>Is a string containing a single SELECT statement or a single stored procedure
      call. The size of the string is limited only by available database server
      memory. <I>stmt</i> can contain parameters having the same form as a variable
      name, for example:</P>
    <P class="ex"><CODE>'SELECT * FROM Employees WHERE EmployeeID = @IDParameter'</CODE></P>
    <P>Each parameter included in <i>stmt</i> must have a corresponding entry
      in both the <b>@paramdef</b> parameter definition list.</P>
  </DD>
  <dt>[<B>@flag =</B>] <I>flag</i></dt>
  <DD>An integer value. The exact function of this flag is unknown.
    <I>flag</I> is <B>int</B>, with value of 1.</DD>
</dl>
<h5>Return Code Values</h5>
<P>0 (success) or 1 (failure).</P>
<h5>Result Sets</h5>
<P>None.</P>
<h5>Permissions</h5>
<P>Execute permissions default to the <b>public</b> role.</P>
<h5>Example</h5>

<h2><A NAME="_sp_execute"></A>sp_execute</h2>
<P>Used to execute a prepared SQL statement.</P>
<h5>Syntax</h5>
<P><B>sp_execute</b> [<B>@handle =</B>] <I>statement_handle</I><BR>
  &nbsp;&nbsp;&nbsp;&nbsp;{, [<B>@</B><i>param1</i><B> =</B>] <I>value1</I> [,<i>...n</i>]
  }</P>
<h5>Arguments</h5>
<dl>
  <dt>[<B>@handle =</B>] <I>statement_handle</i></dt>
  <DD>Is the integer value of the statement handle.
    <I>statement_handle</I> is <B>int</B>, with no default.</DD>
  <DT>[<b>@</b><i>param1</i><b> =</b>] <i>value1</i></DT>
  <DD>Is a value for the first parameter defined in the parameter string. The
    value can be a constant or a variable. There must be a parameter value supplied
    for every parameter included in prepared statement <i>handle</i>. The values are not needed if
    the Transact-SQL statement in <i>stmt</i> has no parameters.</DD>
  <DT><i>n</i></DT>
  <DD>Is a placeholder for the values of additional parameters. Values can be
    only constants or variables. Values cannot be more complex expressions such
    as functions, or expressions built using operators.</DD>
</dl>
<h5>Return Code Values</h5>
<P>0 (success) or 1 (failure).</P>
<h5>Result Sets</h5>
<P>Returns a result set if specified by the prepared statement.</P>
<h5>Permissions</h5>
<P>Execute permissions default to the <b>public</b> role.</P>
<h5>Example</h5>

<h2><A NAME="_sp_unprepare"></A>sp_unprepare</h2>
<P>Used to free a prepared SQL statement.</P>
<h5>Syntax</h5>
<P><B>sp_unprepare</b> [<B>@handle =</B>] <I>statement_handle</I></P>
<h5>Arguments</h5>
<dl>
  <dt>[<B>@handle =</B>] <I>statement_handle</i></dt>
  <DD>Is the integer value of the statement handle.
    <I>statement_handle</I> is <B>int</B>, with no default.</DD>
</dl>
<h5>Return Code Values</h5>
<P>0 (success) or 1 (failure).</P>
<h5>Result Sets</h5>
<P>None.</P>
<h5>Permissions</h5>
<P>Execute permissions default to the <b>public</b> role.</P>
<h5>Example</h5>

<h2><A NAME="_sp_prepexec"></A>sp_prepexec</h2>
<P>Used to prepare and execute a parameterized SQL statement. This command combines the
functions of the sp_prepare and sp_execute procedures and is available from SQL2000 onwards.</P>
<h5>Syntax</h5>
<P><B>sp_prepexec</b> [<B>@handle =</B>] <I>statement_handle</I> OUTPUT,<BR>
  &nbsp;&nbsp;&nbsp;&nbsp;  [<B>@paramdef =</B>] N'<I>parameter_name data_type,</I>
  [,<i>...n</i>]' <BR>
  &nbsp;&nbsp;&nbsp;&nbsp;  [<B>@stmt =</B>] N'<I>stmt</I>',<BR>
  &nbsp;&nbsp;&nbsp;&nbsp;{, [<B>@</B><i>param1</i><B> =</B>] <I>value1</I> [,<i>...n</i>]
  }</P>
<h5>Arguments</h5>
<dl>
  <dt>[<B>@handle =</B>] <I>statement_handle</i></dt>
  <DD>Is the name of a declared integer variable to receive the statement handle.
    <I>statement_handle</I> is <B>int</B>, with no default.</DD>
  <DT>[<B>@paramdef =</B>] N'<i>parameter_name data_type</i> [,<i>...n</i>]'</DT>
  <DD>Is one string that contains the definitions of all parameters that have
    been embedded in <i>stmt</i>. Each parameter definition consists of a parameter
    name and a data type. <i>n</i> is a placeholder indicating additional parameter
    definitions. Every parameter specified in <i>stmt</i> must be defined in <b>@paramdef</b>. </DD>
  <DT>[<B>@stmt =</B>] '<I>stmt</I>'</DT>
  <DD>
    <P>Is a string containing a valid SQL statement. The size of the string is limited only by available database server
      memory. <I>stmt</i> can contain parameters having the same form as a variable
      name, for example:</P>
    <P class="ex"><CODE>'SELECT * FROM Employees WHERE EmployeeID = @IDParameter'</CODE></P>
    <P>Each parameter included in <i>stmt</i> must have a corresponding entry
      in both the <b>@paramdef</b> parameter definition list.</P>
  </DD>
  <DT>[<b>@</b><i>param1</i><b> =</b>] <i>value1</i></DT>
  <DD>Is a value for the first parameter defined in the parameter string. The
    value can be a constant or a variable. There must be a parameter value supplied
    for every parameter included in <i>stmt</i>. The values are not needed if
    the Transact-SQL statement in <i>stmt</i> has no parameters.</DD>
  <DT><i>n</i></DT>
  <DD>Is a placeholder for the values of additional parameters. Values can be
    only constants or variables. Values cannot be more complex expressions such
    as functions, or expressions built using operators.</DD>
</dl>
<h5>Return Code Values</h5>
<P>0 (success) or 1 (failure).</P>
<h5>Result Sets</h5>
<P>Returns a result set if specified by the prepared statement.</P>
<h5>Permissions</h5>
<P>Execute permissions default to the <b>public</b> role.</P>
<h5>Example</h5>

</BODY>
</HTML>