有关游标的一个问题,请多指教!

awds1982

我写了下面一段代码,放在分析器里执行,结果是"名为 'cursor_problemrecord' 的游标不存在。",不知该如何获得该游标,还请高手指教!

DECLARE @sql nvarchar(1000)
DECLARE @eid int
DECLARE @bgndate smalldatetime
DECLARE @enddate smalldatetime
DECLARE @otherwhere nvarchar(200)

DECLARE @ma_6_1 nvarchar(2000)
DECLARE @tmpgrade int  
DECLARE @tmpplaceincomp nvarchar(30)
DECLARE @tmpydate int  
SET @eid = 1
SET @bgndate = convert(smalldatetime,'2005-12-1')
SET @enddate = convert(smalldatetime,'2017-1-1')
SET @otherwhere = N' status=-3 '

SET @sql = N'DECLARE cursor_problemrecord INSENSITIVE CURSOR FOR (SELECT grade,placeincomp,Year(bgndate) FROM problem_record WHERE recscid=31 AND receid=@eid AND (bgndate>=@bgndate AND enddate<@enddate)'+N' AND  '+@otherwhere+N' ORDER BY bgndate DESC  )'
EXEC sp_executesql @sql,N'@eid int,@bgndate smalldatetime,@enddate smalldatetime',@eid,@bgndate,@enddate

OPEN cursor_problemrecord
FETCH NEXT FROM cursor_problemrecord
INTO @tmpgrade,@tmpplaceincomp,@tmpydate
WHILE @@FETCH_STATUS = 0
        BEGIN
                SET @ma_6_1 = @ma_6_1 +CONVERT(nvarchar(4),@tmpydate)+ N'#'+CONVERT(nvarchar(4),@tmpgrade)+ N'#'+@tmpplaceincomp+ N'@'
                FETCH NEXT FROM cursor_problemrecord
                INTO @tmpgrade,@tmpplaceincomp,@tmpydate
        END                                       
CLOSE cursor_problemrecord
DEALLOCATE cursor_problemrecord       

GO

asurae

SET @sql = N'DECLARE cursor_problemrecord INSENSITIVE CURSOR FOR (SELECT grade,placeincomp,Year(bgndate) FROM problem_record WHERE recscid=31 AND receid=@eid AND (bgndate>=@bgndate AND enddate<@enddate)'+N' AND  '+@otherwhere+N' ORDER BY bgndate DESC  )'
EXEC sp_executesql @sql,N'@eid int,@bgndate smalldatetime,@enddate smalldatetime',@eid,@bgndate,@enddate

游标定义在动态SQL语句中，所以这里的编译不认识。

在动态SQL语句的执行中，把游标作为参数返回。
（游标在存储过程中只能作为参数传出，不可传入）

EXEC sp_executesql @sql,N'@eid int,@bgndate smalldatetime,@enddate smalldatetime,cursor_problemrecord cursor output',@eid,@bgndate,@enddate,cursor_problemrecord output

试试

awds1982

执行结果是"向存储过程传递常量时不能使用 OUTPUT 选项。",
还请再麻烦一下,看看是什么问题?谢谢了!