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

--           Copyright (c) 1988-2005 Microsoft Corporation

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

--          转载注明出处

--               blog.csdn.net/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

GO

 

常规下直接取得 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.

On return, @scrollopt contains the type of cursor actually created, which may not match what was requested.

对于上面的例子我们是要有参数传送的，好我们看这个 0x1000 Parameterized query

参数化查询，正是我们所想要的，当然于 0x0002 Dynamic cursor.

这是个是必须的啦，两个相加， 0x1002 = è 4098 ， OK 正好是我上面所用的参数吧，