Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Fetching contributors…

Cannot retrieve contributors at this time

838 lines (824 sloc) 37.689 kb
<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>
Jump to Line
Something went wrong with that request. Please try again.