Can anybody please tell how to declare a cursor name dynamically (fixed name + unique name) in SQL Server?
This is to prevent the error
02-25-2018 10:12:01 ERROR (AdHocReportserviceImpl.java:882) : org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call usp_AdHocReport(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}];
SQL state [34000]; error code [16916]; [FMWGEN][SQLServer JDBC Driver][SQLServer]A cursor with the name 'FetchRegion' does not exist.; nested exception is java.sql.SQLException: [FMWGEN][SQLServer JDBC Driver][SQLServer]A cursor with the name 'FetchRegion' does not exist
while accessing it from multi threaded java application. I've tried adding LOCAL
after CURSOR
declaration but it doesn't work.
DECLARE FetchRegion CURSOR READ_ONLY FOR
SELECT ......
OPEN FetchRegion
FETCH NEXT FROM FetchRegion INTO @RGN
WHILE @@ROWCOUNT <> 0
BEGIN
.....
FETCH NEXT FROM FetchRegion INTO @RGN
END
CLOSE FetchRegion
DEALLOCATE FetchRegion
You can use this sample, i used a system table to get the first 10 rows, but you can create your own cursor.
DECLARE @cursor_name AS NVARCHAR(100)
SET @cursor_name = 'sampleCursor'
+ Replace(Cast(Newid() AS VARCHAR(36)), '-', '')
DECLARE @cursor_sql AS NVARCHAR(max)
SET @cursor_sql = N' DECLARE @name nvarchar(10) DECLARE ' + @cursor_name + N' CURSOR FOR select top 10 name from sys.all_columns OPEN ' + @cursor_name
+ N' FETCH NEXT FROM ' + @cursor_name
+ N' INTO @name WHILE @@FETCH_STATUS <> -1 BEGIN print @name FETCH NEXT FROM ' + @cursor_name + N' INTO @name end CLOSE '
+ @cursor_name + N' DEALLOCATE ' + @cursor_name
PRINT @cursor_sql
EXECUTE Sp_executesql
@cursor_sql
To the cursor name is added a guid, to guarantee that is always different.
Then a query is created and executed based on that name.
Hope it helps!