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?
Best Answer
When opening a cursor, there is the
WITH HOLD
preventing the issue. SoIs working well for my case. As an alternative
close_on_endtrans
option could also be switched either for session or public.