A question about cursors, please advise!

awds1982

I wrote the following piece of code and executed it in the analyzer. The result is "the cursor named 'cursor_problemrecord' does not exist."

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

The cursor is defined in a dynamic SQL statement, so the compilation here does not recognize it.

In the execution of dynamic SQL statements, the cursor is returned as a parameter.
(The cursor can only be passed out as a parameter in the stored procedure, not passed in)

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

Try

awds1982

The result of the execution is "The OUTPUT option cannot be used when passing constants to stored procedures.",
Please trouble again and see what is the problem? Thanks!