Declare cursor in dynamic sql

Question

This code in a store procedure worked for years, now on the OPEN line I am getting A cursor with the name ... does not exist.

Did something change in sp_executesql that might have caused this to break?

Is there another way of doing this (the need for dynamic SQL is because the @Types param is passed in as a pre-formatted string for the IN clause?)

Select @Query = 'DECLARE cur_person CURSOR FOR
                 SELECT *
                 FROM MyTable
                 WHERE PersonID = @PersonnelID
                 AND Type in '  + @Types + '    <== formatted list for IN clause

EXEC sp_executesql @Query
OPEN cur_person                   <== get cursor doesn't exist error

There is a database option (`CURSOR_DEFAULT`) that controls whether cursors default to local or global when not explicitly specified. Maybe this has been changed your end hence the procedure suddenly breaking. In any event you could try explicitly declaring it as `GLOBAL` — Martin Smith, Mar 08 '12 at 10:59
Your way better off converting the in list to a table; `where type in dbo.someSplitFunc(@Types)` http://stackoverflow.com/questions/697519/split-function-equivalent-in-tsql — Alex K., Mar 08 '12 at 11:00
Thanks to Martin - adding GLOBAL did the trick. All other solutions would be fine too I guess, but I didn't have the client code and had to make minimum changes to the proc to make things work. — Graeme, Mar 08 '12 at 16:04

score 1 · Accepted Answer · answered Mar 08 '12 at 11:10

1

In your example, that means that the cursor is defined locally.

You can define it globally with database option (CURSOR_DEFAULT) but that might not be a good idea.

Another thing that you can do is put all code in the query and execute it.

answered Mar 08 '12 at 11:10

aF.

64,980
43
135
198

1

Martin Smith was first with the GLOBAL option but it was a comment so I can't accept him :-( This is next best. – Graeme Mar 08 '12 at 16:14

score 0 · Answer 2 · answered Mar 08 '12 at 11:17

I don't know why it fails, but here's a split function that avoids the need for the dynamic query:

CREATE FUNCTION StringToTable ( @p_list varchar(MAX), @p_separator varchar(5) = ',', @p_distinct bit = null ) RETURNS @ParsedList table ( element varchar(500) ) AS BEGIN

DECLARE @v_element varchar(500), @Pos int, @v_insert_ind bit

SET @p_list = LTRIM(RTRIM(@p_list))+ @p_separator
SET @Pos = CHARINDEX(@p_separator, @p_list, 1)

IF REPLACE(@p_list, @p_separator, '') <> ''
BEGIN
    WHILE @Pos > 0
    BEGIN
        SET @v_insert_ind = 1
        SET @v_element = LTRIM(RTRIM(LEFT(@p_list, @Pos - 1)))
        IF @v_element <> ''
        BEGIN
            IF (@p_distinct = 1)
            AND (SELECT count(element) FROM @ParsedList WHERE element = @v_element) > 0
                SET @v_insert_ind = 0
            IF @v_insert_ind = 1
            BEGIN
                INSERT INTO @ParsedList (element) 
                VALUES (@v_element)
            END
        END
        --
        SET @p_list = RIGHT(@p_list, LEN(@p_list) - @Pos)
        SET @Pos = CHARINDEX(@p_separator, @p_list, 1)
    END
END

RETURN
END

Declare cursor in dynamic sql

2 Answers2