1

Having this code here:

DECLARE @rowfoo integer
DECLARE @query varchar (32000)

SET @query = 'CREATE OR REPLACE PROCEDURE testprocedure AS
            BEGIN
                print ''Just a dummy text''
            END'

DECLARE test_cursor CURSOR FOR
        SELECT row_num FROM sa_rowgenerator(1, 31, 1)

OPEN test_cursor
    FETCH NEXT test_cursor into @rowfoo
    WHILE @@FETCH_STATUS = 0
    BEGIN
        execute(@query)
        FETCH NEXT test_cursor into @rowfoo
    END
CLOSE test_cursor
DEALLOCATE test_cursor

I've recognized, that the cursor is getting closed after the execute(@query) statement. Based on what I've found so far it's because of implicit commit done for DDL statements. As I need to create number of procedures via a script, is there any work around for this issue?

2

When opening a cursor, there is the WITH HOLD preventing the issue. So

DECLARE @rowfoo integer
DECLARE @query varchar (32000)

SET @query = 'CREATE OR REPLACE PROCEDURE testprocedure AS
            BEGIN
                print ''Just a dummy text''
            END'

DECLARE test_cursor CURSOR FOR
        SELECT row_num FROM sa_rowgenerator(1, 31, 1)

OPEN test_cursor WITH HOLD
    FETCH NEXT test_cursor into @rowfoo
    WHILE @@FETCH_STATUS = 0
    BEGIN
        execute(@query)
        FETCH NEXT test_cursor into @rowfoo
    END
CLOSE test_cursor
DEALLOCATE test_cursor

Is working well for my case. As an alternative close_on_endtrans option could also be switched either for session or public.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.