l******9
发帖数: 579 | 1 【 以下文字转载自 Database 讨论区 】
发信人: light009 (light009), 信区: Database
标 题: SQL run a stored procedure by fetching from a cursor row by row
发信站: BBS 未名空间站 (Fri May 23 17:57:23 2014, 美东)
I need to run a stored procedure on SQL server 2008.
But, I can only fetch one row from the cursor. After that, the @@FETCH_
STATUS is -1.
DECLARE @my_table TABLE
(
code int not null
);
INSERT INTO @my_table (id)
SELECT DISTINCT a.id
FROM table1 as a
WHERE a.value = 'abc'
ORDER BY a.id ASC
DECLARE t_input CURSOR FOR
SELECT id
FROM @my_table
DECLARE @return_value tinyint
DECLARE @my_id varchar(3)
OPEN t_input
FETCH NEXT FROM t_input into @my_id
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC @return_value = my_procudure
@arg1 = 6,
@ar2 = 9
SELECT 'Return Value' = @return_value
FETCH NEXT FROM t_input into @my_id # this is -1 !!!!!!!!!!
END
So, it only run one iteration in the while loop.
my_procudure run well and has nothing to do with the cursor.
Any help would be appreciated. |
|
