如何在sqlserver里使用动态游标

[mouseonline]

sql sever7.0
我想在定义游标时的sql语句是一个变量
请问高手怎么定义,下面是sqlserver帮助里的一个例子,怎么在
sql query里不能执行,能不能就语法解释一下,谢谢
EXEC SQL BEGIN DECLARE SECTION;
char szCommand[] = "SELECT au_fname FROM authors WHERE au_lname = ?";
char szLastName[] = "White";
char szFirstName[30];
EXEC SQL END DECLARE SECTION;
EXEC SQL
DECLARE author_cursor CURSOR FOR select_statement;
EXEC SQL
PREPARE select_statement FROM :szCommand;
EXEC SQL OPEN author_cursor USING :szLastName;
EXEC SQL FETCH author_cursor INTO :szFirstName;

[mouseonline]

晚上结帐

[pbdesigner]

马上发一个,绝对让各位想不到的好方法.呵...

[wangsw]

是的，SQLSERVER游标又叫后台CURSOR！！
不用的最好不用，包括前台CURSOR！因为其速度极慢！！
后台用法如下：：
DECLARE Employee_Cursor CURSOR FOR

SELECT LastName, FirstName

FROM Northwind.dbo.Employees

WHERE LastName like 'B%'



OPEN Employee_Cursor



FETCH NEXT FROM Employee_Cursor

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM Employee_Cursor

END



CLOSE Employee_Cursor

DEALLOCATE Employee_Cursor

SQL-92 Syntax

DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR
FOR select_statement
[FOR {READ ONLY | UPDATE [OF column_name [,...n]]}]

Transact-SQL Extended Syntax

DECLARE cursor_name CURSOR
[LOCAL | GLOBAL]
[FORWARD_ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
[TYPE_WARNING]
FOR select_statement
[FOR UPDATE [OF column_name [,...n]]]

[mouseonline]

请高手亮招呀

[wangsw]

SET NOCOUNT ON



DECLARE @au_id varchar(11), @au_fname varchar(20), @au_lname varchar(40),

@message varchar(80), @title varchar(80)

PRINT "-------- Utah Authors report --------"

DECLARE authors_cursor CURSOR FOR
SELECT au_id, au_fname, au_lname
FROM authors
WHERE state = "UT"
ORDER BY au_id

OPEN authors_cursor

FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT " "
SELECT @message = "----- Books by Author: " +
@au_fname + " " + @au_lname


PRINT @message

-- Declare an inner cursor based
-- on au_id from the outer cursor.

DECLARE titles_cursor CURSOR FOR
SELECT t.title
FROM titleauthor ta, titles t
WHERE ta.title_id = t.title_id AND
ta.au_id = @au_id -- Variable value from the outer cursor

OPEN titles_cursor
FETCH NEXT FROM titles_cursor INTO @title

IF @@FETCH_STATUS <> 0
PRINT " <<No Books>>"


WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @message = " " + @title
PRINT @message
FETCH NEXT FROM titles_cursor INTO @title

END

CLOSE titles_cursor
DEALLOCATE titles_cursor

[dotnba]

：（

[mouseonline]

提前

[ping_ping]

:)给个例子！看合不合适！

string ls_a, ls_n
string ls_sql
ls_n = "manager_id"
ls_sql = "SELECT " + ls_n + " FROM employee WHERE Emp_id = '102'"
DECLARE cru_test DYNAMIC CURSOR FOR SQLSA;
PREPARE SQLSA FROM :ls_sql USING SQLCA;
OPEN DYNAMIC cru_test;
DO WHILE SQLCA.SQLcode = 0
FETCH cru_test INTO :ls_a;
//对取得的ls_a进行处理，再继续取下一条
LOOP
IF SQLCA.SQLcode < 0 THEN
MessageBox("游标出错", "错误信息如下：~n " + SQLCA.SQLErrText)
END IF

[pbdesigner]

请到http://www.csdn.net/develop/article/8/8799.shtm,我有解决方案了.

[mouseonline]

to ping_ping:
我不想用动态sql,我觉得数据量的话,比较慢,
不知道对否,能不能写存储过程呢

[hellon1]

存储过程当然可以了，和PB中差不多，只是可以判断其内部的状态变量@@FETCH_STATUS

[dukeli]

thnan说得对,将你的例子改成带参数的存储过程.然后在程序中调用存储过程应该可以.

[mouseonline]

谢谢各位

[zhuzhichao]

在存儲過程中定義一個傳入參數.
你可以傳sql語句給這個參數.
然後在存儲過程中用sp_executesql執行這個參數不就可以了.

[mouseonline]

to 各位大侠:
要取的数据很多，要用游标
ping_ping的方法是可以的,怎么把他转为sqlsever的语法写法呀

[zhuzhichao]

to mouseonline(老鼠)
你將下面這些在查詢分析儀中執行會得到一些啟發.
能不用游標儘量不要用游標.
下面的@SQLString你可以不用set,在sp中作為一個傳入參數將你的sql語句傳入.


DECLARE @IntVariable INT

DECLARE @SQLString NVARCHAR(500)

DECLARE @ParmDefinition NVARCHAR(500)



/* Build the SQL string once.*/

SET @SQLString =

N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = @level'

SET @ParmDefinition = N'@level tinyint'

/* Execute the string with the first parameter value. */

SET @IntVariable = 35

EXECUTE sp_executesql @SQLString, @ParmDefinition,

@level = @IntVariable

/* Execute the same string with the second parameter value. */

SET @IntVariable = 32

EXECUTE sp_executesql @SQLString, @ParmDefinition,

@level = @IntVariable

[mouseonline]

谢谢草兄,我来看看

[mouseonline]

提前一下