sp_cursoropen的动态传参用法

最新推荐文章于 2024-11-24 17:21:34 发布

原创最新推荐文章于 2024-11-24 17:21:34 发布 · 7.7k 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#output #scroll #microsoft #insert #concurrency #sql server

MSSQL 专栏收录该内容

86 篇文章

订阅专栏

本文介绍如何在 SQL Server 中通过 sp_cursoropen 使用动态 SQL 传递参数，并详细解释了 scroll_options 参数的设置方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

sp_cursoropen的动态传参用法

------------------------------------------------------------------------

-- Author: happyflystone

-- Date : 2009-04-29 12:23:00

-- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)

-- Apr 14 2006 01:12:25

-- Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

-- 转载注明出处

-- blog.youkuaiyun.com/happyflystone

------------------------------------------------------------------------

测试环境准备：

CREATE TABLE T2(A INT,B INT)

INSERT INTO T2 SELECT 3,4

INSERT INTO T2 SELECT 2,6

INSERT INTO T2 SELECT 8,2

INSERT INTO T2 SELECT 1,4

常规下直接取得T2表的所有数据，相信大家都会吧，下面我简单测试一下

DECLARE @cursor INT

DECLARE @rowcount INT

EXEC sp_cursoropen @cursor OUTPUT, N'SELECT * FROM t2', 2, 8193

EXEC sp_cursorfetch @cursor, 2, 0, 4

EXEC sp_cursorclose @cursor

a b

----------- -----------

a b

----------- -----------

3 4

2 6

8 2

1 4

好，下面就是如果要想仅返回a = 2的记录呢？我们看帮助

Syntax

sp_cursoropen [@cursor =] cursor_handle OUTPUT,
    [@stmt =] 'stmt'
    [, [@scrollopt =] scroll_options OUTPUT]
    [, [@ccopt =] concurrency_options OUTPUT]
    [, [@rowcount =] rowcount OUTPUT]

[
{, [@paramdef =] N'parameter_name data_type [,...n]' }
{, [@param1 =] value1 [,...n] }

]

看来也不难呀，动态SQL写了那么多照着写就是了，好，我们试试看

DECLARE @cursor INT

DECLARE @rowcount INT

EXEC sp_cursoropen @cursor OUTPUT , N'SELECT * FROM t2 where a=@id'

, 2 , 8193 , @rowcount output ,N'@id int' ,@id=2

EXEC sp_cursorfetch @cursor, 2, 0, 4

EXEC sp_cursorclose @cursor

正在直接执行SQL；无游标。

消息137，级别15，状态2，第1 行

必须声明标量变量"@id"。

消息16909，级别16，状态1，过程sp_cursorfetch，第1 行

sp_cursorfetch: 所提供的游标标识符值(0)无效。

消息16909，级别16，状态1，过程sp_cursorclose，第1 行

sp_cursorclose: 所提供的游标标识符值(0)无效。

看来没有得到我们想的结果吧，这原因出在哪儿哟？

下面我先给出正常结果：

DECLARE @cursor INT

DECLARE @rowcount INT

EXEC sp_cursoropen @cursor OUTPUT

, N'SELECT * FROM t2 where a=@id'

, 135170

, 8193

, @rowcount output

,N'@id int'

,@id=2

EXEC sp_cursorfetch @cursor, 2, 0, 4

EXEC sp_cursorclose @cursor

a b

----------- -----------

a b

----------- -----------

2 6

DECLARE @cursor INT

DECLARE @rowcount INT

EXEC sp_cursoropen @cursor OUTPUT

, N'SELECT * FROM t2 where a=@id'

, 4098

, 8193

, @rowcount output

,N'@id int'

,@id=2

EXEC sp_cursorfetch @cursor, 2, 0, 4

EXEC sp_cursorclose @cursor

a b

----------- -----------

a b

----------- -----------

2 6

大家一定要问这个4098 和135170哪来的呀，这就是根据配置项给合出来的一个值，事由上在网上提供的资料也有不对的地方，那个作者使用的是2 ，而2仅是表明这个光标是动态，对于参数没有定义接收，也就是2不能让它知道SQL语句要接受参数值。

结论：其实我们只是没有注意到这个第三个参数的设置而已，根据参数列进行组合达到自己的目的，我们先看看这个参数的说明：

[@scrollopt =] scroll_options OUTPUT

Is the cursor scroll type. scroll_options is int with a default of 1 (keyset-driven), and can be a combination of these values (exactly one of the first 5 must be specified).

Value	Description
0x0001	Keyset-driven cursor.
0x0002	Dynamic cursor.
0x0004	Forward-only cursor.
0x0008	Static cursor.
0x0010	Fast forward-only cursor.
0x1000	Parameterized query.
0x2000	Auto fetch.
0x4000	Auto close.
0x8000	Check acceptable types
0x10000	Keyset-driven acceptable.
0x20000	Dynamic acceptable.
0x40000	Forward-only acceptable.
0x80000	Static acceptable.
0x100000	Fast forward-only acceptable.