How to name a cursor dynamically in SQL Server

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

Solution

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!