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?